It works - but doesn't return both parent AND child values for misc3 (table joins itself). It does return both values outside of the query in PHP by command line or MySQL Query Browser.
here's the
whole thing (via Dreamweaver then pasting in the complex query). It returns the child value twice.
I don't see a way for PHP to distinguish between I2.misc and I3.misc3 values.
Code:
<?php
mysql_select_db($database_mas, $bogus_mas);
$query_QUERY1 = "SELECT O.order_id, O.SKU, I2.misc3, I3.misc3, O.quantity, OS.date_placed
FROM INVENTORY AS I2
LEFT JOIN INVENTORY as I3 ON I2.parent_sku=I3.SKU,
ORDERLINES O
LEFT OUTER JOIN ORGANIZATION OG ON O.SKU=OG.SKU,
ORDERS OS
WHERE OS.date_placed >= '2006-09-20 15:00:01' AND O.status = 'Processing'
AND OG.category = '35482' AND OS.order_id=O.order_id AND I2.SKU=O.SKU
ORDER by SKU, O.order_id ASC";
$QUERY1 = mysql_query($query_QUERY1, $bogus_mas) or die(mysql_error());
$row_QUERY1 = mysql_fetch_assoc($QUERY1);
$totalRows_QUERY1 = mysql_num_rows($QUERY1);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<table border="1" cellpadding="2" cellspacing="0">
<tr>
<td>order_id</td>
<td>SKU</td>
<td>misc3</td>
<td>misc3</td>
<td>quantity</td>
<td>date_placed</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_QUERY1['order_id']; ?></td>
<td><?php echo $row_QUERY1['SKU']; ?></td>
<td><?php echo $row_QUERY1['misc3']; ?></td>
<td><?php echo $row_QUERY1['misc3']; ?></td>
<td><?php echo $row_QUERY1['quantity']; ?></td>
<td><?php echo $row_QUERY1['date_placed']; ?></td>
</tr>
<?php } while ($row_QUERY1 = mysql_fetch_assoc($QUERY1)); ?>
</table>