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

Navigation
3

I would like to make sure that after the script runs, there may only be the last 12 records in a specific table that has the date field in international format if you 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 the server does not accept subquerys ... another way?

    
asked by anonymous 29.09.2014 / 18:14

4 answers

5

This is a very simplified solution, without subqueries assuming the order of ids to be 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 race condition case is someone inserting a record between one of the two queries , and for some brief moment you have more than twelve temporary ones , until further consultation.

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

    
29.09.2014 / 19:11
2

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

After the script is executed, let's say it inserts 100 records, execute another insert.

# Clear the table 

DELETE FROM <ultimos visualizados>;

After inserting 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 have helped. : D

    
29.09.2014 / 18:31
2

Use a subquery with property IDs within NOT IN combined with DELETE (assuming 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 keep only the last 12 of the internal query. You can apply other filters if you wish in your DELETE . 

DELETE FROM visitados WHERE cod_imovel = 15 AND id_visita NOT IN (
    SELECT id_visita 
    FROM visitados 
    ORDER BY data DESC 
    LIMIT 12
)
    
29.09.2014 / 18:36
2

Considering that the "last" are determined by the date field and that the date field is unique, you can exclude all that are not between the last 12 dates: 

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

Or, whereas you have a primary key Id like 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 subqueries, you can use it in LEFT JOIN by deleting all records that are not "marked to be kept", ie deleting all records that are not in 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: link .

    
29.09.2014 / 18:35
How to use the vector class of C ++? Compare attribute id with class and give .show in another div