How to delete the first ten records from a table when reaching a certain number of Rows?

Question

Good evening, I have an SQL table in which I will put movie recommendations by users, but I want when it reaches a total of records in this table, it deletes the older records.

My code is like this:

$sql = "INSERT INTO filmes_rec (filmes)
        VALUES ('$recomendacao')";

if($link->query($sql) === TRUE) {
    echo 'Obrigado por compartilhar seu gosto com a gente, você recomendou:<br>'; 
    echo '<p class="strong"><strong>';
    echo $recomendacao;
    echo '</strong></p>';
} else {
    echo "Error creating new record";
}

Answer 1

DELETE FROM filmes_rec ORDER BY nome_da_tabela ASC LIMIT 0, 10

in "table name" put the name of the table that will be using in an organizational way, for example the column that contains the date of the records so it will erase the 10 but old.

Answer 2

It is not possible to put a value of lines in the table, so you need to create a method for this.

You can give COUNT in the table and, if the value is greater than the maximum, DELETE older (using ID, date or what allows such comparison).

$sql = INSERT INTO filmes_rec (filmes)
            VALUES ('$recomendacao');

if($link->query($sql) === TRUE) {

   $count = SELECT COUNT(*)
              FROM filmes_rec
             WHERE usuario_id = x //se você tiver um identificador que precisa usar no count

   if($link->query($count) > n) {
     DELETE FROM filmes_rec
           WHERE id > n;
   }

    echo 'Obrigado por compartilhar seu gosto com a gente, você recomendou:<br>'; 
    echo '<p class="strong"><strong>';
    echo $recomendacao;
    echo '</strong></p>';
} else {
    echo "Error creating new record";
}

This way I use the same pattern that you are using, but it is possible to also do this with a PROCEDURE. Instead of running in the PHP script it will run in Mysql and is less likely to give problem by changing code or things like that (the method will be linked to DB).