Re: Novice with ERRORS on WHILE (sub)loops.

Mo wrote:
I am trying to build a Employee Order Detail report.
My first hurdle is as follows.

The SO info I am getting isn't listed under the correct user.
It belongs to the user matching the pre-assigned $eID (employee
number) value, not the $eID of the current pointer.
If I DON'T pre-assign a value, I get these ERRORS (which repeat for
each user name):
********
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL
result resource in /home/voyager1/public_html/turbine/moTest.php on
line 46

Warning: current() [function.current]: Passed variable is not an array
or object in /home/voyager1/public_html/turbine/moTest.php on line 52

********
MY CODE:
********
<?PHP
$host = "xxx";
$uid = "xxx";
$pw = "xxx";
$db = "xxx";

$moCon = MySQL_connect($host,$uid,$pw);
MySQL_select_db($db,$moCon);

// temp for test purposes
$eID = 15;

// WHERE statements
//$empWhere = "WHERE";
$agrWhere = "WHERE i.shipDate='2008-05-21' AND i.employeeID=$eID";
$dtlWhere = "WHERE sl.consignmentCode='B-001'";

// Queries
$empQry = "SELECT employeeID, signature FROM Employee";

$agrQry = "SELECT i.shipDate, i.salesOrderID, i.invoiceID, c.name,
c.oemFlag
FROM Invoice as i
LEFT JOIN SalesOrder as so USING (salesOrderID)
LEFT JOIN Company as c ON so.companyID=c.companyID
$agrWhere";

$dtlQry = "SELECT r.soItemID, r.stockID, sl.consignmentCode,
r.shipped, SOi.unitAmount, sl.unitCost, cc.codeCommRate
FROM Reserve as r
LEFT JOIN Stockline as sl USING (stockID)
LEFT JOIN SOItem as SOi USING (salesOrderID,soItemID)
LEFT JOIN ConsignmentCodes as cc ON
sl.consignmentCode=cc.ConsignmentCode
$dtlWhere";

// Query results
$empResult = MySQL_query($empQry,$moCon);
$agrResult = MySQL_query($agrQry,$moCon);
$dtlResult = MySQL_query($dtlQry,$moCon);

// Result arrays
$empArray = MySQL_fetch_array($empResult, MYSQL_NUM);
$agrArray = MySQL_fetch_assoc($agrResult);
$dtlArray = MySQL_fetch_assoc($dtlResult);

while ($empRow = MySQL_fetch_assoc($empResult))
{
$eID = current($agrArray);
echo $empRow["employeeID"] . ") " . $empRow["signature"] . "<br/>";
while ($agrRow = MySQL_fetch_assoc($agrResult))
{
echo $agrRow["salesOrderID"] . "/" . $agrRow["invoiceID"] . "<br/>";

}
$eID++ ;
}

print('<pre>');
print_r($empArray);
print('</pre>');

print('<pre>');
print_r($agrArray);
print('</pre>');

print('<pre>');
print_r($dtlArray);
print('</pre>');
?>
********

THE DESIRED END RESULTS:
We want a report which gives the following info:
-User
-----SO/Invc info
------------SO/Invc Item info
------------SO/Invc Item info
-----SO/Invc info
------------SO/Invc Item info
------------SO/Invc Item info
-User
-----SO/Invc info
------------SO/Invc Item info
------------...etc...

For the time being, I am focusing on getting just the first 2 layers
(names and orders) to behave properly, then I'll add in the Item level
details.

TIA,
~Mo


Your problem is the query itself is failing.

You should ALWAYS check the value returned from mysql_query() (and all other mysql calls). If there is a problem, mysql_query() returns false. When this happens, look at the result from mysql_error().

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@xxxxxxxxxxxxx
==================

.

Relevant Pages

  • Re: Novice with ERRORS on WHILE (sub)loops.
    ... It belongs to the user matching the pre-assigned $eID (employee ... If I DON'T pre-assign a value, I get these ERRORS (which repeat for ... LEFT JOIN SalesOrder as so USING ...
    (comp.lang.php)
  • Re: Novice with ERRORS on WHILE (sub)loops.
    ... My first hurdle is as follows. ... It belongs to the user matching the pre-assigned $eID (employee ... Your problem is the query itself is failing. ... you have a failing query. ...
    (comp.lang.php)