[RESOLVED] MySQL Query Trouble - 1 Date, Multiple Values

rdj

I'm having trouble figuring out how to properly code a query. I have an Updates tables with the following fields:

UpdateID - auto increment
dd - int
mm - int
yyyy - int
Added - datetime
Updates - text
Category - varchar(50)

UpdateID            Added                Updates
1                   2009-01-01           update 1
2                   2009-01-01           update 2
3                   2009-01-02           update 1
4                   2009-01-03           update 1
5                   2009-01-03           update 2

I'm not very concerned with the mm, dd, yyyy fields at the moments. I currently have the table populated with updates to a site i run. I have previously programmed this in ColdFusion and am migrating it to php, however, my troubles is this.

I want to query the table to find the last 3 dates where an update has been added. From there, I want to find each update for the corresponding date and display in the format below:

January 1, 2009
-update 1
-update 2

January 2, 2009
-update 1

January 3, 2009
-update 1
-update 2

I am able to query the last 3 updates just fine, however I can't seem to be able to get the update for each date working correctly. Below is the code to display the updates how I want, however it only configured to display 1 date and 1 update per date at a time. I'm thinking I may need another table to function as an index in order for this work properly. Any ideas are appreciated!

// Find the last 3 update dates for default display
	$query = "SELECT Added, Updates FROM Updates ORDER BY Added DESC LIMIT 3";
	$result = mysql_query($query) or die ('Error executing query: <br>'. mysql_error());

// Display results
while($row = mysql_fetch_array($result)) {
	$added = $row['Added'];
	$updates = $row['Updates'];

	echo "<span class=\"redbgtext\"><strong>$added</strong><blockquote><p>$updates</p></blockquote></span>
	<hr width=75% color=\"black\" align=\"center\">";
}

tomhath

This is really a php question. The rows are being returned in the order you want, all you need to do is keep track of the previous $added inside the while loop, whenever it changes echo the date, save the current date, then echo the $updates. Next time through the loop if $added is the same just echo $updates without the date line.

I didn't test this:

    $prev_added = ''
    while($row = mysql_fetch_array($result)) {
        $added = $row['Added'];
        $updates = $row['Updates'];

    if ($prev_added != $added) {
       echo $added;
       $prev_added = $added;
    }

    echo "<span class=\"redbgtext\"><blockquote><p>$updates</p></blockquote></span>
    <hr width=75% color=\"black\" align=\"center\">";
}

rdj

Brilliant! It works exactly how I had hoped! Only problem I've come across is that I don't want the <hr> to display if there are more than one updates listed for a date, but I should be able to work that out. Thanks for the help!