www.webdeveloper.com
Results 1 to 9 of 9

Thread: Select query - all dates in future.

  1. #1
    Join Date
    Aug 2004
    Location
    70 klicks above the Do Lung bridge
    Posts
    654

    [RESOLVED] Select query - all dates in future.

    I am trying to select all records which have a date (integer timestamp) greater than the current day. I thought I had it with the code below but I guess I was wrong. Can anyone point me in the reight direction please? Am I wrong with (CURDATE())?
    PHP Code:
    $result mysql_query("SELECT * FROM gigs WHERE gig_date > (CURDATE()) order by gig_date ASC "); 
    Last edited by Dopple; 03-23-2006 at 09:21 AM.

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    what about CURRENT_TIMESTAMP()?

    Wait, you have this as an int? why?! you should always use a date/time column and then format the results. costs less to do that than to store an int and then convert it to something else.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Aug 2004
    Location
    70 klicks above the Do Lung bridge
    Posts
    654
    Thanks. I take it I would just format it in the same way as I did with the int?
    PHP Code:
    $date $row["gig_date"];
    $date date("l tS of F y"$date); 
    I'll try that out.

  4. #4
    Join Date
    Aug 2004
    Location
    70 klicks above the Do Lung bridge
    Posts
    654
    Ok I've changed it to DATE format so the date's are listed as 2006-03-23 but I'm still insure as to how to get all the records with a date in the future. What I have now is
    PHP Code:
    $now date("Y-m-d");
    $result mysql_query("SELECT * FROM gigs WHERE gig_date > $now order by gig_date ASC "); 
    the whole code is below
    (edited)
    PHP Code:
    <?php
      
    include( "pagehead.php" );
      include( 
    "misc.inc" );
      
    $result mysql_query("SELECT * FROM gigs WHERE gig_date > NOW() order by gig_date ASC ");
    ?>
    <div class="content">
    <h1>Gigs listing</h1>
    <h2>Upcoming gigs</h2>
    <?php
      
    while ($row mysql_fetch_assoc($result)) {
      
    $date $row["gig_date"];
      
    $date date("d/m/Y"$date);
      
    $location $row["gig_location"];
      
    $info $row["gig_description"];
      echo 
    "<p>$date<br />$location<br />$info</p>";
    }
    ?>
    </div>
    <div class="content">
    <h2>Previous gigs</h2>
    <?php
      $result2 
    mysql_query("SELECT * FROM gigs WHERE gig_date < NOW() order by gig_date ASC ");
      while (
    $row2 mysql_fetch_assoc($result2)) {
      
    $date2 $row2["gig_date"];
      
    $date2 date("d/m/Y"$date2);
      
    $location2 $row2["gig_location"];
      
    $info2 $row2["gig_description"];
      echo 
    "<p>$date2<br />$location2<br />$info2</p>";
    }
    ?>
    <p>The band can't remember dates, but you may have seen them at one of these many venues they've played.</p>
    <hr />
    <p class="gig">Droothy Neebors - Dundee</p>
    <p class="gig">The New Westport Bar - Dundee</p>
    <p class="gig">The Beat Bar - Dundee</p>
    <p class="gig">The Rothes Halls - Glenrothes</p>
    <p class="gig">The Subway - Edinburgh</p>
    <p class="gig">The Path Tavern - Kirkcaldy</p>
    </div>
    </body>
    </html>
    Last edited by Dopple; 03-23-2006 at 07:16 AM.

  5. #5
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    your query should be one of these:

    Code:
    SELECT * FROM gigs WHERE gig_date > NOW() order by gig_date ASC
    Code:
    SELECT * FROM gigs WHERE gig_date > CURDATE() order by gig_date ASC
    You're passing it in a string when you do it at the application side instead of the db side.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  6. #6
    Join Date
    Aug 2004
    Location
    70 klicks above the Do Lung bridge
    Posts
    654
    Thanks that's sorted out the problem of precious and future gigs but now the problem is that the DATE data is not being shown. It's just showing 01/01/1970. The data is in the database correctly. It's just not showing as planned. I reckon it's to do with the date() function as it's not a time stamp I'm using. can someone help here? I've amended the code above to show a true representation of gigs.php.

  7. #7
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    select the date format of the column.

    * returns raw data, you should look back at the article named "Why * is bad." or something like that.

    use this:

    PHP Code:
    $sql "SELECT date_format(`gig_date`,'%d/%m/%Y') gig_date, `gig_location`, `gig_description` FROM your_table....";
    //then do the query
      
    $gig_date $row2['gig_date'];
      
    $location2 $row2["gig_location"];
      
    $info2 $row2["gig_description"];
    //and continue 
    and if you want to format your date differently, http://dev.mysql.com/doc/refman/4.1/...functions.html and look for the function "DATE_FORMAT"
    Last edited by chazzy; 03-23-2006 at 08:53 AM.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  8. #8
    Join Date
    Aug 2004
    Location
    70 klicks above the Do Lung bridge
    Posts
    654
    Thanks Chazzy. That's sorted it. It's ok, that's how the date should be. At least now I know if the user would prefer a DATETIME column rather a DATE one I can get it working.

  9. #9
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    FYI, the functions work the same on DATETIME, DATE, and TIMESTAMP types.

    at least they're supposed to.

    even if you used an int column, you could use a datetime function (i think that exists) and convert.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles