Page 1 of 1

MySQL results not ordering properly, need help...

Posted: Thu Sep 10, 2009 7:15 pm
by WithHisStripes
Heya,
So I'm working on this site: http://west-valley-community-church.theportlandco.com and the results on the right, "Upcoming Events", aren't ordering properly. It should be a list of events starting with the next date, but it's not putting them in proper order. The first item is a recurring event, so I think this may have something to do with it. I'm stumped as how to resolve this, but I noticed if I change "ASC" to "DESC" it will order them properly, just backwards.

Here's my code:

Code: Select all

        <?php
        $get_upcoming_events = mysql_query("SELECT * FROM `wp_calendar` WHERE `event_begin` >= " . date('Y-m-j') . " ORDER BY `event_begin` ASC LIMIT 3");
        
        while($show_upcoming_events = mysql_fetch_array($get_upcoming_events, MYSQL_ASSOC)) {
            echo "
                <h2><a href='" . $show_upcoming_events['event_link'] . "'>" . $show_upcoming_events['event_title'] . "</a></h2>
                <p><i>" . date('l F jS', strtotime($show_upcoming_events['event_begin'])) . " " . date('g:iA Y', strtotime($show_upcoming_events['event_time'])) . "</i></p>
                <p>" . $show_upcoming_events['event_desc'] . "</p>
                <br />
                
            ";
        }
        ?>

Re: MySQL results not ordering properly, need help...

Posted: Thu Sep 10, 2009 7:27 pm
by SimonMayer
I see that it refers on the site to "Tuesday October 14th 6:00PM 2009"

October 14th is a Wednesday, this year.
It was a Tuesday in 2008.

How is it stored in the database? Is it stored as 2008? If so, I suspect the problem might be with how strtotime() handles dates from previous years and that the MySQL is just doing what it should.

Also, out of interest, what values do you have stored for the `event_begin` field on each row?

Re: MySQL results not ordering properly, need help...

Posted: Thu Sep 10, 2009 7:51 pm
by WithHisStripes
Ah, good eye! Yeah the date in the database is 2008, I wonder why it's showing 2009 though when it's printed. It's stored in the database as a "date" type, and the dates appear as YYYY-MM-DD.

Re: MySQL results not ordering properly, need help...

Posted: Thu Sep 10, 2009 8:08 pm
by SimonMayer
it looks like the Year is being called from the event time.
I'm not terribly familiar with strtotime() but I think

Code: Select all

date('g:iA Y', strtotime($show_upcoming_events['event_time']))
will assume that it is the current year (assuming that event_time is just a time of day).

This may cause a real headache as soon as you reach 2010, as I would expect all of your events to suddenly list as 2010.

Therefore, you really should set the year, according to 'event_begin'.

Re: MySQL results not ordering properly, need help...

Posted: Thu Sep 10, 2009 8:15 pm
by John Cartwright
Is it never a good idea to rely on php's time settings, especially when there may be differences between the database and the webserver. Instead, it is better to ask mysql to return a timestamp of the datefield for php to use as a reference. I.e.,

Code: Select all

SELECT UNIX_TIMESTAMP(your_date_field) AS createdtimestamp

Re: MySQL results not ordering properly, need help...

Posted: Thu Sep 10, 2009 8:20 pm
by WithHisStripes
Okay, that makes sense. I've never used unix_timestamp though, can you show me how it would be implemented more specifically than what you've written?

Re: MySQL results not ordering properly, need help...

Posted: Thu Sep 10, 2009 8:31 pm
by John Cartwright
Sure, perhaps something like

Code: Select all

       <?php
        $get_upcoming_events = mysql_query("
           SELECT *, 
                      UNIX_TIMESTAMP(event_time) AS eventtime_timestamp, 
                      UNIX_TIMESTAMP(event_begin) AS eventbegin_timestamp  
           FROM `wp_calendar` 
           WHERE `event_begin` >= CURDATE() 
           ORDER BY `event_begin` ASC 
           LIMIT 3
        ");
       
        while($show_upcoming_events = mysql_fetch_array($get_upcoming_events, MYSQL_ASSOC)) {
            echo "
                <h2><a href='" . $show_upcoming_events['event_link'] . "'>" . $show_upcoming_events['event_title'] . "</a></h2>
                <p><i>" . date('l F jS', $show_upcoming_events['eventbegin_timestamp']) . " " . date('g:iA Y', $show_upcoming_events['eventtime_timestamp']) . "</i></p>
                <p>" . $show_upcoming_events['event_desc'] . "</p>
                <br />
               
            ";
        }
        ?>
Again, you basically want to avoid mixing mysql and php dates as they are not guaranteed to be the same. Instead of returning the date field field and converting into a timestamp with strtotime(), simply have mysql return the timestamp as a reference point for PHP.

The same thing applied to your WHERE clause, you want to use mysql's internal date functions (as demonstrated)