[SOLVED] Help with Select query on multiple tables.

[cmaclennan]

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>';