SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Member Reaper2047's Avatar
    Join Date
    Jan 2005
    Location
    Tennessee
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Query returns wrong rows

    Hi everybody, my name is James. Long time lurker, first time poster, I try to visit the site daily and have learned lots from it...

    Anyway, I have a php script that querys a MySQL database and returns a list of current events, I'm trying to only return events that are > or = to the current date. But it returns rows that have a previous date.

    Here are the relevent parts of the code.
    Code:
    $curdate = time();
    Code:
    $events = @mysql_query("SELECT * FROM Events, Authors WHERE $curdate<=Events.Event_Date AND Events.Author_ID=Authors.AID ORDER BY Events.Event_Date ASC LIMIT 10");
    Thanks for any help you can provide.

  2. #2
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, James. Glad to see you decided to emerge from lurkdom.

    I'm sure you whole problem is with the way you're querying the date. The question is, what kind of date format is being stored in your database? That will dictate just how to setup your query and get the kind of results you're seeking.

    Hope this helps.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  3. #3
    SitePoint Member Reaper2047's Avatar
    Join Date
    Jan 2005
    Location
    Tennessee
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right now the Event_Date column is MySQL date type, and the date is inserted in the format of Y-m-d.

    For some reason when I output the results of the query showing an unmodified date (change of format to d/m/Y) it shows me seconds (like the time function will). I don't know if this has anything to do with it, I'm still pretty new to PHP and MySQL.

  4. #4
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi James. Rather than assigning a variable in PHP to the current time, you could just use the MySQL functions NOW() or CURDATE(). For more info see section 13.5 of the MySQL manual.

    Your query could be changed to:

    SELECT * FROM Events, Authors WHERE Events.Event_Date >= NOW() AND Events.Author_ID=Authors.AID ORDER BY Events.Event_Date ASC LIMIT 10

    Hope this helps.

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2004
    Location
    USA
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    would this work?
    PHP Code:
      <?php
      
      $curdate 
    date('Y-m-d');
      
     
    $sql 'SELECT * FROM Events, Authors WHERE ( ( Events.Event_Date <= "' $curdate '" ) AND ( Events.Author_ID = Authors.AID ) ) ORDER BY Events.Event_date ASC LIMIT 0,10';
      
      
    $result = @mysql_query($sql$conn_id);
      
      
    ?>
    ?????

  6. #6
    SitePoint Member Reaper2047's Avatar
    Join Date
    Jan 2005
    Location
    Tennessee
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    WooHoo!

    Thanks for all your quick responses guys!
    I used djones's suggestion of the NOW() function from MySQL and it worked.

    Again, thanks much ya'll.


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
  •