Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2002
    Location
    Toronto
    Posts
    13

    Unanswered: accessing a postgres db from php

    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;
    PHP Code:
    $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.

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    postmaster

    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
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Sep 2002
    Posts
    8

    Lightbulb PEAR solution

    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();
    ?>

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    question

    What do you think it's better for applications

    ADODB or PEAR?
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  5. #5
    Join Date
    Sep 2002
    Posts
    8

    Wink AODB vs PEAR

    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

  6. #6
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    CREATE TABLE

    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
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  7. #7
    Join Date
    Sep 2002
    Posts
    8
    yes i have and it works fine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •