MySQL query works on CLI or Query Browser but NOT in PHP

CraigB · #1 (**permalink**) 09-21-2006, 01:46 PM

This query (yes, there is a join of one table to itself) works properly on the command line or using MySQL Query Browser, but fails when attempted in a PHP script

Code:

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;

I won't give this level of access to anybody else. Any suggestions on how to make it work as a script so that I can have others easily fetch the resulting report? (Ultimately needs to save results as a .csv)

Thanks,

Craig

**wige** · #2 (**permalink**) 09-21-2006, 03:23 PM

What error message do you get?

After running the query, add the following code

Code:

if (!$result) {
   die('Invalid query: ' . mysql_error());
}

CraigB · #3 (**permalink**) 09-21-2006, 05:56 PM

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>

**wige** · #4 (**permalink**) 09-28-2006, 10:33 AM

Might this be the problem? Rather than it returning the same thing, I think you might be displaying the same field twice.

Code:

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

At the end of each of the do loops, you can add the following code which will output the exact contents of the query response:

Code:

echo '<!-- ';
print_r($row_QUERY1);
echo ' -->';

CraigB · #5 (**permalink**) 09-28-2006, 11:39 AM

The solution:

Code:

$query_QUERY1 = "SELECT O.order_id, O.SKU, I2.misc3 as misc3a, ..."

and

Code:

<td><?php echo $row_QUERY1['misc3a']; ?></td>

repeat as needed.

Thanks for the input! I've been approaching this for way toooo long.

Craig

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode