Group output by date

[68thorby68]

I have a simple database with 3 columns: id, event_date, event_name
I have 9 rows in the database

id event_date event_name
1 2010-01-17 Food 17th
2 2010-01-17 Bar 17th
3 2010-01-17 Football 17th
4 2010-01-18 Food 18th
5 2010-01-18 Bar 18th
6 2010-01-18 Football 18th
7 2010-01-19 Food 19th
8 2010-01-19 Bar 19th
9 2010-01-19 Football 19th

I would like an output that looks like this
2010-01-17
Bar 17th
Food 17th
Football 17th

2010-01-18
Bar 18th
Food 18th
Football 18th

2010-01-17
Bar 19th
Food 19th
Football 19th

my code

<?php
$query = "SELECT DISTINCT event_date FROM events WHERE event_date >'$startdate' AND event_date <'$enddate'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
	echo "<b>". $row['event_date'] . "</b><br />";
	 
$query2 = "SELECT event_name FROM events WHERE event_date >'$startdate' AND event_date <'$enddate' GROUP by event_name";
		$result2 = mysql_query($query2);
		while($row = mysql_fetch_assoc($result2))
			{
				echo $row['event_name'] . "<br />";
			}
echo "<br />";
}
?>

unfortunately this gives me an output of
2010-01-17
bar open 17th
bar open 18th
bar open 19th
food 17th
food 18th
food 19th
football 17th
football 18th
football 19th

2010-01-18
bar open 17th
bar open 18th
bar open 19th
food 17th
food 18th
food 19th
football 17th
football 18th
football 19th

2010-01-19
bar open 17th
bar open 18th
bar open 19th
food 17th
food 18th
food 19th
football 17th
football 18th
football 19th

How can I group all by date, with only one date entry? Is this possible at all?

Many thanks

[amd_k8]

<?php
$query = "SELECT DISTINCT event_date FROM events WHERE event_date >'$startdate' AND event_date <'$enddate'";
$result = mysql_query($query);
$date = '';
while($row = mysql_fetch_assoc($result))
{
            $date = $row['event_date'];
	echo "<b>". $date . "</b><br />";
 
$query2 = "SELECT event_name FROM events WHERE event_date='$date'";
		$result2 = mysql_query($query2);
		while($row = mysql_fetch_assoc($result2))
			{
				$row['event_name'] . "<br />";
			}
echo "<br />";
}
?>

Since in your first query you selected the needed dates, there is no need to filter them again in the second query.

[68thorby68]

Many thanks,
this makes sense, however I have just one question. on line 4 the variable date is just defined as blank, how does the 2nd query know which date is to be used.

I have just copied and paste the code you kindly sent me, and my output is

2010-01-17

2010-01-18

2010-01-19

[julzk]

This is what you want.. I have tested this and works.. The results display as:

2010-01-17
Bar 17th
Food 17th
Football 17th

2010-01-18
Bar 18th
Food 18th
Football 18th

2010-01-19
Bar 19th
Food 19th
Football 19th

// ##### DB data ##### Start ->
$str = str_replace("\n", "<BR />", $str);
if(!isset($cmd))
{
    $result = mysql_query("SELECT DISTINCT event_date FROM test WHERE event_date >'2010-01-01' AND event_date <'2010-01-25'");
	
	while($r = mysql_fetch_assoc($result))
	{
	$event_date=$r["event_date"]; 

	echo "$event_date<br>";

			$result2 = mysql_query("SELECT event_name FROM test WHERE event_date = '$event_date' ORDER BY event_name ASC");
				while($r = mysql_fetch_assoc($result2))
					{
					$event_name=$r["event_name"]; 
					
					echo "$event_name<br>";
					
					}
			echo "<br>";
	}
}
// ##### DB data ##### End <-

[julzk]

Oh, I forgot.. you'll have to change the 2010-01-01 and 2010-01-25 back to your variable $startdate and $enddate

[68thorby68]

You are a star,

I made a couple of alterations (see below) and all working as required.

Thank you very much...5 hours of pain have now ended.

<?php
$query = "SELECT DISTINCT event_date FROM events WHERE event_date >'$startdate' AND event_date <'$enddate'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
            $date = $row['event_date'];
	echo "<b>". $date . "</b><br />";
 
$query2 = "SELECT event_name FROM events WHERE event_date='$date'";
		$result2 = mysql_query($query2);
		while($row = mysql_fetch_assoc($result2))
			{
			$vent = $row['event_name'];
			echo "<b>". $event . "</b><br />";
			}

}
?>

[james_horn165]

<?php

	
 $sql = "select date(p.post_modified) as post_modified from wp_posts p inner join wp_term_relationships tr on tr.object_id = p.ID inner join wp_terms tt on tt.term_id = tr.term_taxonomy_id where p.post_type='post' and tt.slug='your category slug name' group by date(p.post_modified)" ;
		  
		 
$rows = $wpdb->get_results($sql);
if($rows){
foreach($rows as $row){
	
	
	$date = $row->post_modified;
	$date = strtotime($date);
	$date = date('jS F Y', $date);		
	
	$newdate = $row->post_modified;
	
?>

<div id="dailyquestions">
<div id="datebar"><strong>

<?php echo $date;  ?>

</strong></div>
<div class="row1">
<div class="col2">

<?php
			$sql1 = "select p.post_title, p.guid from wp_posts p inner join wp_term_relationships tr on tr.object_id = p.ID inner join wp_terms tt on tt.term_id = tr.term_taxonomy_id where p.post_type='post' and tt.slug='your category slug name' and date(p.post_modified) = '$newdate' order by date(p.post_title) limit 0,20";

			$pst = $wpdb->get_results($sql1);
				foreach($pst as $p){
			?>

<li class="orange"><strong><a href="<?php echo $p->guid;?>"><?php echo $p->post_title; ?></a></strong></li>

<?php
		}
	?>

</div>
</div>
</div>

<?php
}
}
}
?>

Note: You can replace wordpress syntax to php mysql and have enjoy.