Grouping

[ec]

Is there anyway of grouping the results by detention.detentiondate so that each date is only shown once?

 

 

 

$query = "SELECT detention.detentiondate, detention.pupilno, detention.reason, detention.teacherid, 
pupil.firstname, pupil.lastname FROM detention, pupil 
WHERE detention.detentiondate >= '$_SESSION[startdate]' AND detention.detentiondate <= '$_SESSION[enddate]' 
AND detention.pupilno = pupil.pupilno ORDER BY detention.detentiondate, pupil.lastname, pupil.firstname, pupil.pupilno  ";
$result = mysql_query($query);
if ($result) {
    while ($array= mysql_fetch_assoc($result)) {
       echo "<br />";
       print "$array[detentiondate]<br />";
       print "$array[pupilno]: <i>$array[firstname] $array[lastname]</i><br />";
       print "<b>Reason:</b> $array[reason]<br />";
       print "<b>Allocated By:</b> $array[teacherid]<br />"; }
       }
?>

[phpknight]

You might want to post this in the mySQL forum.

[GingerRobot]

Give this a whirl:

 

<?php
$query = "SELECT detention.detentiondate, detention.pupilno, detention.reason, detention.teacherid, 
pupil.firstname, pupil.lastname FROM detention, pupil 
WHERE detention.detentiondate >= '$_SESSION[startdate]' AND detention.detentiondate <= '$_SESSION[enddate]' 
AND detention.pupilno = pupil.pupilno ORDER BY detention.detentiondate, pupil.lastname, pupil.firstname, pupil.pupilno  ";
$result = mysql_query($query) or die(mysql_error());
if ($result){
    $last_date = '';
    while ($array= mysql_fetch_assoc($result)) {
        echo "<br />";
        if($array[detentiondate] != $last_date){//the current date is different from the last one
            print "$array[detentiondate]<br />";
            $last_date = $array[detentiondate];
        }
        print "$array[detentiondate]<br />";
        print "$array[pupilno]: <i>$array[firstname] $array[lastname]</i><br />";
        print "<b>Reason:</b> $array[reason]<br />";
        print "<b>Allocated By:</b> $array[teacherid]<br />"; }
    }
?>

 

I think that's what you wanted.

[ec]

thanks that was the type of structure i was looking for...got it looking like what i want now

 

THANKS!