MySQL results not ordering properly, need help...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

MySQL results not ordering properly, need help...

Post 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 />
                
            ";
        }
        ?>
SimonMayer
Forum Commoner
Posts: 32
Joined: Wed Sep 09, 2009 6:40 pm

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

Post 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?
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

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

Post 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.
SimonMayer
Forum Commoner
Posts: 32
Joined: Wed Sep 09, 2009 6:40 pm

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

Post 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'.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

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

Post 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
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

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

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

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

Post 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)
Post Reply