PDA

View Full Version : accessing a postgres db from php

catch
03-25-02, 18:14
heylo,

i've created a simple postgreSQL db called 'test' which is owned by user 'one' on my rh 7.2 system. ive tried to connect to the db with the following php call;
$dbhandle = pg_connect("dbname=test, user=one, password=***, host=localhost");

when i call the php page that holds this code i get an error message to the effect "database 'test' does not exist in the system catalog".

anyone know how i can check the system catalog, or otherwise troubleshoot this? is it some sort of permission problem or does php need to have a path specified to the db? i can access the database from the command line via 'psql test'.

thanks.
eperich
04-21-02, 19:35
do you have the postmaser started with the
-i flag
otherwise the dbserver does not allow TCP/IP connections

or maybe some missing permission in pg_hba.conf
ednark
09-27-02, 01:30
you may want to look into the PEAR library for simple connections:

http://pear.php.net/manual/en/core.db.php

basic overview....

1) you create a DB object.
2) you then pass sql to it a sql statement. which returns the result.

You may query the database using several different functions, each of which returns your data in a different useful PHP form.

This is as functional as using pg_connect but may end up giving you an easier time with stepping through results

here is a spoofed up example from the pear.php.net page

<?php
require_once 'DB.php';

$user = 'foo';
$pass = 'bar';
$host = 'localhost';
$db_name = 'clients_db';

// Data Source Name: This is the universal connection string
$dsn = "pgsql://$user:$pass@$host/$db_name";

// DB::connect will return a PEAR DB object on success
// or an PEAR DB Error object on error

$dbh = DB::connect($dsn);

// With DB::isError you can differentiate between an error or a valid connection.
if (DB::isError($dbh)) {
die ($dbh->getMessage());
}


$sql = ' SELECT * FROM customers ";

/// query databse and return an PEAR object repreesnting the result
$query_result = $dbh->query( $sql );

// With DB::isError you can differentiate between an error or a valid result.
if (DB::isError($query_result)) {
die ($dbh->getMessage());
}


/// query database and return a more userful array of arrays, each item in the array represents a row in the form of array( 'col1' = 'val1', ... )

$sql = " SELECT name, address, password FROM customers ";

$results = $dbh->getAll( $sql, DB_FETCHMODE_ASSOC );

foreach ( $results as $result ) {
echo $result['name'];
echo $result['address'];
echo $result['password'];
}

/// query databse and return only first column of first row found in result
$sql = " SELECT COUNT(*) FROM customers ";
$count = $dbh->getOne( $sql );


/// query database and return an array of customer names: an array form of the first column found
$sql = " SELECT name FROM customers ";
$array_of_names = $dbh->getCol( $sql );

foreach ( $array_of_names as $name ) {
echo $name;
}

// You can disconnect from the database with:
$db->disconnect();
?>
eperich
09-27-02, 04:19
What do you think it's better for applications

ADODB or PEAR?
ednark
09-27-02, 14:15
sorry for being long:

I am assuming we are talking about these...
http://php.weblogs.com/ADODB_manual
http://pear.php.net/manual/en/core.db.php

well as far as i can tell they are functionally equivalent at the basic level... AODB seems to have a few more specific features... including the use of pivot table creation (a bit plus)... some extensions for creating diplays and form fields based on the tables....

I think at the coding level PEAR is more well coded (well at least more interestingly and more complex) and more structured... easier to extend... and has a simpler interface with it's multiple getX() function... The intent is to be a base for future further applications and not meant to be ONLY a standalone interface to a database

AODB seems to have a lot more support and popularity... has some more specific features as mentioned above... and probably has more people thinking about ways to use it effectively across different projects...

It's obvious that AODB started as a smaller project, that it has been heavily influenced by and has adopted many of the PEAR styles (which they freely amit themselves), such as error handling and using the dsn string format for connections...

I don't think you can go wrong using either one...

personally i use PEAR but i have to admit that i know about using PEAR a lot more than i know about using AODB... and have not attempted to use AODB for a project... so the only reason i use PEAR is that... well... I use PEAR....

as an example of the intent of the projects... AODB is focused at implementing your SQL query correctly, and returning the basic results, and has been extended continuously with bits that make life easier for you.... PEAR is coded very modularly and is built assuming you would want to make something else based upon the code... for example

PEAR is really trying to move onto higher level conceptual use of webpage databases and php.

PEAR DatabaseObject: The Pear project has been moving foreward toward a method of using the database connection not for arbitrary queries but for really making the realtional database model seem an object oriented model to mesh more easily with PHP coding styles....

for example in concept you link an object class type with a table in the database... when you instantiate the object which makes an automatic query to the database to fill in the values of the object you want.....


Bottom line: WHATEVER FLOATS YOUR BOAT.

neither is superior to the other in general... they are different and each project team has spent optimizing their code for the places they assume it will be the most usefull... and both have done a good job implementing the common basic features that everyone needs to survive
eperich
09-27-02, 16:20
Do you have tried to send "create table" statements through the Pear db
I wanted this to know cause I don't want to write different sql-files for mysql and postgresql.

Is this possible with pear?
With ADODB it's not possible
ednark
10-12-02, 18:40
yes i have and it works fine