Get and Increment values in a column by 1 selected randomly by id and time

Question

I want to get 3 random values in a column and increment them by 1.

• Each selected value in the stats column should not have been entered into the database past 7 days
• Each value in the stats column should then be incremented by 1.

To select the values I want I've used:

//  $conn = new PDO...
{

    $date = date_create('now', timezone_open('America/Los_Angeles'));
    $date = date_format($date, 'Y-m-d H:i:s');

    $rand = $conn->prepare("SELECT `id`, `stats`,timestamp FROM `stats_db` WHERE TIMESTAMPDIFF(day,`timestamp`,'".$date."' ) < 7  ORDER BY RAND() LIMIT 3");
    $rand->execute(); 
    
}

I now need to increment the values and update them into the row. How can I do that?

Answer 1

You can use ORDER BY and LIMIT in an UPDATE query, so you don't need to SELECT first.

There's also no need to substitute $date into the query, you can get the date in MySQL.

$stmt = $conn->query('
    UPDATE stats_db 
    SET stats = stats + 1
    WHERE `timestamp` > DATE_SUB(NOW(), INTERVAL 7 DAY)
    ORDER BY RAND() 
    LIMIT 3');