Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts

    php date ordering problem

    Hi, this is frustratingly simple but i can't work out the problem

    I want to find the next upcoming event in the database... by looking at the event date. I'm doing this by storing todays date as $todaysdate and then in the WHERE clause looking for "gigdate >= $todaysdate". The gigdate field is formatted as 'date' in the database (so stored as 2012-12-01 etc..).

    PHP Code:
    $todaysdate date("Y-m-d");
    $findnextgig mysql_query("SELECT * FROM gig WHERE venueid = $thevenue AND gigdate >= $todaysdate");

    if(
    $nextgig mysql_fetch_array($findnextgig)){
        echo 
    $nextgig['gigid'];
        }
        else
        {
                 echo 
    "<p>no upcoming gigs</p>";
        } 
    I have 3 piece of test data in the table, each 2011, 2012, and 2013 dates. And it keeps showing the ID for the 2011 date?
    Last edited by paddyfields; 04-14-2012 at 02:33 PM.

  • #2
    Regular Coder
    Join Date
    Mar 2011
    Posts
    148
    Thanks
    0
    Thanked 20 Times in 20 Posts
    Hi
    To use mathematical operators, like '<' to compare two values, they must be numerical. So, in this case try use Timestamp:
    PHP Code:
    $todaysdate time();
    $findnextgig mysql_query("SELECT * FROM gig WHERE venueid = $thevenue AND UNIX_TIMESTAMP(gigdate) >= $todaysdate"); 

  • #3
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts
    web five

  • #4
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    you can use mathematical operators with the values in date/datetime fields but you need to quote your date string. 2012-04-14 doesn't say the 14th of April 2012, it says 2012 minus 4 minus 14

    PHP Code:
    $todaysdate date("Y-m-d");
    $findnextgig mysql_query("SELECT * FROM gig WHERE venueid = $thevenue AND gigdate >= '$todaysdate' order by gigdate limit 0,1"); 

  • Users who have thanked NancyJ for this post:

    paddyfields (04-14-2012)

  • #5
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts
    Thank you Nancy that works perfectly.

    (..bit of a facepalm moment now you've pointed out the subtracting of the numbers).

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    You don't need PHP for the actual date, and I'd drop that too. Assuming that you are using a subset of the DATETIME datatype in MySQL (which appear to be the case), you can simply use:
    Code:
    SELECT * FROM gig WHERE venueid = $thevenue AND gigdate >= CURDATE() order by gigdate limit 0,1
    And put the burden on MySQL. That will compare just the date parts of the gigdate to today. If gigdate includes a time, you can use the NOW() function instead to ignore records where gigdate has passed the time for today. I'd do this in something like a listing of upcoming (since its no longer upcoming but passed), and I'd use curdate() for something like a calendar view (since its not complete until the day is done).

  • Users who have thanked Fou-Lu for this post:

    paddyfields (04-16-2012)

  • #7
    Regular Coder
    Join Date
    Dec 2010
    Location
    London
    Posts
    339
    Thanks
    63
    Thanked 11 Times in 11 Posts
    Sweet, thank you.


  •  

    Posting Permissions

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