FOREACH statement problem

[kpage]

Trying to process a dataset returned from mysql. my query returns info, ordered by various events, and the number of days a record within that event has existed, ie been waiting. I am trying(for days!) to organize the data into a table, ordered for each event and showing the number of records per event that have been waiting < 30 days, < 60 days etc. I have put together the following, although I am note sure if its strictly php or if I am making this up. Any direction would be greatly appreciated!

$list.= "<table border=\"1\ cellpadding=\"0\" cellspacing=\"1\" align=\"center\">";
$list.="<tr><th>Event Name</th>";
$list.="<th>Less than 1 month</th>";
$list.="<th>1-2 months</th>";
$list.="<th>2-3 months</th>";
$list.="<th>3-4 months</th>";
$list.="<th>4-5 months</th>";
$list.="<th>5-6 months</th>";
$list.="<th>Over 6 months</th><tr>";

while ($row= mysql_fetch_array($rs))
while ($row2= mysql_fetch_array($rs2))

foreach ($row2 as $key=>$val)) {
$event = $row2[$key]["eventid"];
foreach ($row where $row["wname"]=$event){
switch ($row["days"]) {

case "($row["days"]<= 30)":
$month = $month++;
break;
case "($row["days"] between 31 and 60)")":
$month2 = $month2++
break;
case "($row["days"] between 61 and 90)")":
$month3 = $month3++
break;
case "($row["days"] between 91 and 120)")":
$month4 = $month4++
break;
case "($row["days"] between 121 and 150)")":
$month5 = $month5++
break;
case "($row["days"] between 151 and 180)")":
$month6 = $month6++
break;
case "($row["days"] > 180")":
$month7 = $month7++
break;

{

$list.="<tr>";

$list.="<td>".$row["$event"]."</td>";
$list.="<td>".$row1["$month"]."</td>";
$list.="<td>".$row2["$month2"]."</td>";
$list.="<td>".$row3["$month3"]."</td>";
$list.="<td>".$row4["$month4"]."</td>";
$list.="<td>".$row5["$month5"]."</td>";
$list.="<td>".$row5["$month6"]."</td>";
$list.="<td>".$row5["$month7"]."</td>";
$list.= "</tr>";

}

}
}
}

$list.="</table>";

echo($list);

?>

[Zipper]

well, the entire thing seem to be at the peak of confusion and I really doubt you need a foreach statement at all. could you give us a bit more information on the database (table) from which you are retrieving the records, like the fields, their descriptions and also the current queries ur using.

[kpage]

Table descs:
waitinglist ¦ CREATE TABLE `waitinglist` (
`waitid` int(11) NOT NULL auto_increment,
`bhrid` varchar(10) default NULL,
`doctorid` int(11) default NULL,
`datereceived` date default NULL,
`eventid` varchar(10) default NULL,
`appdate` date default NULL,
`status` varchar(25) default NULL,
`comment` text,
PRIMARY KEY (`waitid`),

waitevents ¦ CREATE TABLE `waitevents` (
`weventid` int(11) NOT NULL auto_increment,
`wname` varchar(50) NOT NULL default '',
PRIMARY KEY (`weventid`)

query:

select c.wname, ((TO_DAYS(NOW())-(TO_DAYS(w.datereceived)))) as days
from waitinglist as w,waitevents as c
where w.appdate Is Null and w.eventid = c.weventid
order by c.wname,days;"

Thanks in advance for any help you can provide!

[grandpa]

I am trying(for days!) to organize the data into a table, ordered for each event and showing the number of records per event that have been waiting < 30 days, < 60 days etc.

I've done this sort of thing recently. Let's call various events 'Sales Orders'. The critical step will be to query your existing data correctly. After that, piece of cake.
I'm collecting Sales Order Numbers (events), which in this case have an Open status in the detail record. My query looks like this:

$sql = "SELECT DISTINCT ordnum, orddat from sales_journal
LEFT JOIN journal_detail ON sales_journal.ordnum=journal_detail.orderid
WHERE journal_detail.status = 'O' ORDER BY orddat DESC";

That give me a complete list of events with an Open status, and in that recordset I have unique (DISTINCT) Order Numbers and date. Then I get the date span from today, for each of the orders. For that I use a class and it returns the number of days.

$dateresult = $MyDate->Span(days,$wkdat,$now);

Now all I need to do is use those records that are within the time frame (number of days) that I specify. $dateresult contains the number of days from today. In my case, I plan to display these records in a drop drown list, so I'm storing them in an array. You can just as easily write them to a table.

if ($dateresult <= 5) {
$orderdate[] = $wkord . " -- " . $wkdat;
}

[grandpa]

<edit>
What sort of results is your query returning now?

[edited by: grandpa at 2:06 pm (utc) on Feb. 18, 2005]

[Zipper]

first of all, I'm not quite sure how u managed to use two recordsets ($rs & $rs2) using just one query. Anyway, what you should know is that each time you loop through my_fetch_array() it returns a single row. So your foreach statement serves no purpose.

However, I think you have been trying to compare eventid with wname (though the syntax actually re-assigns one value to the other) which apparently doesn't match at all according to your query (eventid = weventid)

from what I can understand by the info u've given, u have already set the condition in your query. If this is correct and ur certain that the query returns the expected resultset, you can completely forget about the two foreach statements and use case or if statements to add up the counts.

one important thing i have to mention is the use of operators when comparing two variables. It should be == (two equal signs) and NOT =, which assigns a value to another rather than comparing them.

anyway, assuming $rs2 is the recorset resulting from the query u posted above,

while ($row2 [b]==[/b] mysql_fetch_array($rs2)){
$days = $row2[1];
switch (true) {
case ($days <= 30):
$month++; 
break;
case (($days >= 31) && ($days <= 60)):
$month2++; 
break;
case (($days >= 61) && ($days <= 90)):
$month3++; 
break;
case (($days >= 91) && ($days <= 120)):
$month4++; 
break;
case (($days >= 121) && ($days <= 150)):
$month5++; 
break;
case (($days >= 151) && ($days <= 180)):
$month6++; 
break;
case ($days >= 181):
$month7++;
break;
}
}

you might have to define all the $month-X variables with zeros at the begining in case the script throws errors. also you have to address them by their own variable names, i.e. $month, $month2 etc and NOT $row1["$month"] or $row2["$month2"]. Not sure how u got that in that first place. let us know if u need more help.

[kpage]

Thanks everyone for your suggestions, I am trying them all.

Grandpa, in response to your question, my query returns the expected results, its just the grouping bit causing me grief. This is the idea:

Event1 58
Event1 60
Event1 63
Event1 67
Event1 77
Event1 81
Event1 102
Event2 106
Event3 35
Event3 71
Event4 16
Event4 17

So then group event 1 into < 30, 30-60,60-90...for each of my events

Thanks again for taking the time to help me with this