Problems with Postgresql and PHP

[coreilly]

So, I'm new to using postgresql, and I'm having some issues getting data out using PHP. I am able to connect to the database, and I've printed my query string out and run it in pgAdmin III, and it works fine, but I do not get a result set back.

$my_pg = pg_connect('host='.$hostname.' dbname='.$dbGeomName.' user='.$username.' password='.$password);
if (!$my_pg)
{echo "didn't connect<br>";}
else
{echo "connected<br>";}

  function colorCode($county)
  {
    $query = "select \"1HR\" from \"Flood_Advisor\" where \"name\" ilike '".$county."%'";
    $result = pg_query($my_pg, $query) ;//or die ("failed to get results ".pg_last_error());
    if(!$result)
    { echo "failed to get rows<br>"; }
    else
    {echo "got rows<br>";}
    $resultArray = pg_fetch_array($result, 0) ;//or die ("no rows");
    $stuff = $resultArray['1HR'];
    echo $query." ".$county." is ".$stuff."<br>";
  }
colorCode("Augusta"); 

for output I get:
---------------------------------------
connected
failed to get rows
select "1HR" from "Flood_Advisor" where "name" ilike 'Augusta%' Augusta is
---------------------------------------
So, it seems I am connecting to postresql, but fail to get a result, the query spit out does return a result though. Any help is greatly appreciated.

[NogDog]

How about echoing the result of pg_last_error() in your error code if the pg_query() fails?

(Or look into using pg_send_query(), pg_get_result() and pg_result_error() to possibly get more useful data.)

[coreilly]

I added in:

    echo "row: ".pg_numrows($result)."<br>";
    echo "error: ".pg_last_error(); 

I also tried changing my query to:

$result = pg_query($my_pg, $query) or die ("failed to get results ".pg_last_error()); 

But in both instances, I do not receive an error message from pg_last_error, and I also do not receive a row count. I'm looking into the other methods you suggested now.

[coreilly]

I just tried using pg_send_query() and pg_get_result(), and get the exact same output as with pg_query() and pg_fetch_array(). Also, pg_result_error() does the same as pg_last_error() and does not return any text.

[NogDog]

As far as I can see from the info you've provided and compared to the manual example page, I don't know why you don't get any error data. If you don't get any satisfaction here, I know there are at least a couple guys at the PHPbuilder.com forum who regularly use PostgreSQL and might have better insights into it than I do.