Problem with query only outputting 1 record

amar3d

Hi, i have table that is called cartdvds, in this table i have a 2 fields; email and dvd id; and values such as Joe@Bloggs.com id1, Joe@Bloggs.com id2, Jim@Bloggs.com id3 ect....now the problem i have is that when i try to output all of the records in cart dvd that belong to joe bloggs i only seem to get record id1 output, instead of both records id1 and id3, below is my code, can anyone help with this cos i honestly cant see where i'm going wrong, thanks in advance,

<html>
<body>
Your Shopping Cart

<?php
$email = 'joe@bloggs.com';
$db="test";
$link = mysql_connect("localhost");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
$query1 = "SELECT * from cartdvds WHERE email='$email' ";
$result1 = mysql_query($query1);

while ($row1 = mysql_fetch_array($result1)){

$dvd_id = $row1['dvd_id']; 
}

$query = "SELECT * FROM dvds where dvd_id='$dvd_id'";
$result = mysql_query($query);

echo "<div align=center><table bordercolor=#000000 border=0 width=100% cellspacing=0>\n";
echo "<tr>
<td width=12% height=41>

			<font size=4 face=Courier></font></span></td>
			<td width=10%><span style=font-weight: 400>


			<font size=4 face=Courier></font></span></td>
			<td width=><span style=font-weight: 400>

			<font size=4 face=Courier></font></span></td>
			<td width=><span style=font-weight: 400>
		</tr>\n";

while ($row = mysql_fetch_array ($result)) {

echo " <tr>
<td > </td>

<td >{$row['dvd_name']} {$row["genre"]}{$row["synopsis"]}
 Price: {$row["dvd_price"]}
</td>

</tr>\n\n";
}
echo '</table>'

?>
</body>
</html>

Drakla

There are a couple of mistakes in your looping logic - this code is untested as I've had to try editing without you using the PHP tags and I'm useless without syntax highligting

<html>
<body>
<p align='center'><font face="Courier"><b><font size="5">Your Shopping Cart</font> </b></font></p>



<?php
$email = 'joe@bloggs.com';
$db="test";
$link = mysql_connect("localhost");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
$query1 = "SELECT * from cartdvds WHERE email='$email' ";
$result1 = mysql_query($query1);

while ($row1 = mysql_fetch_array($result1)){

$dvd_id[] = $row1['dvd_id'];	// you were just maiking one id equal the same thing every time
}

$dvd_ids = implode(',', $dvd_id);	// assuming the ids are numeric

//$query = "SELECT * FROM dvds where dvd_id='$dvd_id'";
$query = "SELECT * FROM dvds where dvd_id IN ($dvd_ids)";
$result = mysql_query($query);

echo "<div align=center><table bordercolor=#000000 border=0 width=100% cellspacing=0>\n";
echo "<tr>
<td width=12% height=41><span style=font-weight: 400>

<font size=4 face=Courier></font></span></td>
<td width=10%><span style=font-weight: 400>


<font size=4 face=Courier></font></span></td>
<td width=><span style=font-weight: 400>

<font size=4 face=Courier></font></span></td>
<td width=><span style=font-weight: 400>
</tr>\n";

while ($row = mysql_fetch_array ($result)) {

echo " <tr>
<td ><font size=4 face=Courier> </font></td>

<td ><font size=4 face=Courier> <img src='{$row['thumb']}'></font><p></p></td>


<td ><font size=4 face=Courier><b>{$row['dvd_name']}</b></br><b> {$row["genre"]}</b></br>{$row["synopsis"]}
</br> <b> Price:</b> {$row["dvd_price"]}</font>
</br></td>



</tr>\n\n";
}
echo '</table>'


?>
</body>
</html>[/pHP]