Spark_Plug Posted June 10, 2009 Share Posted June 10, 2009 I have a calendar table which holds all events found on the calendar. The fields are as follows: eventID, day, month, year, time, description, location I am trying to get the next three upcoming events, however the events are not in any particular order in the database. So, currently I am pulling everything from the database and comparing making dates, then comparing them and if they are after the specific date, then add it to an array of objects. Here is the code I am using: <?php function nextThreeEvents() { $year = date('Y'); $month = date('m'); $day = date('j'); $nextEvents = "SELECT * FROM calendar"; $nextEventResult = mysql_query($nextEvents); echo '<h2>Upcoming Events</h2>'; $counter = 0; for ($i = 0; $i < mysql_numrows($nextEventResult); $i++) { $name = mysql_result($nextEventResult, $i, "name"); $location = mysql_result($nextEventResult, $i, "location"); $description = mysql_result($nextEventResult, $i, "description"); $time = mysql_result($nextEventResult, $i, "time"); $eventDay = mysql_result($nextEventResult, $i, "day"); $eventMonth = mysql_result($nextEventResult, $i, "month"); $eventYear = mysql_result($nextEventResult, $i, "year"); $arrayTime = explode(':', $time); $time = date('g:i A', mktime($arrayTime[0], $arrayTime[1], $arrayTime[2], 0, 0, 0)); $todaysDate = date(mktime(0,0,0, $month, $day, $year)); $eventDate = date(mktime(0,0,0,$eventMonth,1,$eventYear)); $diff = $eventDate - $todaysDate; $actualDate = date('M', mktime(0,0,0,$eventMonth,1,$eventYear)) . ' ' . $eventDay . ', ' . $eventYear; $event = new Event($name, $location, $description, $actualDate, $eventDate, $time); if ($diff > 0) { $events[$counter] = $event; $counter++; } } } ?> My question is how do I go about sorting the event array by the $eventDay, $eventDay is the UnixTime Stamp? After I sort it I plan on displaying Events[0], Events[1] and Events[2] in order to display the next three upcoming events. Also would there be an easier way to do this? Thanks for any help! Quote Link to comment Share on other sites More sharing options...
J.Daniels Posted June 10, 2009 Share Posted June 10, 2009 Personally, when I enter dates into a database, I use MySQL's DATE types. This allows you to order by date, or select by a range of dates. However, using your database structure, you can order your results by multiple columns. $nextEvents = "SELECT * FROM calendar ORDER BY year, month, day DESC LIMIT 0, 3"; This should order the records by year, then month, then day in a descending order. The LIMIT only returns the first 3 records. Quote Link to comment Share on other sites More sharing options...
Spark_Plug Posted June 11, 2009 Author Share Posted June 11, 2009 Thanks for that SQL statement, exactly what I was looking for. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.