Search with PHP for calendar dates within a MySQL array?

Question

I'm building a calendar and I'm really dumb when it comes to arrays. First of all, I need to query my database, so:

$events = mysql_query ("SELECT id,title,date WHERE date BETWEEN 2009-01-01 AND 2009-01-31")
or die(mysql_error());

So, now I need to order those events so, when I'm echoing my calendar table, I can check this array for events. In this great Calendar written by David Walsh, he does a query for every day, but I suppose it would be a performance nightmare. So...any ideas how can I do this?

Answer 1

First up, make sure you're comparing dates with dates. I think your code should be (i.e. with single quotes around date):

$events = mysql_query ("SELECT id,title,date WHERE date 
BETWEEN '2009-01-01' AND '2009-01-31' order by date asc")or die(mysql_error());

assuming date is a date column.

Answer 2

i would do that in a elegant for loop

for($i=0;$i<30;$i++)
{
$events[] =  mysql_fetch_array(mysql_query ("SELECT id,title,date WHERE date BETWEEN 2009-01-01 AND 2009-01-{$i}"));
}

now you have an array do somethingg with it like this

for($i=0;$i<count($events);$i++)
{ 
  echo $events["id"];
}

will return each id for events in that array

Answer 3

Ok regarding your comments you can do something like this: Get the events like Davek proposed (note the ORDER BY!)

$events = mysql_fetch_assoc(mysql_query ("SELECT id,title,date WHERE date BETWEEN '2009-01-01' AND '2009-01-31' ORDER BY date asc"));

Then you got the events ordered by the date. To output it you can do this:

$last_date = null;
foreach($event in $events) {
    if($last_date !== $event['date']) {
        echo 'Events for day ' . $event['date'] . ": \n";
        $last_date = $event['date'];
    }

    echo $event['title'] . "\n"

}

Note: This is just a rough sketch, you have to adjust the output of course, but it should give you the right idea.

Output would look like this (in my example):

Events for 2009-01-01: 
Event 1
Event 2
Events for 2009-01-02: 
Event 1
.
.
.

---

Edit after comment:

You can write your own function:

function get_events($date, $events) {
    $result = array();
    foreach($event in $events) {
        if($event['date'] == $date) {
            $result[] = $event;
        }
    }
    return $result;
}

But this way you search the the complete array over and over again for each day. You can improve it, if you remove the events you already searched for from the $events array. So every time you search in a smaller array. But I would only do this if there is a performance issue.