event calendar

[bddotnet]

I have an event calendar setup at http://www.epsilonepsilon.org/?pg=schedule. This is a basic MySQL database of calendar events sorted by date using PHP.

Why are the events prior to today's date displayed on that page? Here's the code:


<?
include("dbinfo.inc.php");

$connect=mysql_connect('mysqlxx.powweb.com',$usern ame,$password);
$result=mysql_select_db($database) or die( "This database is not working");

echo "

<center><h1>Upcoming Events Calendar</h1></center>
<center>Click <a href=\"?pg=schedule\" class=\"link\">here</a> to view
the current month's activities.</center>
<br>
";

$current_date_month = date("Y-m");

$list_query = mysql_query("SELECT * FROM schedule where LEFT(eventstart,7) > '$current_date_month' ORDER BY eventstart ASC");

while ( $data = mysql_fetch_array($list_query) ) {

echo "

<div align=\"right\">$data[eventname]</div>
<br>
Start Date: $data[eventstart]
<br>
Ending Date: $data[eventend]
<br>
Room: $data[eventplace]
<br>
Event Time: $data[eventtime]
<hr color=\"#000000\">
";

}

?>

[mixerson]

I'd use the SQL function TO_DAYS() to convert the first of the month and 'eventstart' to integers, then do the comparison.

[bddotnet]

Thanks! I figured out what my problem was:


$current_date_month = date("Y-m");

$list_query = mysql_query("SELECT * FROM schedule where LEFT(eventstart,7) = '$current_date_month' ORDER BY eventstart ASC");


should have been:


$current_date_month = date("Y-m-d");

$list_query = mysql_query("SELECT * FROM schedule where LEFT(eventstart,10) > '$current_date_month' ORDER BY eventstart ASC");


That made it work correctly. Thanks mixerson!

[bddotnet]

Now, here's another scenario...

I have this:


$current_date_month = date("Y-m-d");
$current_month = date("Y-m");

$list_query = mysql_query("SELECT * FROM schedule WHERE eventstart >= '$current_date_month' ORDER BY eventstart ASC");


How would I modify this query and variables if I want it to display only the events of the current month that are left in the month?

NOTE: The variable $eventstart is in the format: yyyy-mm-dd

[mixerson]

Check out the "Date and Time Functions" section of the MySQL documentation. There's some great stuff in there for date calculations and formatting.

Here's a sample query that will get you the dates you need for your comparison:SELECT
@today:=CURDATE() as todays_date,
@daynum := DAYOFMONTH(@t) as day_of_month,
@first := @t - INTERVAL (@d - 1) DAY as first_day_of_month,
@last := (@f + INTERVAL 1 MONTH) - INTERVAL 1 DAY as last_day_of_month
The query above produces the following (after a little formatting):todays_date = 2006-02-26
day_of_month = 26
first_day_of_month = 2006-02-01
last_day_of_month = 2006-02-28Since SQL variables (@today, etc) persist between queries, you can get the remaining events in the current month with:SELECT * FROM schedule WHERE eventstart BETWEEN @today AND @last ORDER BY eventstart ASCor all the events in the current month with SELECT * FROM schedule WHERE eventstart BETWEEN @first AND @last ORDER BY eventstart ASCThe BETWEEN test will succeed for all dates matching both end dates and those in between.

[bddotnet]

That's cool! Thanks mixerson!

[mixerson]

Oops! I tested my first SELECT example with short variable names (@t, @d, etc.), but changed them for readibility (@today, @daynum, etc.) when I posted. Unfortunately, I forgot to change them all. Here's the correct version: SELECT
@today:=CURDATE() as todays_date,
@daynum := DAYOFMONTH(@today) as day_of_month,
@first := @today - INTERVAL (@daynum - 1) DAY as first_day_of_month,
@last := (@first + INTERVAL 1 MONTH) - INTERVAL 1 DAY as last_day_of_month

[bddotnet]

Thanks mixerson!