PDO PHP Select Where Less Than Time

[everton.retweets]

Hi, I am trying to get the records out of a mysql database where the time is less than todays date and time -

I have set the column as DateTime in mysql and it is stored as 2014-05-31 15:00:00

    $TodayDate = date('Y-m-d H:i:s', time()+28800);
    $sttTodayDate=strtotime($TodayDate);
    // We Will prepare SQL Query
        $STM = $dbh->prepare('SELECT * FROM tbl WHERE kickoff< = :kickofftime ORDER BY kickoff Limit 1');
        $STM->bindParam(':kickofftime', $sttTodayDate);
    // For Executing prepared statement we will use below function
        $STM->execute();
    // Count no. of records 
        $count = $STM->rowCount();
    //just fetch. only gets one row. So no foreach loop needed :)
        $row  = $STM -> fetchAll(PDO::FETCH_ASSOC);

        if($results > 0){   
            //$row = mysql_fetch_array($results);
            foreach($results as $row);
            $nextGame = $row['game'];
            $gameTime = $row['kickoff'];    
        }

When I echo out $TodayDate I get 2014-05-30 09:03:
When I echo out $sttTodayDate I get 471401465827

But I am not seeing / getting any results from the database. Not sure if what way to correctly format the $TodayDate -
Anyone got any ideas what I am doing wrong ?
Thanks

[kvprajapati]

What is the field type of kickoff?

If it is DateTime then $TodayDate as bindParam variable.

$STM->bindParam(':kickofftime', $TodayDate);
$STM->execute();
$rows  = $STM -> fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row) {
   ....
}

[diafol]

$sttTodayDate=strtotime($TodayDate);

This is setting the date format to integer (unix timestamp) so, you're searching your kickoff field for an integer less or equal to this.

Even if you were storing data in the field as integer, I think your query would always retrieve just the first record in the DB (if sorted by kickoff datetime).

Also, adding seconds to a date is never a good idea, use time increment (+8 hours) if possible and the 'safer' DateTime object.

Is there any reason why you're adding 8 hours? Not to correct a server timezone setting, is it?

[everton.retweets]

Hi Diafol - Yes, I am adding +8 hours to coorect the server time.

I have tried taking the seconds off the $TodayDate var but im still not having any luck with getting any records from the db.

@Avd - I have tried to bind both $TodayDate & $sttTodayDate but I am unable to get the records from the db.

How can I edit either the db datetime or the query to get the record from the db?

[diafol]

$dt = new DateTime();
$dt->add(new DateInterval('PT8H'));
$koff = $dt->format('Y-m-d H:i:s'); // OR $koff = $dt->format('U'); //for timestamp

...

$STM = $dbh->prepare('SELECT * FROM tbl WHERE kickoff <= :koff ORDER BY kickoff LIMIT 1');
$STM->execute(array(':koff'=>$koff));
if(!$STM->rowCount())
{
    echo 'No records';
}else{
    $row = $STM->fetch(PDO::FETCH_ASSOC);
    //... process ...//
}

Should work if your sql is valid

[everton.retweets]

Wow, thanks very much @diafol