MYSQL - Query items that match date

Question

I'm making a Calendar in PHP, and I have a for loop for 31 days (or depending on how many days are in the month), each day is written out with $listDay.

In my MySQL database, I have a field called 'eventDate' with a unix timestamp, and I have a variable $event_day built from the timestamp just for the day. I can't figure out how to write a query that says "If $listDay == $event_day then show this". This code might be a little messy because i've been playing around with a bunch of options.

What this is supposed to do is if there is an event that day, replace the standard number with a link, which if moused over will show a little popup of the event (that is handled in js, and i do have the popup working correctly)

I know something is wrong with the way I have the MySQL statements, but I can't quite figure it out. Oh yeah, I created a field in the db called 'eventMonth', since I couldn't figure how to make the query 'match' a month with the timestamp.

$month = date("F");
$vmonth = date("n");
$current_month = date("n"); //value will change if the user changes the month they are viewing
$current_year = date('Y');
$theday = date(w, mktime(0, 0, 0, $current_month, 1, $current_year));
$daysinmonth = date("t", mktime(0, 0, 0, $current_month, 1, $current_year));

echo "<table bgcolor=\"#C68282\" style=\"border: #990000 1px solid; font-size:8pt; font-family: Tahoma; color: #000000\" cellpadding=\"1\" cellspacing=\"0\" width=\"129\">";
echo "<tr>
<td colspan=7 align=center style=\"font-weight: bold;\">".date('F Y', mktime(0, 0, 0, $current_month, 1, $current_year))."</td>
</tr>";
echo "<tr style=\"background-color: 990000; color: white;\">";
echo "<td align=center>S</td>";
echo "<td align=center>M</td>";
echo "<td align=center>T</td>";
echo "<td align=center>W</td>";
echo "<td align=center>T</td>";
echo "<td align=center>F</td>";
echo "<td align=center>S</td>";
echo "</tr>";
echo "<tr>";
for ($i=0;$i<$theday;$i++)
{
   echo "<td>&nbsp;</td>";
}
$query = "SELECT * FROM calendar WHERE eventMonth = '77'"; 
$result = mysql_query($query); 
$row = mysql_fetch_array($result);
for ($list_day=1;$list_day<=$daysinmonth;$list_day++)
{
    $event_day = date('j',$row['eventDate']);
  echo "<td align=\"center\">";
  if($list_day == $event_day) { 
    echo "<a href=\"#\" class=\"cal\">";
     echo $list_day . "<span class\"cal\"><div style=\"border-bottom: 1px solid #999999; padding: 2px; \">" . $month ."&nbsp;". $list_day .",&nbsp;". $current_year ." </div><div style=\"padding: 2px;\">" . $row['event'] ."</div></span></a></td>";
    } else { 
     echo $list_day;
    }
  if ($theday == 6)
  {
    echo "</tr>";
    echo "<tr>";
    $theday = -1;
  }
  $theday++;
}
echo "</tr>";
echo "</table>";

Answer 1

Use MySQL's DATE_FORMAT:

SELECT * FROM calendar WHERE DATE_FORMAT(timestamp, '%Y-%m-%d') = '2009-12-11';

But if you are querying the database on every day, then you are doing 30 unneeded queries. It would be wiser to get all the events in advance in one query and then building an array from those, like this:

// Get all events for December 2009
$q = mysql_query("SELECT
                    DATE_FORMAT(timestamp, '%d') AS day,
                    event_title
                FROM
                    calendar
                WHERE
                    DATE_FORMAT(timestamp, '%Y-%m') = '2009-12'");

$events = array();
// Loop through the results and put events in array
while($row = mysql_fetch_array($q)) {
    $events[$row['day']][] = $row['event_title'];
}

Now $events has every event for the month indexed by the day, and as you are printing the calendar, you can get the amount of events for that day using:

for($d = 1; $d <= 31; $d++) {
    if(isset($events[$d])) {
        echo count($events[$d])." events!";
    } else {
        echo "No events.";
    }
}

Answer 2

Just use a string - mysql will convert it

select * from test_time_table where the_time = '2009-12-09';

to get the days events do

select * from test_time_table where the_time between '2009-12-09 00:00:00' and '2009-12-09 23:59:59';

Seriously - don't use DATE_FORMAT. Not only will it make mysql perform an unnecessary step, it will prevent the query from entering the query cache.

Edit - you are storing the field as TIMESTAMP, right?