Query within While Loop Question

[LinusIT]

Quite a simply page, basically it shows a table with the contents from a db query: $sql="SELECT * FROM scrapped ORDER BY scrapped_date_logged ASC";
$result = mysql_query($sql) or die(mysql_error());. The page renders fine however with two fields it shows numerical values which I would like to run a query on.

Here's the code I've got:

<?php
$color1 = "#D7DFFF";
$color2 = "#F3F3F3";
$row_count = 0;

while($row=mysql_fetch_array($result)) {
$row_color = ($row_count % 2) ? $color1 : $color2;
?>
<tr style="background-color:<?php echo $row_color ?>;">
<td><?=$row['scrapped_date_in']?></td>
<td><?=$row['scrapped_date_scrapped']?></td>
<?php $manufacturer = $row['scrapped_make'];
$sql_man="SELECT * FROM manufacturer WHERE manufacturer_id = '".$manufacturer."'";
$res_man = mysql_query($sql_man) or die(mysql_error());
?>
<td><?=$res_man['manufacturer_name']?></td>
<td><?=$row['scrapped_model']?></td>
<td><?=$row['scrapped_registration']?></td>
<td><?=$row['scrapped_entered_by']?></td>
<td><?=$row['scrapped_last_keeper']?></td>
</tr>
<?php $row_count++;
}?>

So the Manufacturer & Entered By fields are just an ID number from another table. I'd like to select the manufacturer name based on the ID and the same for the Entered By.

Any pointers would be great, thank you.

[g1smd]

One note about the code layout.

You should separate this code into two loops.

The first loop reads the database into an array, and produces error messages into an array if there is a problem.

A separate loop should write the HTML data while reading from the array, or show the error messages if there are any.

The code loop reading from the database should be before the point in the code where the HTML DOCTYPE is sent to the browser.

Among other things, this allows you to return a 404 header if the database read returns no results. It's too late to return a 404 if you have already sent a chunk of the HTML page, DOCTYPE, HTML headers, etc.

[rocknbil]

So the Manufacturer & Entered By fields are just an ID number from another table. I'd like to select the manufacturer name based on the ID and the same for the Entered By.

So you have a manufacturer's table and a "user" table for entered by, right?

$sql="SELECT scrapped.*, manufacturers.name as mfg, user.fname as ufname, user.lname as ulname FROM scrapped, manufacturers, user where scrapped.mfg_id=manufacturers.mfg_id and scrapped.user_id=user.user_id ORDER BY scrapped.scrapped_date_logged ASC";

(etc.)

echo "<p> mfg " . $row['mfg'] . " entered by " . $row['ufname'] . " " . $row['ulname'] . "</p>";

You could also use left/inner/outer join syntax if you're more comfortable with it.

[LinusIT]

Thanks for the replies, I understand what's been suggested but I don't think I've explained the situation properly.

At the top of the page I have

$sql="SELECT * FROM scrapped ORDER BY scrapped_date_logged ASC";
$result = mysql_query($sql) or die(mysql_error());

Then in the body I have:

<table cellpadding="0" cellspacing="0" id="models">
<tr class="headings">
<th class="w100">Date In</th>
<th class="w100">Date Scrapped</th>
<th class="w140">Manufacturer</th>
<th class="w80">Model</th>
<th class="w80">Registration</th>
<th class="w140">Entered By</th>
<th class="w90">Last Keeper</th>
</tr>
<?php
$color1 = "#D7DFFF";
$color2 = "#F3F3F3";
$row_count = 0;

while($row=mysql_fetch_array($result)) {
$row_color = ($row_count % 2) ? $color1 : $color2;
?>
<tr style="background-color:<?php echo $row_color ?>;">
<td><?=$row['scrapped_date_in']?></td>
<td><?=$row['scrapped_date_scrapped']?></td>
<?php $manufacturer = $row['scrapped_make'];
$sql_man="SELECT * FROM manufacturer WHERE manufacturer_id = '".$manufacturer."'";
$res_man = mysql_query($sql_man) or die(mysql_error());
?>
<td><?=$res_man['manufacturer_name']?></td>
<td><?=$row['scrapped_model']?></td>
<td><?=$row['scrapped_registration']?></td>
<td><?=$row['scrapped_entered_by']?></td>
<td><?=$row['scrapped_last_keeper']?></td>
</tr>
<?php $row_count++;
}?>
</table>

