[Resolved] not doing what it should

rocket

i wrote this code to bring back every thing in the table that it finds but it is only bringing back the first thing it finds could someone please help?

<?
session_start();
?><?
$usr = "ecom";
$pwd = "*";
$db = "ecom";
$host = "localhost";

# connect to database
$cid = mysql_connect($host,$usr,$pwd);
if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }

?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>Items in your cart<p></p><?
$account=$_SESSION["account"];

$SQL = "SELECT * FROM cart WHERE account LIKE '$account'";

# execute SQL statement
$retid = mysql_db_query($db, $SQL, $cid);

# check for errors
if (!$retid) { echo( mysql_error()); }
else {

    # display results

    while ($row = mysql_fetch_array($retid)) {
        $itemnumber = $row["itemnumber"];
        $account1 = $row["account"];
		$price1 = $row["price1"];
		}
		$SQL = "SELECT * FROM video WHERE itemnumber LIKE '$itemnumber'";
		  $retid = mysql_db_query($db, $SQL, $cid);
		 if (!$retid) { echo( mysql_error()); }
else {
 while ($row = mysql_fetch_array($retid)) {
         $smallpic = $row["smallpic"];
		$name = $row["name"];
		$price = $row["price"];
		$type = $row["type"];

		echo ("<DD><p><table width=75% border=1 cellspacing=4 cellpadding=15>

<tr>
<td>$smallpic</td>
<td>Name: $name</td>
<td>Type: $type</td>
<td> Cost: $price</td>

</tr>
</table></DD>\n");}}}?>
</body>
</html>

barand

I think you'll find the problem is here

while ($row = mysql_fetch_array($retid)) { 
$itemnumber = $row["itemnumber"]; 
$account1 = $row["account"]; 
$price1 = $row["price1"]; 
}

Every time you fetch the next row you store the contents overwriting the contents of the previous row.

You need to extend this while loop to include the video table processing.

It would be more efficient to have a single joined query and do all the processing on the results of that.

rocket

when i do what you said to do all i get is a blank page but what did you mean by joining the two?

Thanks

rocket

disregard what i just said i got it to work you were right thanks

barand

Join the tables on itemnumber instead of getting item nuber from one then querying the other table

$SQL = "SELECT * FROM cart c INNER JOIN video v
ON c.itemnumber = v.itemnumber
WHERE c.account = '$account'";

execute SQL statement

$retid = mysql_db_query($db, $SQL, $cid);

check for errors

if (!$retid) { echo( mysql_error()); }
else {

display results

while ($row = mysql_fetch_array($retid)) {
$smallpic = $row["smallpic"];
$name = $row["name"];
$price = $row["price"];
$type = $row["type"];

echo ("<DD><p><table width=75% border=1 cellspacing=4 cellpadding=15>
<tr>
<td>$smallpic</td>
<td>Name: $name</td>
<td>Type: $type</td>
<td> Cost: $price</td>

</tr>
</table></DD>\n");}}}?>
</body>
</html>