2006-09-08, 07:43 PM
Not really a code modification, but...
Basically I wanted to include the next 5 events from the MyBB calendar on the front page of the site, but due to the retarded way that MyBB stores the date format (I mean, there's a date type in MySQL for a reason, rather than using some dodgy string which isn't even fixed length - caused me a major headache), caused me some problems.
I had to do a pretty convuluted SQL query to get it do to this, so I decided to paste the code I had here to save other people the trouble.
Basically, the query takes the date as stored it in the table and converts it into a form that allows easy ordering and comparison against a current date (this form is Ymd).
The SQL query is as follows:
The limit 5 at the end only returns the latest 5 things. You can set it to return more if you want by altering that number.
With the results of the query, I did something like:
But you can do anything really...
Hope this helps
Basically I wanted to include the next 5 events from the MyBB calendar on the front page of the site, but due to the retarded way that MyBB stores the date format (I mean, there's a date type in MySQL for a reason, rather than using some dodgy string which isn't even fixed length - caused me a major headache), caused me some problems.
I had to do a pretty convuluted SQL query to get it do to this, so I decided to paste the code I had here to save other people the trouble.
Basically, the query takes the date as stored it in the table and converts it into a form that allows easy ordering and comparison against a current date (this form is Ymd).
The SQL query is as follows:
SELECT date,eid,subject FROM mybb_events WHERE private != 'yes' AND CONCAT(RIGHT(date, 4), CONCAT(REPEAT('0', 2-LENGTH( TRIM('-' FROM SUBSTRING(date, -7, 2)))), TRIM('-' FROM SUBSTRING(date, -7, 2))), CONCAT(REPEAT('0', 2-LENGTH( TRIM('-' FROM LEFT(date, 2)))), TRIM('-' FROM LEFT(date, 2)))) > " . gmdate('Ymd') . " ORDER BY CONCAT(RIGHT(date, 4), CONCAT(REPEAT('0', 2-LENGTH( TRIM('-' FROM SUBSTRING(date, -7, 2)))), TRIM('-' FROM SUBSTRING(date, -7, 2))), CONCAT(REPEAT('0', 2-LENGTH( TRIM('-' FROM LEFT(date, 2)))), TRIM('-' FROM LEFT(date, 2)))) ASC LIMIT 5The limit 5 at the end only returns the latest 5 things. You can set it to return more if you want by altering that number.
With the results of the query, I did something like:
while ($array = mysql_fetch_array($query)) {
$output .= "<p class=\"center\">" . $array['date'] . "<br />" . '<a href="/forum/calendar.php?action=event&eid=' . $array["eid"] . '">' . $array["subject"] . "</a></p>";
}But you can do anything really...
Hope this helps