At the moment we record a users last 20 media views so that they can have a brief history page of what they've looked at recently.
To do this we insert the media ids into a table.
To keep the table a small size it's currently only 20 items per user so before inserting the row we check to see how many history ids they currently have in there, if it's less than 20 then we just insert the new row, if it's more than 20 then we have to select the last row and delete it before the new row can be inserted.
$historyResult = mysql_query("SELECT id FROM mediatable WHERE userId = $userId ORDER BY id ASC");
if(mysql_num_rows($historyResult) >= 20)
{
$historyResult = mysql_query("SELECT id FROM mediatable WHERE userId = $userId ORDER BY id ASC LIMIT 1");
$historyRow = mysql_fetch_row($historyResult);
mysql_query("DELETE FROM mediatable WHERE id = '$historyRow[0]'");
}
mysql_query("INSERT INTO mediatable (userId, mediaId) VALUES ($userId, $mediaId))");
I'm now converting the site to more modern code and will be using pdo queries, my quesiton is:
Is the above a good way to approach this or should I use another method such as a MySQL trigger on insert?