PHP / MySQL Grouping Events by Date

[xcrookshanksx]

I am using MySQL with a flat table exported from another dB to create a simple event calendar in Dreamweaver MX 2004. I would like to have the events grouped by date and ordered by date and time. I have been unable to figure out how to group by date while preserving all the records. I have tried using GROUP BY in my SQL statement, but this reduces the number of records returned. I have also been trying, without success, to use loops and if statements to suppress the date from repeating after the first record. A sample of my SQL statement and code, along with what my current return looks like and what I would like it to look like are provided. Any assistance or reference to the correct procedure for accomplishing this would be appreciatted.

/***** SQL and variables *****/

mysql_select_db($database_conn_testing, $conn_testing);
$query_rs_argCalendar = "SELECT *, UNIX_TIMESTAMP(argumentDate) AS argDateTimestamp FROM argumentcalendar ORDER BY argumentDate, argumentTime";

$rs_argCalendar = mysql_query($query_rs_argCalendar, $conn_testing) or die(mysql_error());

$row_rs_argCalendar = mysql_fetch_assoc($rs_argCalendar);

$totalRows_rs_argCalendar = mysql_num_rows($rs_argCalendar);

/***** Sample return code *****/

<?php do {?>
<div class="argCalendarDetail">
<ul>
<li class="argDate"><?php echo date('l, F j, Y',$row_rs_argCalendar['argDateTimestamp']);?></li>
<li class="docket"><?php echo $row_rs_argCalendar['docketNumber'];?>&nbsp;<span class="title"><?php echo $row_rs_argCalendar['caseTitle'];?></span></li>
<li class="argTime"><?php echo $row_rs_argCalendar['argumentTime'];?>&nbsp;<?php echo $row_rs_argCalendar['argumentLength'];?></li>
<li class="argLocation"><?php echo $row_rs_argCalendar['argumentLocation'];?>:&nbsp;<?php echo $row_rs_argCalendar['roomNumber'];?></li>
<li class="panelName">Panel Name:&nbsp;<?php echo $row_rs_argCalendar['panelName'];?></li>
<li class="panelMembers">Panel Members:&nbsp;<?php echo $row_rs_argCalendar['panelMembers'];?></li>
</ul>
</div>
<?php } while ($row_rs_argCalendar = mysql_fetch_assoc($rs_argCalendar));?>

/***** What it currently looks like *****/

* Tuesday, August 31, 2004
* 42345 X VS. X
* 13:00 60 min

* Monday, September 13, 2004
* 41234 X vs. X
* 10:00 30 min

* Monday, September 13, 2004
* 43456 X VS. X
* 10:30 30 min

/***** What I want it to look like *****/

* Tuesday, August 31, 2004

* 42345 X VS. X
* 13:00 60 min

* Monday, September 13, 2004

* 41234 X VS. X
* 10:00 30 min

* 43456 X VS. X
* 10:30 30 min

Thanks again.

[StupidScript]

I'm thinking: dump the argDateTimestamp into a temp variable to see if it has already been output, check the value of the temp variable against the current value, and suppress that one line if it matches.

<?php do {?>
<div class="argCalendarDetail">
<ul>
<?php
if ($tmpdate!=$row_rs_argCalendar['argDateTimestamp']) {
?>
<li class="argDate"><?php echo date('l, F j, Y',$row_rs_argCalendar['argDateTimestamp']);?>
<br /><br /></li>
<?php
}
$tmpdate=$row_rs_argCalendar['argDateTimestamp'];
?>
<li class="docket"><?php echo $row_rs_argCalendar['docketNumber'];?>&nbsp;<span class="title"><?php echo $row_rs_argCalendar['caseTitle'];?></span></li>
<li class="argTime"><?php echo $row_rs_argCalendar['argumentTime'];?>&nbsp;<?php echo $row_rs_argCalendar['argumentLength'];?></li>
<li class="argLocation"><?php echo $row_rs_argCalendar['argumentLocation'];?>:&nbsp;<?php echo $row_rs_argCalendar['roomNumber'];?></li>
<li class="panelName">Panel Name:&nbsp;<?php echo $row_rs_argCalendar['panelName'];?></li>
<li class="panelMembers">Panel Members:&nbsp;<?php echo $row_rs_argCalendar['panelMembers'];?></li>
</ul>
</div>
<?php } while ($row_rs_argCalendar = mysql_fetch_assoc($rs_argCalendar));?>

Note that I also added two line breaks when the $row_rs_argCalendar['argDateTimestamp'] is printed, to give that separation in the "what I want" example.

[xcrookshanksx]

Worked perfectly. Thanks for the help.

[coopster]

Welcome to WebmasterWorld, xcrookshanksx!

StupidScript nailed it. And it looks like you understand why you can't use a

GROUP BY

clause here now.

[Hyper Hydrogen]

Hi guys, I tried doing the suggested answer but I got an error on this -> Undefined variable: tmpdate...any suggestion? thanks!

[jatar_k]

Welcome to WebmasterWorld Hyper Hydrogen,

Have you looked to see what is contained in that var or where it is being defined.