Re: Novice with ERRORS on WHILE (sub)loops.
- From: Jerry Stuckle <jstucklex@xxxxxxxxxxxxx>
- Date: Tue, 10 Jun 2008 14:48:58 -0400
Mo wrote:
On Jun 9, 5:30 pm, Jerry Stuckle <jstuck...@xxxxxxxxxxxxx> wrote:Mo wrote:On Jun 9, 4:59 pm, Jerry Stuckle <jstuck...@xxxxxxxxxxxxx> wrote:Nonetheless, you have a failing query (or more). Do what I said andMo wrote:I have proven all my queries in PMA, and my print_r() statements allI am trying to build a Employee Order Detail report.Your problem is the query itself is failing.
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
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.
jstuck...@xxxxxxxxxxxxx
==================
return a valid dataset.
Additionally, these errors only occur if I don't predefine the $eID
variable.
Am I missing something?
~Mo
you'll see the problem.
Not knowing which is line 46, I can't say which query is failing.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@xxxxxxxxxxxxx
==================
Turns out that the cause of the errors is very simple.
If I don't predefine the var (or even define it as an empty string),
an incomplete WHERE statement results, and the query fails due to a
syntax error.
Other than that, my queries are clean and proven. No mysql_error()
results, and the array gets iterated through nicely if done by itself
in a WHILE loop.
So ultimately, my question still stands.
How do I get the aggregate query to use the EmployeeID value from the
current iteration through the employee query?
(How do I get the SO Info to list under the correct User on the
report?)
~Mo
Any time you need a different employee id, you need to issue a new SQL query. So each time through your iteration, you would need to rebuild and reissue the aggregate query. You're reissuing the query - but not rebuilding it first.
Or you could just have one query get all of the information at one time, which would be much more efficient. Try comp.databases.mysql for help on how to do that.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@xxxxxxxxxxxxx
==================
.
- Follow-Ups:
- References:
- Novice with ERRORS on WHILE (sub)loops.
- From: Mo
- Re: Novice with ERRORS on WHILE (sub)loops.
- From: Jerry Stuckle
- Re: Novice with ERRORS on WHILE (sub)loops.
- From: Mo
- Re: Novice with ERRORS on WHILE (sub)loops.
- From: Jerry Stuckle
- Re: Novice with ERRORS on WHILE (sub)loops.
- From: Mo
- Novice with ERRORS on WHILE (sub)loops.
- Prev by Date: looking for menu--navigation code
- Next by Date: Re: Novice with ERRORS on WHILE (sub)loops.
- Previous by thread: Re: Novice with ERRORS on WHILE (sub)loops.
- Next by thread: Re: Novice with ERRORS on WHILE (sub)loops.
- Index(es):
Relevant Pages
|
