Group records by month with single header

[Darylt]

I am trying to achieve the followind

Month 1
Date 1 Item 1 Location 1
Date 2 Item 2 Location 2
Date 3 Item 3 Location 3

Month 2
Date 4 Item 4 Location 4
Date 5 Item 5 Location 5
Date 6 Item 6 Location 6

I am using php and am collecting my data from a mysql database

I have been using Dreamweaver to build my website, however using the repeat region tool, the nearest that I can get is to have the month above each record.

Thanks in advance for any help that you might be able to give!

[venelin13]

I have no idea of your database table structure and thus how the data is organized, but probably you need to run two queries:

Query #1 to retrieve a list of all the months (e.g. select distinct month from table_name order by month)....

Than, looping through the Query #1 result, execute a second query to retrieve the information for each month. (e.g. select * from table_name where month = '$month')

If you post here your database table structure, we can help better.

[mooger35]

You can do that with one query

//put your query here...
$query = mysql_query("SELECT * FROM table ORDER BY date DESC");

//initiate month variable
$month = "";

while($row = mysql_fetch_assoc($query)){

//check to see if month from db = $month
if($month != $row['month']) echo "<tr><td colspan=\"3\"><b>".$row['month']."</b></td></tr>\r\n";

//echo data
echo "<tr>
<td>Date</td>
<td>Item</td>
<td>Location</td>
</tr>";

//set $month to last month from db
$month = $row['month'];
}

/untested, but if it doesn't work it should get you on the right track.

[Darylt]

My database consists of the following fields

id
start_date
finish_date
course_type
location_address
location_map
price

I have the page setup so that I can see the data, I have also managed to extract the month from the sql date from the column start_date

I have managed to get the month to show as a header above the course information for that month and subsequant months, however if I have more than one course in a month it shows the header above each entry.

The query looks something like this

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

mysql_select_db($database_Strata, $Strata);
$query_Courses = "SELECT * FROM courses WHERE courses.start_date >= CURRENT_DATE() ORDER BY start_date DESC";
$Courses = mysql_query($query_Courses, $Strata) or die(mysql_error());
$row_Courses = mysql_fetch_assoc($Courses);
$totalRows_Courses = mysql_num_rows($Courses);
?>

And the code looks something like this

<?php do { ?>
<table width="100%" border="1">

<tr>
<td colspan="6"><?php echo date('F',strtotime($row_Courses['start_date'])); ?></td>
</tr>
<tr>
<td><?php echo $row_Courses['start_date']; ?></td>
<td><?php echo $row_Courses['finish_date']; ?></td>
<td><?php echo $row_Courses['course_type']; ?></td>
<td><?php echo $row_Courses['location_address']; ?></td>
<td><?php echo $row_Courses['location_map']; ?></td>
<td><?php echo $row_Courses['price']; ?></td>
</tr>
</table>
<?php } while ($row_Courses = mysql_fetch_assoc($Courses)); ?>