The code in bold is my attempt in getting it working, not a great start though.

What's happening is the Manufacturer & Entered By columns are being populated with numerical values. I have two other tables (manufacturer & employees), in these tables I have an ID number and then the name.

Manufacturer:

ID 1 = Alfa Romeo
ID 2 = Audi

Employees:

ID 1 = John Smith
ID 2 = Rob Snow

So what I'd like to do is perform two more queries for each row returned, thus being select manufacturers name and employees name based on the values in those two fields. The results will/can be different for each returned row.

[rocknbil]

Nah I got you (I think,) what you are doing is a while loop on the outer table, then an inner select inside the loop to get manufacturer name. I'm saying you could do that in one select with joins, pretty much as I posted.

No matter. I see what's wrong (also "I think.") you missed the fetch row or fetch assoc . . . . so it's the $res_man object that is returning numbers. You need to fetch the array from it.

<?php $manufacturer = $row['scrapped_make']; 
$sql_man="SELECT manufacturer_name FROM manufacturer WHERE manufacturer_id = '".$manufacturer."'"; 
$res_man = mysql_query($sql_man) or die(mysql_error()); 
if ($row2 = mysql_fetch_array($res_man)) { // no need for while, should only be one, right? 
 $manvalue = $row2['manufacturer_name']; 
} 
else { $manvalue = 'N/A'; } 
?> 
 <td><?php echo $manvalue;?></td> 
 <td><?php echo $row['scrapped_model'];?></td> 
 <td><?php echo $row['scrapped_registration'];?></td> 
 <td><?php echo $row['scrapped_entered_by'];?></td> 
 <td><?php echo $row['scrapped_last_keeper'];?></td> 
</tr> 

Note also I stored it in $row2 so it doesn't overwrite $row of the outer loop, and also since we need only one field, select the manufacturer_name field only - * is overhead if you don't need it.

I don't see where you're getting "entered by" though. Like I said, you can do all this in your first statement, the outer one, and then all those **would** be stored in $row.

[LinusIT]

Thank you for your help (once again) rocknbill. Your code has worked and the results now populate correctly.

Here's the working code:

<?php
$color1 = "#D7DFFF";
$color2 = "#F3F3F3";
$row_count = 0;

while($row=mysql_fetch_array($result)) {
$row_color = ($row_count % 2) ? $color1 : $color2;
?>
<tr style="background-color:<?php echo $row_color ?>;">
<td><?=$row['scrapped_date_in']?></td>
<td><?=$row['scrapped_date_scrapped']?></td>
<?php $manufacturer = $row['scrapped_make'];
$sql_man="SELECT manufacturer_name FROM manufacturer WHERE manufacturer_id = '".$manufacturer."'";
$res_man = mysql_query($sql_man) or die(mysql_error());
if ($row2 = mysql_fetch_array($res_man)) {
$manvalue = $row2['manufacturer_name'];
}
else { $manvalue = 'N/A'; }
?>
<td><?php echo $manvalue;?></td>
<td><?=$row['scrapped_model']?></td>
<td><?=$row['scrapped_registration']?></td>
<?php $employee = $row['scrapped_entered_by'];
$sql_emp="SELECT employee_name FROM employees WHERE employee_id = '".$employee."'";
$res_emp = mysql_query($sql_emp) or die(mysql_error());
if ($row3 = mysql_fetch_array($res_emp)) {
$empvalue = $row3['employee_name'];
}
else { $empvalue = 'N/A'; }
?>
<td><?php echo $empvalue;?></td>
<td><?=$row['scrapped_last_keeper']?></td>
</tr>
<?php $row_count++;
}?>
</table>
<?php } ?>

I am interested in how this would be done using a join statement if that's more efficient.

Thanks again for your help.