MySQL/PHP results unexpected - query help

[knippysing]

I'm trying to run a query that locally (on MS Access) has the results I would expect. When I transfer them to MySQL/PHP I get uunexpected results. I have multiple IDs (EquipID) for each result but instead of displaying the multiple IDs for each it's showing the right NUMBER of results but the multiples are only displaying the first ID of each list.

The query/code I am using is below. I know you don't know the data but was wondering if there are any red flags in the code as it is that would stand out to you and you could tell me what I'm doing wrong.
Thanks as always!

  <?php
require 'connect.php';
$date = date('Y-m-d');
$date2 = explode("-",$date);
$mktime=mktime(0,0,0,$date2[1],$date2[2],$date2[0]);
$period = date( 'm', $mktime );
$month = date( 'F', $mktime );
print "For the month of $month:";
$sqlquery1 = "SELECT EquipAtt.Invoice, Dispatch.Terms, AgrSched.Period, AgrSched.Task, AgrSched.Date, Dispatch.CustNo, Dispatch.LocNo, Dispatch.Dispatch, Dispatch.CalledInBy, Dispatch.RecDate, Dispatch.RecTime, Dispatch.RecBy, Dispatch.Invoice, Dispatch.ServAgrNum, Dispatch.ServAgrPer, Dispatch.TaskNo, Dispatch.Notes, Equip.Mfg, Equip.EqType, Equip.Model, Equip.Serial, Equip.EquipLoc, Equip.Notes, Equip.Source, Equip.ID
FROM AgrSched INNER JOIN (Equip INNER JOIN (Dispatch INNER JOIN EquipAtt ON (Dispatch.Invoice = EquipAtt.Invoice) AND (Dispatch.Dispatch = EquipAtt.Dispatch)) ON (Dispatch.ServAgrNum = Equip.SerAgrNo) AND (Equip.LocNo = Dispatch.LocNo) AND (Equip.CustNo = Dispatch.CustNo) AND (Equip.Counter = EquipAtt.Count)) ON (AgrSched.Period = Dispatch.ServAgrPer) AND (AgrSched.AgrmtNo = Equip.SerAgrNo) AND (AgrSched.CustNo = Equip.CustNo) AND (AgrSched.LocNo = Equip.LocNo)
WHERE (((Dispatch.Terms)='SA') AND ((Dispatch.Complete) Is Null) AND ((Dispatch.CustNo)='$custno') AND ((Dispatch.LocNo)='$locno') AND ((AgrSched.Period)='$period'))
ORDER BY AgrSched.Date";
$result = mysql_query($sqlquery1) or die (mysql_error());
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {
$a=mysql_result($result,$i,"ID");
$bb=mysql_result($result,$i,"Date");
$bbb = explode("-",$bb);
$bbbb=mktime(0,0,0,$bbb[1],$bbb[2],$bbb[0]);
$b = date( 'm/d/Y', $bbbb );
$e=mysql_result($result,$i,"ServAgrNum");
$task=mysql_result($result,$i,"Task");
$period=mysql_result($result,$i,"Period");
$custno=mysql_result($result,$i,"CustNo");
$locno=mysql_result($result,$i,"LocNo");
$model=mysql_result($result,$i,"Model");
$mfg=mysql_result($result,$i,"Mfg");
$serial=mysql_result($result,$i,"Serial");
$type=mysql_result($result,$i,"EqType");
print ($i % 2)? "<tr bgcolor=\"F0F0F0\">" : "<tr bgcolor=\"FFFFFF\">";
print "<td align=left><font size=2 px><a href=pm_tasks.php?usr=$usr&equip=$a&task=$task&custno=$custno&locno=$locno&per=$period>$a</a></font></td>";
print "<td align=left><font size=2 px>$b&nbsp;</font></td>";
print "<td align=left><font size=2 px>$mfg $type<br><font color='#66000'>Model:$model<br>Serial#:$serial</font></font></td>";
print "<td align=left><font size=2 px>$e</font></td>";
++$i;
}
?>

[mcibor]

Try tu use this query on phpMyAdmin and then explaining it.

I don't see a possibility for it to sum things up.

PS. If you wish to read mysql result simply:

while($arr = mysql_fetch_assoc($result))
{
$a = $arr['ID'];
...
}

Sorry for not solving your problem
Regards
Michal

[eelixduppy]

Try a query like so:

$sqlquery1 = "SELECT EquipAtt.Invoice, Dispatch.Terms, AgrSched.Period, AgrSched.Task, AgrSched.Date, Dispatch.CustNo, Dispatch.LocNo, Dispatch.Dispatch, Dispatch.CalledInBy, Dispatch.RecDate, Dispatch.RecTime, Dispatch.RecBy, Dispatch.Invoice, Dispatch.ServAgrNum, Dispatch.ServAgrPer, Dispatch.TaskNo, Dispatch.Notes, Equip.Mfg, Equip.EqType, Equip.Model, Equip.Serial, Equip.EquipLoc, Equip.Notes, Equip.Source, Equip.ID
FROM [b]AgrSched,EquipAtt,Dispatch,Equip[/b] WHERE (((Dispatch.Terms)='SA') AND ((Dispatch.Complete) Is Null) AND ((Dispatch.CustNo)='$custno') AND ((Dispatch.LocNo)='$locno') AND ((AgrSched.Period)='$period'))
ORDER BY AgrSched.Date"; 

This makes it a little easier, and then you can add your joins as you go. Make sure this works first, though :)

Good luck!