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:52:23
Message-ID: 4890E297.3080405@mailnetwork.co.uk (view raw or flat)
Thread:
Lists: pgsql-php
Haha, no worries, I've had the same issue before.
It's almost certain that the text in your column does not exactly match 
"Alcohol" for one of a few reasons, e.g.

"Alcohol " (right-padding)
" Alcohol" (left-padding)
" Alcohol " (padded both sides)

will not match = 'Alcohol' in the query. 

Try trimming the data in that field - from PgAdmin or some other query 
tool, run something like:

|UPDATE glossary_item SET "name" = |trim(both ' ' from "name")

WARNING: I've not tried the above query so make sure you have a backup 
of your data before you run it ;-)
Then try changing your PHP query back to = again.

Andy

PJ wrote:
> I want to thank everyone for their contribution. As usual, the 
> solution turns out to be simple. Remeber the KISS principle.
> see the note within the text: the LIKE condition did it. But I will 
> have more questions as I grope further in the dark. :))
>
> Andy Shellam wrote:
>> 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%'
> Great aim for shooting in the dark. :)) That was it.
> Now, could someone explain why I need the LIKE statement?
> I will try to check the documentation...
>>
>> 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: PJDate: 2008-07-31 13:55:05
Subject: Re: display query results
Previous:From: PJDate: 2008-07-30 21:46:23
Subject: Re: display query results

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