Hi there.

I am trying to select rows from a db which are prior to the date of submission. SQL doesn't seem to like the format which PHP passes the time in. I've tried a few formats, but none seem to work, including the one which the data in the SQL db matches.

// SPIT OUT NEW!
$time = date('Y-m-d h:m:s');
echo $time; //just to check format

$new  = "SELECT * FROM `posts` WHERE `time` < '%time'";
$newcheck = mysql_query($new);

if (mysql_num_rows($newcheck) > 0)
{
blah blah blah
}

Once again, I am sure it is something really simple, but I just can't fathom it.

Many thanks 🙂

    What is %time? It should be $time in that query. %time doesnt mean anything. And use mysql's own date funcs:

    $new  = "SELECT * FROM `posts` WHERE `time` < NOW()'";

      Thanks, that was very useful.

      The % was just a slip.. the correct $ was in the final code. Using now() is okay for previous events, but how would I limit them back to a basedate by passing the date from PHP ie, some prior time < > NOW() ?

        Depends on the format of the Database value.

        Column type => Format
        DATETIME => 0000-00-00 00:00:00
        DATE => 0000-00-00
        TIMESTAMP => 0000-00-00 00:00:00
        TIME => 00:00
        YEAR => 0000

        If you use mySQL's UNIX_TIMESTAMP() function then that is just the seconds since the Unix epoch, which can then be converted into any date format in any program 😉 Also note, that previous to mySQL 4.1, the timestamp was without formatting (YYYYMMDDHHMMSS).

        Personally, I prefer to use Unix Timestamps to compare everything. This way, I can format it as I please (or as my users please) and comparison is very, very easy. If x > y or if x < y do K. Now, I'm not comparing dates that are formatted, but rather numerical values. This way, you don't worry about formatting until the very end when you display it for the user. Honestly, it's a very nice way to do it.

        some prior time < > NOW()

        Do you mean user-input is between some prior time and NOW()? If so, then just do this:

        SELECT *
FROM `posts`
WHERE UNIX_TIMESTAMP(time) > UNIX_TIMESTAMP($some_prior_time)
    AND UNIX_TIMESTAMP(time) < UNIX_TIMESTAMP(NOW())

        This will allow you to choose any time and put it in whatever format you please. UNIX_TIMESTAMP is a nice function to go from formatted dates, to actual seconds easily. Now you're comparing times, and NOT formatted dates. Much easier.

        To get the information into a human readable format, you'd either use FROM_UNIXTIME() or just something quick like date($timestamp);

          Thanks again.. I've almost cracked it, but I really don't follow why this isn't working.

          $new  = "SELECT * FROM `posts` WHERE `time` > $timenow AND time < NOW()";

          if $timenow is set to 20050808210000 or something directly as in:

          $timenow = 20050808210000;

          that statement works and returns the correct results, but if the variable uses the date function ,ie,

          $timenow = date(YmdHis);

          No results are returned. Any ideas?

            Other than the fact that one is a string, and the other an integer, no. I have no idea why it wouldn't work.

              Odd. It seems to work now. Very strange.

              $new  = "SELECT * FROM `posts` WHERE `time` > $timenow AND 'time' < NOW()";

              Thanks for all your help, guys 🙂

                Write a Reply...