CODEWITHSUNDEEP
phpmysqlfor-loopif-statementconnection
Martin
Martin

Reputation: 9

PHP - save the recent 3 viewed cars in DB

I want to save the car id and a timestamp to a DB. But only the last 3 viewed cars.

id  |  timestamp    |       VehicleId
01 |23-09-21 22:53|XIEKFWSH7S5GESQDGS
02 |23-09-21 24:32|GSTRDDHSDE5GHRDSGD
03 |23-09-21 46:17|LDHGUC86SJNB6%HNSH

Check if there is a old timestamp and overwrite would be preferred (if, then else), as it would generate a lot of data.

Upvotes: 0

Views: 29

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

One approach would be to add the following index to your table:

CREATE INDEX idx ON yourTable (timestamp);

Then, run this query:

SELECT *
FROM yourTable
ORDER BY timestamp DESC
LIMIT 3;

Finally, to deal with data you don't need, you could periodically delete older data from the data, except for the top three most recent rows.

DELETE t1
FROM yourTable t1
LEFT JOIN
(
    SELECT id
    FROM yourTable
    ORDER BY timestamp DESC
    LIMIT 3
) t2
    ON t2.id = t1.id
WHERE
    t2.id IS NULL;

Upvotes: 1

Related Questions