Hey guys,
Hoping someone can help me out here, I have a query thats pulling essentially an order record from multiple tables, it pulls all of the customer details etc as it should however when the order contains more than 1 part for example instead of just returning 1 set of order details and repeating the parts it repeats all of the information for each part.
When i noticed this i split the parts query off from the main one and it groups the parts together but still shows 2 sets of customer details.
Hope someone can point me in the right direction i could use a fresh set of eyes.
// Check for a valid part ID, through GET or POST.
if ( (isset($_GET['id'])) ) { // Accessed through parts_search.php
$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) ) { // Form has been submitted.
$id = $_POST['id'];
} else { // No valid ID, kill the script.
echo '<div id="title">Page Error</div>
<p class="error">This page has been accessed in error.</p><p><br /><br /></p>';
exit();
}
require_once ('mysql_connect.php'); // Connect to the db.
//Make the Query
$result = mysql_query("SELECT customers_log.*, shipping.*, orders.*, parts_ordered.* FROM customers_log, shipping, orders, parts_ordered WHERE customers_log.customer_id = shipping.customer_id AND shipping.shipping_id = orders.shipping_id AND orders.order_id = parts_ordered.order_id AND parts_ordered.order_id='$id'");
while($row = mysql_fetch_array($result))
//Print Data
{
echo '<table width="700" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="7" bgcolor="#FFFFFF"><img src="images/powerlogo.gif" width="300" height="39" alt="PowerCart" /></td>
</tr>
<tr>
<td colspan="4"><strong>Customer Details</strong></td>
...
<tr>
<td>Tracking #:</td>
<td colspan="2">'; echo $row['tracking']; echo '</td>
<td></td>
<td colspan="3"></td>
</tr>
<tr>
<td colspan="7"> </td>
</tr>
</table><p>';
}
// Make the query.
$query = "SELECT * FROM parts_ordered WHERE order_id='$id'";
$result = @mysql_query ($query); // Run the query.
// Table header.
echo '<table cellspacing="5" cellpadding="0" width="650px">
<tr>
<td align="left"><b>Item</b></td>
<td align="left"><b>Description</b></td>
<td align="left"><b>Quantity</b></td>
<td align="left"><b>Serial</b></td>
</tr>
';
// Fetch and print all the records.
$bg = '#eeeeee'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switch the background color.
echo '<tr bgcolor="' . $bg . '">
<td align="left">' . $row['partid'] . '</td>
<td align="left">' . $row['desc'] . '</td>
<td align="left">' . $row['quantity'] . '</td>
<td align="left">' . $row['serial'] . '</td>
</tr>
';
}
echo '</table>';