[Resolved] PostgreSQL pg_fetch_array

[n00854180t]

I am getting a bunch of blank results when I try to use pg_fetch_array. Instead of just not getting any output it is looping through a lot and displaying the html in my script, but not the results from the database.

<?php

// This page will list all of the items
// from the product_list table.  Each item will have
// a link to add it to the cart


// This page contains the connection routine for the
// database as well as getting the ID of the cart, etc

$dbServer = "mysql.mygisol.com";
$dbUser = "thanato_admin";
$dbPass = "lifedoor";
$dbName = "thanato_regrowth";

function ConnectToDb($server, $user, $pass, $database)
{
// Connect to the database and return 
// true/false depending on whether or 
// not a connection could be made. 

$s = mysql_connect($server, $user, $pass);
$d = mysql_select_db($database, $s);

if(!$s || !$d)
return false;
else
return true;
}

function GetCartId()
{
// This function will generate an encrypted string and
// will set it as a cookie using set_cookie. This will
// also be used as the cookieId field in the cart table

if(isset($_COOKIE["cartId"]) OR $_GET["cartid"] == $_COOKIE["cartId"])
{
return $_COOKIE["cartId"];
}
else
{

// There is no cookie set. We will set the cookie
// and return the value of the users session ID

session_start();
setcookie("cartId", session_id(), time() + ((3600 * 24) * 30));
return session_id();
}
}
@session_start();






// Get a connection to the database

//$cxn = ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
pg_connect("dbname=regrowth") or die("Couldn't Connect ".pg_last_error()); 


if (!$_GET['country']){

$_SESSION["country"] = $_POST["selcountry"];
} elseif (!$_POST['country']) {
$_SESSION['country'] = $_GET['country'];
}


$ems[1]= array("Canada");
$ems[2]= array("");
$ems[3]= array("United Kingdom");
$ems[4]= array("Japan");
$ems[5]= array("China");

$ems[6]= array("Azerbaijan","Bosnia","Croatia","Cyprus","Estonia","France","Ireland","Lithuania","Luxembourg","Northern Ireland","Poland","Scotland","Spain","Yugoslavia");

$ems[7]= array("Armenia","Austria","Belgium","Crete-Greece","Czech Republic","Czechoslovakia","Denmark","Deutschland","Faroe Islands","Finland","Germany","Greece","Hungary","Iceland","Latvia","Liechtenstein","Malta","Netherlands","Norway","Portugal","Russia","Slovenia","Sweden","Switzerland","Ukraine");

$ems[8]= array("Austrailia","Guam","Hong Kong","India","New Zealand","Pakistan","Philippines","Singapore","South Austrailia","Taiwan","Tasmania, Austrailia","Vietnam","West Indies");

$ems[9]= array("French Polynesia","Laos","Malaysia","Thailand");
$ems[10]= array("Israel","Italy","Jordan","Turkey","United Arab Emirates","United Arab Republic");
$ems[11]= array("Bahrain","Kuwait","Oman","Qatar","South Africa","South Korea");

$ems[12]= array("Argentina","Bahamas","Bolivia","Brazil","Chile","Columbia","Costa Rica","Dominican Republic","Grenada West Indies","Guatemala","Jamaica","Peru","Trinidad & Tobago","Uruguay");
$ems[13]= array("USA");

$air[1]= array("Canada");
$air[2]= array("");

$air[3]= array("Belgium","Crete-Greece","Denmark","Deutschland","Faroe Islands","Finland","France","Germany","Greece","Iceland","Ireland","Israel","Italy","Liechtenstein","Luxembourg","Netherlands","Northern Ireland","Norway","Portugal","Scotland","Spain","Sweden","Switzerland","United Kingdom");

$air[4]= array("Austrailia","Japan","New Zealand","South Austrailia","Tasmania, Austrailia");

