Clear data from 2 MySQL tables

Question

Clear data from 2 MySQL tables

Navigation

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

0

I need to delete data from two tables whenever they are older than a given date. The tables are the following:

Table: reservation

| id | contrato_id | fecha | ...

Table: contract

| id | tipo | precio_total | ...

Are related by the contract ID: in the reservation table (contract_id) and in the contract table (id).

The query I have so far is:

DELETE * FROM reserva as s INNER JOIN contrato as c ON s.contrato_id=c.id WHERE s.fecha < "'. $fecha_limite .'";

The problem is that both tables do not have the same number of records and this query does not work for me, it only deletes records if they have a contract done. But it may be the case that there are reserves without contract_id because it has not yet been created. How can I solve it?

mysql php

asked by Norak 23.05.2018 в 16:31

source

2 answers

Web | Modal message only in desktop resolutions I need to pass the data from one row of a table to another

score 1 · Answer 1

I think this should work, since the question has the php tag, I've done it in php

php

$conection = /*(conexion en este ejemplo mysqli)*/;
$sql_select = "SELECT id_contrato FROM reserva WHERE fecha < '".$fecha."'";
$result = $conection->query($sql);
$ids= "";
if($result){
//guardamos las id de los contratos que tengan reservas que hayan vencido
    while($row = $result->fetch_array()){
        $ids .= $row[0].", "; 
    // si las id se guardan como varchar o similar $ids .= "'".$row[0]."', ";
    }
    //le quitamos la ultima coma y espacio
    $ids = substr($ids,0,-2);
    $sql_DELETE_Reservas = "DELETE FROM reserva where fecha < '".$fecha."'";
    $result2 = $conection->query($sql_DELETE_Reservas);
    if($result2){
        $sql_DELETE_contratos = "DELETE FROM contrato WHERE id IN (".$ids.")";
        $result_Final = $conection->query($sql_DELETE_contratos);
        //Si quiere, comprobar que funciona
        if($result_Final){
            echo "EUREKA!!";
        }
    }
}

score 0 · Answer 2

First: you have to take into account that you can not do a deletion of the two tables if it depends on the date of the reservation. (if it depended on the contract_id, with the foreign key and with elimination in casacada, if the two tables could be eliminated at the same time)

Second: as it is possible that there are records greater than or equal to date_limit, then you must first eliminate the reservations less than the deadline and then validate that if a contract has no reservations then eliminate the contract.

This diagram describes how to do this elimination