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

Re: display query results

From: PJ <af(dot)gourmet(at)videotron(dot)ca>
To: Andy Shellam <andy(dot)shellam-lists(at)mailnetwork(dot)co(dot)uk>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: display query results
Date: 2008-07-31 13:55:05
Message-ID: 4891C439.6000106@videotron.ca (view raw or flat)
Thread:
Lists: pgsql-php
I knew it was too good to be true.
Now I am trying to verify the padding on "Alcohol" and in so doing tried 
to invoke phpPgAdmin... and you gussed it, I tried to follow all hte 
INSTALL instructions and http://localhost/phppgadmin just gives "cannot 
find on this server"
On to of it, the same script I ran yesterday now give me "Fatal error" 
Call to undefined function pg_connect() on line 10. Worked fine yesterday.
Wonder what it is that I screwed up?

Andy Shellam wrote:
> 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: Raymond O'DonnellDate: 2008-07-31 14:03:26
Subject: Re: display query results
Previous:From: Andy ShellamDate: 2008-07-30 21:52:23
Subject: Re: display query results

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