Looping out mysql data

Question

I have a mysql database of entries with dates. So what I want is to show all the dates in my database and then under each date, I want to show all the entries in the database entered on the specefic date. I am thinking of two loops but I don't know how to write the condition to display all the dates in my database before I loop out the entries under that date.

Edit: i used NOW() to store the date. Using the loop by longneck, how can ignore the time when checking if date is sameas previous? Should I explode? What's the best way?

Answer 1

I'm assuming that you want a list of the dates so that you can then do separate queries for each date's entries. Usually you would use a query like:

SELECT DISTINCT date_field FROM table_name ORDER BY date_field DESC

(this will do it newest-first, remove DESC to make it oldest-first)

Now, you probably don't want to do it this way, because it will require a lot of queries (one for each day's entries). It is more efficient to just order the entries by the date, and then use logic in your php to print out the headers. So you would use code like:

$result = $db->query("SELECT * FROM table_name ORDER BY date_field DESC");
$current_date = "";
while ($row = $result->fetch_array()) { 
   if ($current_date != $row["date_field"]) { 
       echo "<h3>{$row['date_field']}</h3>";
       $current_date = $row["date_field"]; 
   } 
   // Print your entry from $row here. 
}

Answer 2

you would probably want to get that in your SQL statement: (assuming it is datetime type)

SELECT * FROM table WHERE date BETWEEN '2009-9-8 00:00:00' AND '2008-9-8 23:59:59'

Then just do your normal loop through your results

Answer 3

you should use one query, sort it by date, and detect the change in the date as a signal to display the next date:

<?php

$sql = 'select start_date, name from events order by start_date';

$res = mysql_query($sql) or die(mysql_error());

$prev_date = null;

while ($row = mysql_fetch_assoc($res)) {
  if ($row['start_date'] != $prev_date) {
    echo "<h1>{$row['start_date']}</h1>"\n;
    $prev_date = $row['start_Date'];
  }

  echo "<p>{$row['name']}</p>";
}

?>

Answer 4

Also have a look at the GROUP BY clause of the SELECT statement.