Sorting a calendar by date?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Matt Phelps
Forum Commoner
Posts: 82
Joined: Fri Jun 14, 2002 2:05 pm

Sorting a calendar by date?

Post by Matt Phelps »

I have a database containing a list of events and dates, making up a calendar. What I want to do is only display events that are current - ie; not in the past.

For example, here is the complete list of events:

Code: Select all

1 , South African Grand Prix , 21 , 09 , 02 
2 , Monaco Grand Prix , 12 , 10 , 02 
3 , Indianapolis 500 , 02 , 11 , 02 
4 , Dutch Grand Prix , 23 , 11 , 02 
5 , Belgian Grand Prix , 14 , 12 , 02 
6 , French Grand Prix , 04 , 01 , 03 
7 , British Grand Prix , 25 , 01 , 03 
8 , German Grand Prix , 15 , 02 , 03 
9 , Italian Grand Prix , 08 , 03 , 03 
10 , Canadian Grand Prix , 29 , 03 , 03 
11 , United States Grand Prix , 19 , 04 , 03 
12 , Mexican Grand Prix , 10 , 05 , 03
Today is the 24th of September so I don't want to display the first event (South African GP) because it is in the past.

Here is the mySQL code I have used to pull this data out of the database:

Code: Select all

<?php

$thisday = gmdate('d');
$thismonth = gmdate('m');

$series_title = "Grand Prix Season 5 Classic";
$series_image = "gpclassic_strip.gif";
$series_results = "gps5cresults";
$series_stats = "gps5cstats";

// generate and execute query for race schedule
$query = "SELECT id, event, track, round, race_day, race_month, race_year, starthour, startmin, distance FROM schedule WHERE race_year >= $thisyear AND series = '$series_title' ORDER BY race_year,race_month,race_day ";
$schedule_result = mysql_query($query);

		while ($row = mysql_fetch_array($schedule_result)) 
		{
		echo (" " . $rowїround] . " , " . $rowїevent] . " , " . $rowїrace_day] . " , " . $rowїrace_month] . " , " . $rowїrace_year] . " <br>");
		}


?>
How can I sort the events so that I don't see the historic ones? I tried only pulling out results where raceday was >= $thisday but that won't work because the next race is on the 12th October and 24 is greater than 12! Are you still with me?!

Can anyone suggest a solution for sorting a database by dates? I supposed I could implode the race_day/race_month/race_year numbers and convert them into something mySQL understands like a datetime variable? Would that work?
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post by Coco »

this is one of those *kicks self* solutions


Code: Select all

<?php
if($raceyear<$nowyear)return($past);
if($raceyear>$nowyear)return($future);
else{
if($racemonth<$nowmonth)return($past);
if($racemonth>$nowmonth)return($future);
else{
if($raceday<$nowday)return($past);
if($raceday>$nowday)return($future);
else return($today);
}}
?>
Post Reply