Delete data from two Mysql tables?

Question

Delete data from two Mysql tables?

Navigation

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

0

I would like to delete data from two different tables in Mysql with PHP, I have the following tables;

Table Magazine

id_revista|no_revista|colaboradores|imagen  |fecha_publicacion|archivo|clave
1         |No. 1     | Ana Luna    |img.jpg |2017-12-06       |1.pdf  |cb12

Table Article

id_articulo|articulo   |autor        |clave
1          |Vivir bien |Juan Sanches |cb12

the key of both tables is the same, which is saved without problem, to insert I use the following query;

public function registro($no_revista, $colaboradores, $fecha_publicacion, $imagen, $archivo, $articulo, $autor){
$sql = "INSERT INTO revista (no_revista, colaboradores, fecha_publicacion, imagen, archivo) VALUES ('$no_revista', '$colaboradores', '$fecha_publicacion', '$imagen', '$archivo')";
$consulta = $this->conecta()->query($sql);
$sql1 = "INSERT INTO articulos (articulo, autor) 
VALUES ('$articulo', '$autor')";
$consulta = $this->conecta()->query($sql1);

How do I do it so that if I delete it from the magazine table, all the articles with the same key will be removed from the table?

I have two buttons to delete and update I would like to delete the insertion of the journal table and the table the inserted data that have the same key

and the button how can I add that function?

mysql html php

asked by R.C. Ana 06.12.2017 в 17:54

source

2 answers

HTML and JS form copy records from a table with foreign keys

score 2 · Answer 1

WARNING

Your code has severe bugs the SQL injection because the user information is inserted directly into the query. Whenever possible, you should use prepared statements (if you do not know what they are, check out this article of W3). They are very easy to do using one of the APIs available for PHP ( mysqli or PDO ), where the user information is inserted using placeholders (values parameterized as ? or :name ), which are then replaced with bind_param or execute , depending on which you are using. You should NEVER enter the information in a query directly from $_POST or $_GET .

Implementing the proposed procedure you can modify the code to work as follows:

public function removerEntrada($clave) {
    $tables = array(
      array(
        "table" => "revista",
        "col" => "clave",
        "clave" => $clave
      ),
      array(
        "table" => "articulos",
        "col" => "clave",
        "clave" => $clave
      )
    );

    foreach ($tables as $entry) {
        $stmt = $this->conecta()->prepare("DELETE FROM {$entry['table']} where {$entry['col']} = ?");
        $stmt->bind_param("s", $entry["clave"]);

        if (!$stmt->execute()) {
            // Maneja el error de ejecucion
        }
    }
}

Now, how are you going to implement the use of the library mysqli I recommend you read this guide on how to configure the connection, but in summary it is technically to create an object mysql :

// Asumo que la propiedad llamada por el metodo conecta() es conexion
$this->conexion = new mysql("localhost", "user", "password", "database");

score 0 · Answer 2

Depending on the structure you have in your code and so on, I would do something like this:

(I do not know if it's the right thing to do, I'm based on the way you insert the data)

public function eliminar($clave){
    $sql = "DELETE FROM revista WHERE clave = '$clave'";
    $consulta = $this->conecta()->query($sql);
    $sql1 = "DELETE FROM articulos WHERE clave = '$clave'";
    $consulta = $this->conecta()->query($sql1);
}