Re: display query results - Mailing list pgsql-php

PJ wrote:
> Lynna Landstreet wrote:
> Well, it does explain things a little. Unfortunately, I have tried
> about everything imaginable before posting except the right thing.
> I can not visualize what it is that my query is returning. Here is
> what the code is:
>
> Whatever  I enter as values for pg_fetch_result, the screen output is :
>
> resource(3) or type (pgsql result)
> *Warning*: pg_fetch_result() [function.pg-fetch-result
> <http://biggie/k2/function.pg-fetch-result>]: Unable to jump to row 1
> on PostgreSQL result index 3 in
> */usr/local/www/apache22/data/k2/test1_db.php* on line *29*

This suggests that there is no row 1 in your result-set.  I believe it
is zero-based, so try fetching row 0 if your query only returns 1 row.

> I don't understand what $resuts is returning - if it is an entire row,
> the one that the field is in that I am looking for, then why do I not
> get a printout of the text that is in that field? The row in the table
> is the second row and the field I am trying to retrieve is the 4th field.
$results as explained previously is just a pointer to a recordset.  This
analogy isn't brilliant, but think of your database table as a book.
Each row on a page within that book is a record, and the words in that
row are the data in the table's columns.

When you run a query, think of yourself looking at the book's index for
a given word.  The index will tell you the pages that word is on.
That's your $results - simply a pointer to your data.

You then need to turn to that page in the book (pg_fetch_* functions) to
start examining the lines for the word you want.  Once you've got your
line, you can find the word (column/data, from your array) you're
looking for.

Now turn that into PHP and SQL.  You run your query (looking in the
book's index) and the PostgreSQL driver will save the results into a
block of memory in your server's RAM, returning a resource identifier.
This is literally just saying "resource #3 is located at this location
in the computer's memory."  When you look up a record from that
result-set, PHP then knows where to look for the data.

I never really use the "or die" syntax, I tend to explicitly check the
return values of the functions.  Try this:

<?php
       $db = pg_connect("host=localhost port=5432 dbname=med user=med
password=0tscc71");

       // Note: according to
http://uk2.php.net/manual/en/language.types.boolean.php a resource
always evaluates to true,
       // therefore !$db may not evaluate to false when connection fails.
        if ($db === false)
        {
            die("Could not open connection to database server");
        }

         // generate and execute a query
        $query = "SELECT description FROM glossary_item WHERE
name='Alcohol'";
        $results = pg_query($db, $query);
        var_dump ($results);

       if ($results === false)
       {
          die("SQL query failed: " . pg_last_error($db));
       }
       else if (pg_num_rows($results) == 0)
       {
          // Only do this if you were expecting at least 1 row back
          die("SQL query returned no rows");
       }

       $results_formatted = pg_fetch_all($results);
       echo "<pre>"; // need this to show output better in a HTML page
       var_dump($results_formatted);
       echo "</pre>"; // need this to show output better in a HTML page

       /*
          $results_formatted will then be set out like follows:

          $results_formatted[row_index][column_name] = column_value
       */

        pg_close($db);
       ?>
> Am I querying correctly? The table is "glossary_item", the row I want
> is the one that is unique in containing the word "Alcohol" in the
> column "name"
>
> I changed:   $query = "SELECT * FROM glossary_item WHERE name=
> 'Alcohol'";
> same result
>
> Picture me tearing out my hair...
>

Regards,

Andy