Using a PHP Calendar to display events from a database

oh_maestro

I have been able to produce the calendar (month at a glance), but I cannot query a date from the calendar from its corresponding date in the SQL database to display events from that day. If it works properly, it should show all events for the month on the appropriate day.

Variables used:
$year // example: 2005
$month // example: 8
$day_counter // example: 11 - this is looped to increment until month is done

$thisdate = mkdate(0, 0, 0, $month, $day_counter, $year);

$sql = "SELECT * FROM `school_year0506` WHERE Date LIKE '$thisdate'";

I realize that I am probably way off, so if I need to alter things, I am very willing.

Roger_Ramjet

This really all depends on what format you have chosen for your date column in the table.
But I can tell at a glance that you are using the wrong format anyway ie that stupid yankie m,d,y form. MySQL use the international standard YYYY-MM-DD format for it's native date storage, but will also accept the UNIX timestamp form.

http://dev.mysql.com/doc/mysql/en/datetime.html

oh_maestro

The data type in SQL is DATE.

I've tried to concatenate the date so that it would match YYYY-MM-DD format, but haven't gotten that to work either.

$thisdate = $year."-".$month."-".$day_counter;
// trying to store 2005-08-02 (as an example)
// the day should be able to change to reflect each day of the month that is being viewed on the calendar.

I wasn't sure if it looks at $thisdate as text instead of a date.

Roger_Ramjet

I wasn't sure if it looks at $thisdate as text instead of a date.

'It all depends' is the only answer, you'll have to read the manual to get your head round it.

Now, don't see what you tried to use LIKE for??

If you want all events for a specific Month then use the Year and Month functions


$sql = "SELECT * FROM school_year0506 WHERE Year(Date) = " . $year . " AND MONTH(Date) = " . $month

This will return all rows for the given year and month in one go. Add whatever Order By applies.

to select for just 1 specific day use =

AND DON'T USE DATE FOR A COLUMN NAME, EVER.
MySQL may be able to handle it but most other db engines will spit-the-dummy over it. Besides, it is actually meaningless in the real world as well: what date? event date, birthday, date entered ??
Use more meaningfull names or you'll end up with untold columns called the same thing and no obvious way to identify the contents. Try DateIn, UpDate, StartDate, EventDate, DueDate, InvDate, etc.