How to delete the first 10 records of a table when it reaches a certain number of rows?

Question

How to delete the first 10 records of a table when it reaches a certain number of rows?

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

Good night, I have a table in SQL where I will put movie recommendations for the users, but I want it when it reaches a total of records in this table, it deletes the oldest records.

My code looks 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";
}

php sql tabela

asked by anonymous 12.10.2016 / 01:32

2 answers

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 used in an organizational way, for example the column that contains the date of the records so it will delete the 10 oldest ones.

27.11.2016 / 17:54

Concurrent Use in Asp.net Applications [closed] Get names of all fields with the required tag

score 1 · Accepted Answer

Can not put a maximum value of rows in the table, so you need to create a method for this.

You can give COUNT to the table and, if the value is greater than the maximum, DELETE the oldest (using ID, date or what allows this 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 you are using, but you can 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 bound to the DB).