$air[5]= array("Argentina","Armenia","Austria","Azerbaijan","Bahamas","Bahrain","Bolivia","Bosnia","Brazil","Chile","China","Columbia","Costa Rica","Croatia","Curacao","Cyprus","Czech Republic","Czechoslovakia","Dominican Republic","Estonia","French Polynesia","Grenada West Indies","Guam","Guatemala","Hong Kong","Hungary","India","Iran","Jamaica","Jordan","Kuwait","Laos","Latvia","Lebanon","Lithuania","Malaysia","Malta","Monaco","Oman","Pakistan","Peru","Philippines","Poland","Puerto Rico","Qatar","Russia","Singapore","Slovenia","South Africa","South Korea","Taiwan","Thailand","Trinidad & Tobago","Turkey","Ukraine","United Arab Emirates","United Arab Republic","Uruguay","Vietnam","West Indies","Yugoslavia");
$air[6]= array("USA");

for ($i=1; $i<=13; $i++){

  //check if value in array
  if (in_array($_SESSION["country"],$ems[$i])){

  //set timezone value if value is in array
  $_SESSION["emszone"]= "E" . $i;

   }//end if
}//end for loop

for ($i=1; $i<=6; $i++){

  //check if value in array
  if (@in_array($_SESSION["country"],$air[$i])){

  //set timezone value if value is in array
  $_SESSION["airprior"]= $i;

   }//end if
}//end for loop


if ($_SESSION["country"] != "USA")
{


$pagetitle = "International Product List";
$table = "foreign_prodlist"; 
$_SESSION['expressorems'] = "EMS";
$_SESSION['prorair'] = "AirMail";
$_SESSION['shiptype'] = "AirMail";

}
else
{

$pagetitle = "United States Product List";
$table = "product_list";

$_SESSION['expressorems'] = "Express";
$_SESSION['prorair'] = "Priority";
$_SESSION['shiptype'] = "Priority";
}
$_SESSION["table"] = $table;


$result = pg_query("SELECT * FROM " . $_SESSION['table'] . " ORDER BY id ASC") or die(pg_last_error());



echo $_SESSION['table'];
echo $_SESSION['country'];
echo $_SESSION['emszone'];
echo "(" . $_SESSION['airprior'] . ")";
echo $_SESSION['expressorems'];
echo $_SESSION['prorair'];
?>


<html>
<script language="JavaScript">
function DescView(itemName, winName, height, width)
{
settings="toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=no,resizable=yes,width="+width+",height="+height;

window.open("http://www.neogal.com/desc/"+itemName,winName,settings);
}

function ViewPic(picName, name, height, width)
{
settings="toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=no,resizable=yes,width="+width+",height="+height;


window.open("http://www.neogal.com/pics/"+picName,name,settings);
}
</script>
<font face="verdana" size="3" color="black"><br>
<?php echo $pagetitle; ?>
</font>

<body>
<table>


<tr>
<th><font face="verdana" size="2" color="black">Name</font></th>
<th><font face="verdana" size="2" color="black">Price</font></th>
<th><font face="verdana" size="2" color="black">Description</font></th>
<th><font face="verdana" size="2" color="black">Picture</font></th>
</tr>
<?php 
/*

<tr> 
<td width="15%" height="25">
<font face="verdana" size="1" color="black"> 
<select name="<?php echo $row["itemId"]; ?>" onChange="UpdateQty(this)"> 
*/




/*
for($i = 1; $i <= 20; $i++) 
{ 
echo "<option "; 
if($row["qty"] == $i) 
{ 
echo " SELECTED "; 
} 
echo ">" . $i . "</option>"; 
} 
*/
?>

</select> 
</font> 
</td>
</tr> 


<?php
while($row = pg_fetch_array($result, PGSQL_ASSOC))
{
?>




<tr> 
<td width="30%" height="25"> 
<font face="verdana" size="1" color="black"> 
<?php echo $row["itemName"]; ?> 
</font> 
</td> 
<td width="10%" height="25"> 
<font face="verdana" size="1" color="black"> 
$<?php echo $row["itemPrice"]; ?> 
</font> 
</td> 
<td width="50%" height="25"> 
<font face="verdana" size="1" color="black"> 
<a href="#" OnClick="DescView('<?php echo $row["itemDN"]; ?>', 'Description', 400, 400)">Description: <? echo $row['itemName']; ?></a> 
</font> 
</td> 
<td width="50%" height="25"> 
<font face="verdana" size="1" color="black"> 
<a href="#" OnClick="ViewPic('<?php echo $row["itemPic"]; ?>', 'Picture', 600, 200)"><img src="/pics/<?php echo $row["itemPic"]; ?>" height="60" width="60"></a> 
</font> 
</td> 
<td width="10%" height="25"> 
<font face="verdana" size="1" color="black"> 
<a href="cart.php?action=add_item&id=<?php echo $row['itemId']; ?>&qty=1&shiptype=<?php echo $_SESSION['shiptype']; ?>">Add Item</a> 
</font> 
</td> 
</tr> 
<tr> 
<td width="100%" colspan="4"> 
<hr size="1" color="red" NOSHADE> 
</td> 
</tr> 
<?php } ?>

<tr> 
<td width="100%" colspan="4"> 
<font face="verdana" size="1" color="black"> 
<a href="cart.php">Your Shopping Cart &gt;&gt;</a> 
</font> 
</td> 
</tr> 
</table> 
<a href="sessiontest01.php">Test SESSION</a>
</body> 
</html> 
<?php 
$_SESSION['table'] = $table;

?>

[Sxooter]

for pg_query you HAVE to include the connect handle:

$conn = pg_connect($connect_string);
$res = pg_query($conn,$query);

[n00854180t]

It's still doing the same thing, it displays the html and just loops it forever, but no results are displayed. http://www.neogal.com/ to see what I mean.

[Sxooter]

Right before you start your loop, try tossing in:

print pg_num_rows($result);
exit;

and see how many rows are showing up for that $result handle...

[n00854180t]

It printed "20". When I query the db in psql it gives me the same. It was still printing all the html without results though.

[Sxooter]

Oh, I see it. You realize that the order for args for pg_fetch_array is (result_id,row,PGSQL_FLAGS)???

That means you have to give a row number to use the flags. Seeing as how you are calling the results by name for each column, just try omitting the PGSQL_ASSOC flag and see if it works.

[n00854180t]

I tried both, neither works. The only thing different is that it doesn't repeat a thousand times, only 20.

[Sxooter]

Well, then that's ONE bug down. There's probably some more there.

Try adding this line inside the while fetch_array thing:

print implode(":",array_keys($row));
exit;

And see what you get, and if the array key names match what you're using in your code.

[n00854180t]

0🆔1:itemid:2:itemname:3:itemdesc:4:itemprice:5:itemweight:6:itempic:7:itemdn

Thats the output of the code you just gave me. Those are indeed the names of the columns in my product_list table.

[Sxooter]

Note that they're all lower case, so you might need to lowercase them in the php code to display the rows.

[n00854180t]

Okay that did it. Thanks Sxooter!

Is that supposed to be a feature or a limitation?

[Sxooter]

Originally posted by n00854180t
Okay that did it. Thanks Sxooter!

Is that supposed to be a feature or a limitation?

It's a side effect of most SQL engines. SQL spec says to fold unquoted identifiers to uppercase, which would mean they'd all be $var['THIS_NAME'] kinda thing, harder to read, but would still cause the problem, while Postgresql folds to lowercase which makes it easier to read.

You can prevent such behaviour if you always quote your DDL (data description language, i.e. create table "Bubba" ("Name" text, "IdNum" int);

but then you have to quote them every time you access them or it'll break, because now the parser will see "select Name, IdNum from Bubba" as "select name, idnum from bubba" and not find the table or it's fields.

So, I just stick to all lowercase in postgresql, problem solved.

[n00854180t]

Okay, sounds fair enough. I can make do for subqueries and the extra features and whatnot.