Skip site navigation (1) Skip section navigation (2)

Re: display query results

From: Andy Shellam <andy(dot)shellam-lists(at)mailnetwork(dot)co(dot)uk>
To: PJ <af(dot)gourmet(at)videotron(dot)ca>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: display query results
Date: 2008-07-30 21:23:08
Message-ID: 4890DBBC.6080404@mailnetwork.co.uk (view raw or flat)
Thread:
Lists: pgsql-php
Just shooting in the dark here, but I'm thinking there might be some 
extra spaces around the column values.  For some reason PostgreSQL is 
not returning any rows for that query, which is where the root of your 
problem lies.

Do you have PgAdmin?  If so try the exact same query against the same 
database and server.
Also try changing your query as follows:

SELECT description FROM glossary_item WHERE name LIKE '%Alcohol%'

and see what you get.

Regards,

Andy


PJ wrote:
> Annotated within text below:
>
> Andy Shellam wrote:
>> 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.
> Been there, done that. No change.
>>
>>
>>> 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);
>>       ?>
> Tried your coding - returns: resource(3) of type (pgsql result) SQL 
> query returned no rows
> The row is there... isn't that what were asking for?
> To go by the book, I even changed the description to * as noted before 
> below.
> Something is rotten in Denmark.
> This is getting ridiculous - I have followed the instructions as 
> specified in the Postgresql documentation and examples - and it just 
> doesn't work.
>
> The db is like this..
> int4        ||int4              || varchar(32)|| text
> _item_id  ||  glossary_id ||  name         || description _
> 2           ||        1          || Alcohol      || One of thetwo 
> major.... blah...blah.. blah
>>> 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
>>
>
>

In response to

Responses

pgsql-php by date

Next:From: Tomas VondraDate: 2008-07-30 21:30:26
Subject: Re: display query results
Previous:From: PJDate: 2008-07-30 21:02:46
Subject: Re: display query results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group