SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    May 2003
    Location
    UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Displaying data from MySQL by date

    I have a MySQL database with a date field and I just want to display records with future dates.
    I currently have the following:

    PHP Code:
    $thisday date("d"); 
    $thismonth date("m");
    $thisyear date("Y"); 
    $currentdate "$thisyear-$thismonth-$thisday";

    $numresults=mysql_query("select * FROM roomtest WHERE date > ".$currentdate.""); 
    For some reason this just returns every record. If I try doing less than or equal to no records are returned.

    I tried the following later on in the script:

    PHP Code:
    for ($i=0$i<$num_results$i++)
        {
         
    $row mysql_fetch_array($result);
         if(
    $row[date]>$currentdate){
        echo
    "Display records here";
        }

    This works but is no good as I need it to limit the records when I first query the database because part of my script later on relies on $numresults being correct.
    Is it something to do with the data in the database being stored as date format so can't be compared to $currentdate until it is converted to a string?

    Any help would be appreciated.

    Cheers.

  2. #2
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you use one of the MySQL time types for the column? If so you can try:

    PHP Code:
    $numresults=mysql_query("SELECT * FROM roomtest WHERE date > Now()"); 

  3. #3
    SitePoint Member
    Join Date
    May 2003
    Location
    UK
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cheers.

    Cheers for the reply. I actually got it working in the end like the following:

    PHP Code:
    $currentdate date("Y-m-d"); 
    $numresults=mysql_query("select * from ".$room." WHERE date>='$currentdate' "); 
    Virtually identical to the way that wouldn't work except I defined $currentdate with the PHP date function.
    I'm not sure why that works and the other doesn't though.


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
  •