new coder to PHP with loop / query trouble

[guru_del]

here is my code that i am having trouble with.

<?php

// Get a connection to the database
$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
$result = mysql_query("SELECT * FROM submitorder");
?>

<?php
while($row = mysql_fetch_array($result))
{
$orderId=$row["orderId"];
$orderName=$row["orderName"];
$ordercookieid=$row["cookieid"];

  ?>

<?php echo $row["orderName"]; ?>

<?php

$orderdetails = mysql_fetch_assoc(mysql_query("SELECT * FROM cart WHERE cookieID = '$result[cookieID]'"));

while ($orderdetails = mysql_fetch_assoc($orderdetails)) {

$itemdetails = mysql_fetch_assoc(mysql_query("SELECT * FROM items WHERE itemID = '$ordersdetails[itemID]'"));

echo $orderdetails['qty'] .' X ' $itemdetails['itemname'] .' At ' . $itemdetails['itemprice'] . '<p>';

}

?>

I get this: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/virtual/site482/fst/var/www/html/orderstest.php on line 74 (which is this line while ($orderdetails = mysql_fetch_assoc($orderdetails)) {
)

Thanks for your help.

[HalfaBee]

This line is not correct

$orderdetails = mysql_fetch_assoc(mysql_query("SELECT * FROM cart WHERE cookieID = '$result[cookieID]'")); 

//should be 

$orderdetails = mysql_query("SELECT * FROM cart WHERE cookieID = '$result[cookieID]'"); 

HalfaBee

[guru_del]

thanks that got rid of the error.

Now my problem is for some reason I cannot get my code to output the cookieID field

       <?php
		while($row = mysql_fetch_array($result))
		{
			$orderId=$row["orderId"];
			$cookieID=$row[cookieID];



  ?>

<?php echo $row["cookieID"]; ?>

That returns nothing. it is a varchar(50) field. Anythoughts?

Thanks again.

[sarahk]

start with this small change

<?php 
while($row = mysql_fetch_array($result)) 
{ 
  $orderId=$row['orderId']; 
  $cookieID=$row['cookieID']; 

  echo $row['cookieID']; ?> 
}

[guru_del]

Hmmm.. still nothing. I am not getting the variable cookieID to echo and I have a feeling that is killing my other queries. Here is my full code

<?php

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

include("db.php");

// Get a connection to the database
$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
$result = mysql_query("SELECT * FROM submitorder ") ;	?>




        <?php
		while($row = mysql_fetch_array($result))
		{
			$orderId=$row['orderId'];
			$cookieID=$row['cookieID'];
			$orderName=$row['orderName'];
			$orderPrice=$row['orderPrice'];
			$firstlast=$row['firstlast'];
			$address1=$row['address1'];
			$address2=$row['address2'];
			$city=$row['city'];
			$state=$row['state'];
			$zip=$row['zip'];
			$cardtype=$row['cardtype'];

			$expiration=$row['expiration'];
			$cardinfo=$row['cardinfo'];
			$homephone=$row['homephone'];
			$cellphone=$row['cellphone'];
			$email=$row['email'];
			$billingfirstlast=$row['billingfirstlast'];
			$billingaddress1=$row['billingaddress1'];
			$billingaddress2=$row['billingaddress2'];
			$billingcity=$row['billingcity'];
			$billingstate=$row['billingstate'];
			$billingzip=$row['billingzip'];
			$model=$row['model'];
			$make=$row['make'];
			$year=$row['year'];
			$trans=$row['trans'];
			echo $row['cookieID'];

  ?>

<?php echo $row['orderName']; ?>
<?php echo $row['cookieID']; ?>
<?php
$orderdetails = mysql_query("SELECT * FROM cart WHERE cookieID = '$row[cookieID]'") or die("Couldn't execute query Order Details.");

echo $orderdetails ['itemid'];

while ($orderdetails = mysql_fetch_assoc($orderdetails)) {

$itemdetails = mysql_fetch_assoc(mysql_query("SELECT * FROM `items` WHERE `itemID` = '$ordersdetails[itemID]'")) or die("Couldn't execute query Item Details.");;

echo $itemdetails['itemname'];


}

?>
 // I echo every column in the database from the query result
<?php echo $row['billingstate']; ?>


    <?php
		}
	?>

[superwormy]

There is a fieldname 'cookieID' in your database right? And theres something in the field too right? And its the same case, ie you're not using cookieId or cookieid are you?

[guru_del]

Funny you mention that as I just double checked and the variable was different cased (un-aware that affected things, I am used to Cold Fusion which is not as sensitive) got it to move beyond there but here is where I am getting stalled:

Error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/virtual/site482/fst/var/www/html/orderstest2.php on line 75

Line 75 is : while ($orderdetails = mysql_fetch_assoc($orderdetails)) {

[superwormy]

On the first loop, $orderdetails in a RESOURCE which contains all the data from MySQL.

As soon as you do this:

$orderdetails = mysql_fetch_assoc ($orderdetails)

You're making $orderdetails into an ARRAY with one record, so its dying on the second loop. Do this instead:

$orderdetails_resource = mysql_query ("your query here") or die (mysql_error());

while ($orderdetails_array = mysql_fetch_array ($orderdetails_resource)) {

// do stuff

}

That way you're not overwriting the $orderdetails_resource MySQL Resource.

[guru_del]

All:

Thank you so much for your help so far. I feel like i am getting closer but

<?php
$orderdetails_resource = mysql_query("SELECT * FROM cart WHERE cookieID = '$row[CookieID]'") or die("Cannot Complete Cart Info");

echo $orderdetails_resource['itemID'];

while ($orderdetails_array = mysql_fetch_array($orderdetails_resource)) {

$itemdetails = mysql_fetch_assoc(mysql_query("SELECT * FROM items WHERE itemId = '$ordersdetails_array[itemID]'")) or die("Cannot Complete Item Info"); 

 echo $itemdetails['itemName'];


}

?>

I am getting returned my Cannont Complete Item Info.

[superwormy]

ALWAYS, ALWAYS, ALWAYS use mysql_query ("your query here") or die (mysql_error()); so that you can see your damn errors.

Try that, and lets see if theres an error in your SQL syntax.

And here, I'll give ya a hint, $ordersdetails_array[itemID] read through that carefully, $ordersdetails... $orderdetails... $ordersdetails...

AND you SHOULD NOT be doing this:
$my_array[my_field]

You SHOULD do this:

$my_array['my_field'] OR $my_array["my_field"]

[superwormy]

Also, in general MySQL is NOT case sensitive for fieldnames, BUT once the result gets selected and passed to PHP, it WILL be case sensitive. So if you have a table:

ID, fname, lname

And you do a SELECT * FROM tablename...

When this gets passed to PHP, it will become case senstiive, and you'll need to use the exact fieldnames MySQL is using. BUT if you did this:

SELECT id, FName, LName FROM tablename...

MySQL will be fine with this, because its not case sensitive, BUT now you'll have a different result when it gets passed with PHP, with fieldnames id, FName, LName, with those cases...

[guru_del]

Hey that worked. heheh I guess I just need to figure out what is going on with some other stuff.

It looks like this runs fine for some of the records. I need to clean out my database of test records and make sure.

Thanks again and I appreciate your help.

[guru_del]

The problem appears to be when I have multiple items in the table cart with the same cookieID. Which is the case when a person selects more than one item.

Oh well time to figure this out.

Again thanks.