Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C953B6500B4 for ; Wed, 30 Jul 2008 18:52:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 75566-01 for ; Wed, 30 Jul 2008 18:52:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp-gw30.mailanyone.net (smtp-gw30.mailanyone.net [208.70.128.56]) by postgresql.org (Postfix) with ESMTP id 8ADBA64FFE4 for ; Wed, 30 Jul 2008 18:52:27 -0300 (ADT) Received: from mailanyone.net by smtp-gw30.mailanyone.net with esmtpa (MailAnyone extSMTP andy@networkmail.eu) id 1KOJar-0001L2-Pb; Wed, 30 Jul 2008 16:52:26 -0500 Message-ID: <4890E297.3080405@mailnetwork.co.uk> Date: Wed, 30 Jul 2008 22:52:23 +0100 From: Andy Shellam User-Agent: Thunderbird 2.0.0.16 (Windows/20080708) MIME-Version: 1.0 To: PJ CC: pgsql-php@postgresql.org Subject: Re: display query results References: <48909454.7040305@videotron.ca> <82E74D266CB9B44390D3CCE44A781ED90B64DC@POSTOFFICE.cranel.local> <4890A871.3020906@videotron.ca> <4890AA8C.9080907@spidersilk.net> <4890CA07.2040405@videotron.ca> <4890D124.7020506@mailnetwork.co.uk> <4890D6F6.40709@videotron.ca> <4890DBBC.6080404@mailnetwork.co.uk> <4890E12F.4050805@videotron.ca> In-Reply-To: <4890E12F.4050805@videotron.ca> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200807/13 X-Sequence-Number: 3877 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 >>>>> ]: 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: >>>> >>>> >>> $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 "
"; // need this to show output better in a HTML page
>>>>       var_dump($results_formatted);
>>>>       echo "
"; // 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 >>>> >>> >>> >> > >