I'm constructing a very simple query, and want to display the results in a list, in date order, i.e.

2009-12-25
have lunch
drink beer
fall asleep

2009-12-26.
have breakfast
drink beer
have lunch
etc

My table is also very simple, 1 column for date and 1 column for event.

my query is

<?php
$query  = "SELECT event_date, event_name FROM events WHERE event_date='$todaysdate'"; 
$result = mysql_query($query);

while($row = mysql_fetch_row($result))
{
    $date    = $row[0];
    $event  = $row[1];


    echo "$date <br>" .
         "$event <br>"; 
      
}
?>

As expected, the query returns the both the date and event in the list, i.e.
2009-12-25 have lunch
2009-12-25 drink beer
2009-12-25 fall asleep

2009-12-26 have breakfast
2009-12-25 drink beer
2009-12-25 have lunch
2009-12-25 etc

Is the anyway I can create a result where each data is not repeated unless it is unique?

I'm hoping the answer is so simple, that this is the reason I've not found anyone else reporting a similar frustration on the web!

Many thanks

Recommended Answers

All 6 Replies

Is the anyway I can create a result where each data is not repeated unless it is unique?

You want 'group by'
If you meant each date, you would use group by event_date...

$query  = "SELECT event_date, event_name FROM events group by event_date";

Many thanks for that. Unfortunately this only shows one event per date, i.e.

2009-12-25
have lunch
2009-12-26
have breakfast

when query is

<?php
$query  = "SELECT event_date, event_name FROM events WHERE event_date >'$startdate' AND event_date <'$enddate' group by event_date"; 

$result = mysql_query($query);

while($row = mysql_fetch_row($result))
{
    $date    = $row[0];
    $event = $row[1];


    echo "$date <br>" .
         "$event <br>";
		
      
} 
?>

for data base rows that where there are 4 rows of data for each date.


I also did some extra reading on the group by function and tried to group by event_name, however that seems to give me the same result as the query without the group function.

Oh ok I misunderstood what you were looking for...what about this:

<?php
$query  = "SELECT event_date, event_name FROM events WHERE event_date >'$startdate' AND event_date <'$enddate' order by event_date"; 

$result = mysql_query($query);

while($row = mysql_fetch_row($result))
{
    $date    = $row[0];
    $event = $row[1];

    if ($prevdate != $date){
        echo $date . "<br>";
        $prevdate = $date;
    }

    echo $event . "<br>";
} 
?>

Have you tried the «SELECT DISTINCT» Statement ?

$query  = "SELECT DISTINCT event_date, event_name FROM events WHERE event_date >'$startdate' AND event_date <'$enddate' order by event_date";

http://www.w3schools.com/SQl/sql_distinct.asp

Maybe something like this ?

<?php
$query = "SELECT DISTINCT event_date FROM events WHERE event_date='$todaysdate'";
$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='$todaysdate'";
		$result2 = mysql_query($query2);
		while($row2 = mysql_fetch_assoc($result2))
			{
				echo $row2['event_name'] . "<br />";
			}
echo "<br />";
}
?>

Thanks a lot me655321 for this!!!
Adriana

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.