How can I ignore time for NOW()?

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.

<?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>"; }

?>

In a previous question (http://stackoverflow.com/questions/1395657/looping-out-mysql-data), I resulted in using this code. It pulls the date and time from MYSQL, and I used NOW() to store both date and time. How can I make it ignore the time so I can achieve what I want?

Answer 1

Use CURDATE() instead of NOW().

Answer 2

Try it with a condition like this:

SELECT * FROM `events` WHERE DATE(`start_date`) = '2009-09-09';

This'll get you all events from the database for Sep 9th 2009. I think that's what you're asking for, is it?

Answer 3

as David Andres mentions in the comment, DATE() extracts the date part of a date or datetime expression. so you can do the following:

<?php

$sql = 'SELECT DATE(start_date) AS start_date_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_date'] != $prev_date) {
    echo "<h1>$row[start_date_date]</h1>\n";
    $prev_date = $row['start_date_date'];
  }

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

Answer 4

Untested code that I will probably need someone to correct, but here goes:

SQL to retrieve all the dates that exist in the table:

$sql_get_dates = 'select start_date from events order by start_date distinct';

And, assuming *start_date* is a DATETIME type, the SQL to get all events on a given date:

$sql_get_events = 'select * from events where date(start_date) = "2009-08-09"';

Answer 5

Instead of just selecting the date, you could use some of the MySQL time functions to truncate the date.

$sql = "select date_format(start_date, '%Y-%m-%d') as fstart_date, name from events order by start_date";

Of course, you'll have to change start_date to fstart_date within the PHP code.

Check out the Mysql reference page for DATE_FORMAT().