Re: display query results

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
>>>>
>>>
>>>
>>
>
>