[Resolved] Group related records into separate tables on same page

Joseph_Sliker

Hi again;

I have an online work schedule (stored in a MySQL database) for multiple work group members. I've got all the input, edit, delete pages (php) working nicely. Everyone can get to and edit their own calendar, everyone can select other folks to get a page showing where they are on a specific day. It all works real nice.

I'm trying to do the following:

Create a page that gets EVERYONE's records for a specific day (this part is easy... now for the hard part)

...and displays each person's schedule in separate tables, one after another: The person's name (and maybe the date) will be the label in the topmost table row, and the variable number of schedule items will appear below the label:

Example of desired output:

Joe Slick 11/17/2003
ARRIVE LEAVE LOCATION COMMENT
8:00AM 11:00AM HERE
11:30AM 12:30PM THERE LUNCH
1:00PM 4:30PM HERE

Tom Slick 11/17/2003
ARRIVE LEAVE LOCATION COMMENT
9:00AM 11:00AM HERE
11:45AM 4:30PM THERE LUNCH/NAP

etc.

Here is a simplified version of my MySQL query:

$Query = "SELECT WHEREAMI.*, DATE_FORMAT(DATE, '%a, %b %d') as ddate, TIME_FORMAT(ARRIVE, '%l:%i %p') as fARRIVE, TIME_FORMAT(LEAVE, '%l:%i %p') as fLEAVE, CONCAT(TEAM.FIRST,\" \",TEAM.LAST) AS NAME FROM WHEREAMI LEFT JOIN TEAM on (TEAM.TEAMID=WHEREAMI.TEAMID) WHERE WHEREAMI.DATE = $DATEFILTER ORDER BY NAME, DATE, ARRIVE";

The $Result of this contains all the necessary data/fields, but I believe I need some kind of nested Foreach/While loop structure...and this just confuses the heck out of me.

I believe I need something like:

//For each person:
<TABLE>
<TR><TH>$NAME</TH><TH>$ddate</TH></TR>
<TR><TH>ARRIVE</TH><TH>LEAVE</TH>TH>LOCATION</TH><TH>COMMENT</TH></TR>
//and then a while loop that repeats this row as often as needed for this specific person?
<TR><TD>$farrive</TD><TD>$fleave</TD>TD>$location</TD><TD>$comment</TD></TR><TR><>ARRIVE</TH><TH>LEAVE</TH>TH>LOCATION</TH><TH>COMMENT</TH></TR>
//end the while loop
</TABLE>
//close the table and then go to the next table for the next person until all are done.

I get the overall process, just not the specifics of the foreach and while loop codes in a situation like this.

Any help would be appreciated.

Thanks again!

LordShryku

Just set the name as a constant in the loop. When the name doesn't match, end the table, start a new one. Something like this should get you started

$sql = "Your SQL here";
$res = mysql_query($sql);
if(mysql_num_rows($res) > 0) {
   $name = ""; // var for name
   $i = 0; // just a counter
   while($row=mysql_fetch_array($res)) {
      if($name != $row['Name']) {
         if($i > 0) {
            // close previous table
            echo "</table><br />";
         }
         echo "<table border=\"1\">
                 <tr>
                   <th align=\"left\">".$row['Name']." ".$row['Date']."</th>
                 </tr>";
         $i++; // increment counter
         $name = $row['Name']; // set new constant
      }
      echo "<tr>
              <td>"; // All of your data here
   }
}
else {
   echo "No rows found";
}

Joseph_Sliker

Thank you very much for your reply.

I have not tried it (yet). Before your reply was posted, I hit on the following method that seems to work!

It involves two FOREACH loops, one nested within another. I don't know if this is strictly kosher, but it does seem to work:

//DISPLAY EACH PERSON'S RESULTS IN INDIVIDUAL TABLES
while ($Row=mysql_fetch_array($Result)) { 
	  $ItemArray[$Row[NAME]][] = "<tr><td>$Row[ddate]</td><td>$Row[fARRIVE]</td><td>$Row[fLEAVE]</td><td>$Row[LOCATION]</td><td>$Row[COMMENT]</td></tr>\n";
	}

foreach ($ItemArray as $Key => $Line) {
	print "<table><tr><th colspan= \"5\">$Key</th></tr>";
	print "<TR><TH>DATE</TH><TH>ARRIVE</TH><TH>LEAVE</TH><TH>LOCATION</TH><TH>COMMENT</TH></TR>";
	 foreach ($Line as $LineKey => $schedrow) {
		print "$schedrow";
	}
	//End of this person's items/rows
	print "</table><br>"; //close this person's table and place a line break between it and the next one
}	
//End of initial foreach loop (Team member)