CODEWITHSUNDEEP
phpmysql
AndroidDev
AndroidDev

Reputation: 21247

PHP MySQL Delete Rows older than 1 minute

Sorry for this post that has many answers, but I'm spinning my wheels and am out of ideas. Really simple: delete all records older than 1 minute:

function deleteExpiredKeys ($link) {

    $date = date("Y-m-d H:i:s");
    $time = strtotime($date);
    $time = $time - (60);
    $date = date("Y-m-d H:i:s", $time);

    $sql = 'DELETE from Session_Keys
            WHERE updated_at < ' . $date;

    $result = mysqli_query($link, $sql);

    if (!$result) return false;

    return true;
}

'updated_at' is a datetime field.

This doesn't throw any errors, but it doesn't delete anything either. I've tried many different things, most of them more elegant than what I am posting here, but nothing works. Can anyone help me? Thank you.

Upvotes: 2

Views: 717

Answers (2)

T0xicCode
T0xicCode

Reputation: 4951

MySQL will silently convert properly formatted strings to dates when comparing against a date/datetime. Your code would send the date not as a string, but as a sequence of characters (strings need to be delimited by quotes).

Try this:

function deleteExpiredKeys ($link) {

$date = date("Y-m-d H:i:s", strtotime('1 minute ago'));

$sql = 'DELETE from Session_Keys
        WHERE updated_at < ?';

$stmt = mysqli_prepare($link, $sql);
mysqli_stmt_bind_param($stmt, 's', $date);
$result = mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

if (!$result) return false;

return true;

}

It uses preprared statement, which are a best practice.

Upvotes: 0

Atli
Atli

Reputation: 7930

You don't need to create the time in PHP. MySQL is capable of doing the time calculations by itself.

DELETE from Session_Keys
WHERE updated_at < NOW() - INTERVAL 1 MINUTE

Upvotes: 1

Related Questions