Leave last 12 records and delete the rest in PHP/Mysql

Question

I would like to make sure that after running the script, there can only be the last 12 records in a specific table that has the date field in international format if it can assist.

<?php

# Este pequeno script PHP recupera o OD do imóvel visitado
# e o cadastra no banco de dados permitindo assim que o 
# módulo de ULTIMOS IMOVEIS VISUALIZADOS esteja sempre 
# atualizado.

# Variaveis
$cod_imovel = $_GET['cod_consulta'];
$data       = date('Y-m-d H:i:s');
$visitado   = 1;

#SQL Execute
$sql        = "INSERT INTO visitados (cod_imovel, visitado, data) VALUES (:cod_imovel, :visitado, :data)";
$query  = $pdo->prepare($sql);
$query->execute(array(':cod_imovel' => $cod_imovel, ':visitado' => $visitado, ':data' => $data));

#Apagar todos os registros menos os 12 últimos

?>

It seems that the server does not accept subquerys... another way?

Answer 1

This is a very simplified solution, without Queries assuming the order of ids either temporal:

// primeiro pegamos os 12 ids mais recentes, do maior pro menor
$query = $pdo->prepare( 'SELECT id FROM visitados ORDER BY id DESC LIMIT 12' );
$query->execute();
$result = $query->fetchAll();

// destes, pegamos o id do ultimo registro    
$ultimo = array_pop( $result );
$doze = $ultimo[0];

// agora, deletamos os menores que o id obtido
$query = $pdo->prepare( 'DELETE FROM visitados WHERE id < :doze' );
$query->execute( array(':doze' => $doze ) );

What can happen in a case of race condition is someone enter a record between one of the two darlings, and for some brief moment you have more than twelve recent temporarily, until further consultation.

^{_{Note: I have the impression that there must be a lot of more sensible way to get the result you want without having to delete things at all times, but it does not come to the case for the purpose of answering the question.}}

Answer 2

Hello, From what I understand, you can do something like this,

After executing the script, let’s say it enters 100 records, run another Insert.

#Clear the table

DELETE FROM <ultimos visualizados>;

After entering the new records

INSERT INTO <ultimos visualizados> ([id],[bla],[bla],[DATA])
SELECT [id],[bla],[bla],STR_TO_DATE([DATA],'%Y-%m-%d') FROM <tabela dos 100 registros> ORDER BY <campo que deseja ordenar> DESC LIMIT 12;

I hope I helped. D

Answer 3

Whereas the "last ones" are determined by the date field and the date field is unique, you can exclude everyone who is not among the last 12 dates:

delete from 
    visitados 
where
    data not in (
        select data
        from visitados
        order by data desc
        limit 12)

Or, considering you own a primary key Id as you posted in your comment:

delete from 
    visitados 
where
    id not in (
        select id
        from visitados
        order by data desc
        limit 12)

Since your Mysql version does not support LIMIT in Queries, you can use it in a LEFT JOIN deleting all records that are not "marked to be kept", that is, deleting all records that are not among the last 12:

delete visitados.* 
from visitados
    left join (
        select id, 'sim' as manter 
        from visitados
        order by data desc
        limit 12) 
        as v2 on v2.id = visitados.id
where v2.manter is null;

Tested: http://sqlfiddle.com/#! 8/3eeb4/1/0.

Answer 4

Use a subquery with the property Ids inside a NOT IN combined with a DELETE (assuming that your visited table has a primary key).

DELETE FROM visitados WHERE id_visita NOT IN (
    SELECT id_visita 
    FROM visitados 
    ORDER BY data DESC 
    LIMIT 12
)

It will only keep the last 12 of the internal query. You can apply other filters if you wish to DELETE.

DELETE FROM visitados WHERE cod_imovel = 15 AND id_visita NOT IN (
    SELECT id_visita 
    FROM visitados 
    ORDER BY data DESC 
    LIMIT 12
)