SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    order by not working?

    I've having trouble with the following query. The page is not displaying the results in the proper order; in fact, I can't discern any order whatsoever to the way the results are coming up. Am I missing something here?

    PHP Code:
    $result = @mysql_query("SELECT*id, bands, venue, venueaddress, date_format(showdate, '%W %M %D, %Y') as showdate, price, time, comments*FROM shows WHERE showdate >= CURRENT_DATE() order by showdate desc"); 
    Daniel

    http://www.dfbpunk.com

  2. #2
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are the stars in the query for?
    showdate and CURRENT_DATE (CURDATE) imho have a different format, what would make comparing pretty difficult.
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure where those stars came from... I just typed spaces, but they showed up as stars in this forum for some reason.

    showdate is a column in my database that is the field type "date." Is showdate a reserved word? I used that because it's a database of upcoming concerts... I meant it as in "the date of the show." That part of the query seems to work, though; it doesn't display the shows that have already happened, but the ones it does display are not in the proper order.
    Daniel

    http://www.dfbpunk.com

  4. #4
    orange pips! orange pips!
    Join Date
    Oct 2001
    Location
    Its all about location
    Posts
    652
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My suggestion would be to store 'showdate' as an integer and fill it with the time() function. Then comparing against dates would be really easy. At that point your just comparing long integers.

    You're statement and use of the ORDER BY command is correct from what I can see though.

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not familiar with using the time() function in that manner. Where would the date get converted? When I do the mysql_fetch_array? This is the line I currently have:


    PHP Code:
    $showdate $row["showdate"]; 
    How would I modify that to get it back in the proper format?

    I did notice, though, that when I leave the date as a unix datestamp and don't convert it using date_format the entries show up in the correct order, so I'm suspecting that this will work properly.
    Daniel

    http://www.dfbpunk.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •