For Loop Getting Messed Up

[coolboarderguy]

Hi All,

the below code displays the records correctly, but, it doesn't display the 2nd value, ProductTypes.product_type_detail. If I add the following,

$myresult = mysql_fetch_row($result);

just b4 the echo line and change $result[1] to $myresult[1], it displays it correctly, but, the for loop gets messed up. It only displays 1 product and the 2nd product row is missing the product name, but displays the more details link.
What do I need to do to get this to work correctly..? Thanx.

if (isset($_REQUEST[product_type_id])){
  $product_type_id = $_REQUEST[product_type_id];
$result = mysql_query("SELECT Products.product_qty, ProductTypes.product_type_detail, Makers.maker_detail,
Products.product_id, Products.product_name, Products.product_model_number FROM Products INNER JOIN (ProductTypes 
INNER JOIN Makers ON Products.maker_id = Makers.maker_id)
 ON Products.product_type_id = ProductTypes.product_type_id
WHERE ProductTypes.product_type_id = '$product_type_id'");
if (!$result) {
  echo "Query failed: " . mysql_error();
  exit;
}
$num = mysql_num_rows($result);
echo "<tr align=\"center\"><td colspan=\"3\"><center>Status-".$result[1]."</center></td>
</tr><tr align=\"center\">
<td>Product Name</td><td>Quantity</td><td>More Details</td></tr><br>";
if ($num < 1) {
echo "<tr align=\"center\"><td colspan=\"3\"><center>Sorry, 
no products in that category</center></td></tr>";
}
for ($i=0; $i < $num; $i++){
$myrow = mysql_fetch_array($result);
$product_name = $myrow["product_name"];
$product_price = $myrow["product_price"];
$product_model_number = $myrow["product_model_number"];
$product_id = $myrow["product_id"];
$product_qty = $myrow["product_qty"];
echo "<tr align=\"center\"><td> $product_name</td><td>
$product_qty</td><td><a href=\"productdetails.php?product_id=$product_id\">More Details</a></td></tr><br>";
}
}
}
?>

[bpat1434]

A for() loop is kind of useless, you're better off using a while() loop:

while($myrow = mysql_fetch_array($result)){
    $product_name = $myrow["product_name"];
    $product_price = $myrow["product_price"];
    $product_model_number = $myrow["product_model_number"];
    $product_id = $myrow["product_id"];
    $product_qty = $myrow["product_qty"];
    echo "<tr align=\"center\"><td> $product_name</td><td>
$product_qty</td><td><a href=\"productdetails.php?product_id=$product_id\">More Details</a></td></tr><br>";
}

~Brett

[coolboarderguy]

Hi All,

well, changing to a while gave me weirdness. Only the second record was displayed; twice.

while($myrow = mysql_fetch_array($result)){ 
$product_name = $myrow["product_name"];
$product_price = $myrow["product_price"];
$product_model_number = $myrow["product_model_number"];
$product_id = $myrow["product_id"];
$product_qty = $myrow["product_qty"];
echo "<tr align=\"center\"><td> $product_name</td><td>$product_qty</td><td><a href=\"productdetails.php?product_id=$product_id\">More Details</a></td></tr><br>";
}

Why is that..?

[bpat1434]

Then you have a query issue.

I'm having trouble wrapping my head around what it is you're doing with the query. You reference table aliases, but dont' define them in the query. If ProductTypes, Makers, and Products are all different tables, then your query should probably start off looking like:

SELECT product_qty, product_type_detail, maker_detail,
product_id, product_name, product_model_number
FROM Products
INNER JOIN (ProductTypes
INNER JOIN Makers ON Products.maker_id = Makers.maker_id)

ON Products.product_type_id = ProductTypes.product_type_id

WHERE ProductTypes.product_type_id = '$product_type_id'

~Brett

[coolboarderguy]

Hi All,

my query is in my 1st post. Nothing wrong with it, that I can see.

$result = mysql_query("SELECT Products.product_qty, ProductTypes.product_type_detail, Makers.maker_detail,
Products.product_id, Products.product_name, Products.product_model_number FROM Products INNER JOIN (ProductTypes 
INNER JOIN Makers ON Products.maker_id = Makers.maker_id)
 ON Products.product_type_id = ProductTypes.product_type_id
WHERE ProductTypes.product_type_id = '$product_type_id'");

[bpat1434]

Well, yes, but have run it through mySQL to see exactly what you get?

Also, try adding an ORDER BY statement to it. That may help.

~Brett

[coolboarderguy]

Hi All,

the code as is below, displays all records correctly.
Problem is, trying to display the values of, product_type_detail and/or maker_detail in the 1st row.
For example, if the user chooses Cisco from Makers, the 1st row will look like this,

Status-Cisco

2nd Row like this,

Product Name Quantity More Details

3rd row would then display the record data,

Catalyst 2900 XL 2 <More Details>

Now, when I try to do this, I get all sorts of problems. How would you do it..?

<?php
$db = mysql_connect("localhost", "root", "grunger");
if (!$db) {
  echo "no connection because " . mysql_error();
  exit;
}
mysql_select_db("status", $db);
if (isset($_REQUEST[maker_id])){
  $maker_id = $_REQUEST[maker_id];
$result = mysql_query("SELECT Products.product_qty,
Products.product_id, Products.product_name,
Products.product_price, Products.product_model_number, Makers.maker_detail 
FROM Products INNER JOIN Makers ON Products.maker_id = Makers.maker_id WHERE Makers.maker_id = '$maker_id'");
if (!$result) {
  echo "Query failed: " . mysql_error();
  exit;
} 
$num = mysql_num_rows($result);
echo "<tr align=\"center\"><td colspan=\"3\"><center>Status-</center>
</td></tr><tr align=\"center\"><td>Product Name</td><td>Quantity</td><td>More Details</td></tr><br>";
if ($num < 1) {
echo "<tr align=\"center\"><td colspan=\"3\">Sorry, no products in that category</td></tr>";
}
while($myrow = mysql_fetch_array($result)){
echo "<tr align=\"center\"><td>
".$myrow['product_name']."
</td><td>".$myrow['product_qty']."
</td><td><a href=\"productdetails.php?product_id
=".$myrow['product_id']."\">More Details</a></td></tr><br>";
}
}
else {
if (isset($_REQUEST[product_type_id])){
  $product_type_id = $_REQUEST[product_type_id];
$result = mysql_query("SELECT Products.product_qty, ProductTypes.product_type_detail, Makers.maker_detail,
Products.product_id,
Products.product_name, Products.product_model_number 
FROM Products INNER JOIN (ProductTypes 
INNER JOIN Makers ON 
Products.maker_id = Makers.maker_id) ON 
Products.product_type_id = ProductTypes.product_type_id WHERE ProductTypes.product_type_id = '$product_type_id'");
if (!$result) {
  echo "Query failed: " . mysql_error();
  exit;
}
$num = mysql_num_rows($result);
echo "<tr align=\"center\"><td colspan=\"3\"><center>Status-</center></td>
</tr><tr align=\"center\"><td>Product Name</td><td>Quantity</td><td>More Details</td></tr><br>";
if ($num < 1) {
echo "<tr align=\"center\"><td colspan=\"3\"><center>Sorry, no products in that category</center></td></tr>";
}
 while($myrow = mysql_fetch_array($result)) {
echo "<tr align=\"center\"><td>" .$myrow['product_name']."</td><td>
".$myrow['product_qty']."</td><td><a href=\"productdetails.php?product_id="
.$myrow['product_id']."\">More Details</a></td></tr><br>";
}
}
}
?>