-1

i got

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as idab, b.id_skb as idab FROM skd a, skb b WHERE idab='5'' at line 1** when i try to delete data from id by 2 tables... here it is,

<?php 

include './../../koneksi.php';
 

$idab= $_GET['idab'];

if (mysqli_query($koneksi, "DELETE a.id_skd as idab, b.id_skb as idab FROM skd a, skb b WHERE idab='$idab' "));{


    echo ('<script> 
        alert("Data Anda Berhasil Dihapus...!!!");
        window.location="http://localhost/administrasi_kelurahan/pengguna/administrator/Riwayat_Notif.php";
        </script>');


}

?>
Dharman
  • 30,962
  • 25
  • 85
  • 135
azem loka
  • 13
  • 7
  • What is the purpose of your query? Do you really want to remove records from two tables at once? – GMB Jun 25 '20 at 23:34
  • You can't delete columns, you can only delete rows. – Barmar Jun 25 '20 at 23:40
  • yes sir, so i want delete record from 2 table at once by each id... – azem loka Jun 25 '20 at 23:41
  • Is there a relationship between the tables? You only specified one column in the `WHERE` clause, which table is that column in? – Barmar Jun 25 '20 at 23:42
  • how can sir, anyway i want to delete this record by each id could you give me solution or some example? – azem loka Jun 25 '20 at 23:44
  • If you want to delete it from both tables, use two `DELETE` queries, don't try to do it in one query. – Barmar Jun 25 '20 at 23:46
  • i didnt use fk from each table i just want to know how can i delete 2 tables sir.. – azem loka Jun 25 '20 at 23:47
  • how can, could give me sample? – azem loka Jun 25 '20 at 23:50
  • so the purpose is, i have 2 table in db and i just completly select at once in the page on 1 table html, but when i want to delete at once from 1 table html it notice me some errors... cuz i think i can select at once..... and i made a delete and i try like i do in select.... – azem loka Jun 26 '20 at 00:00
  • 2 delete queries? but anyway how can i fakes 2 id just 1? is aliases does it work? – azem loka Jun 26 '20 at 00:04

2 Answers2

0

When trying to Delete from two tables, you should use a transaction to couple the two seperate delete operations with a clean rollback possibility. Otherwise you might be left with the record deleted in one table an not deleted in the other. If the two tables a linked by referencial integrety, then first delete it in referencing table an then in the referenced table.

Martin
  • 1
  • 1
  • i just do like these, cuz i make select and it works, so i just want to know does it works in delete.... – azem loka Jun 26 '20 at 00:24
0

Perform two DELETE queries, not one. Use a transaction to make it atomic.

$stmt1 = mysqli_prepare($koneski, "DELETE FROM skd WHERE idab = ?");
mysqli_stmt_bind_param($stmt1, "s", $idab);
$stmt2 = mysqli_prepare($koneski, "DELETE FROM skb WHERE idab = ?");
mysqli_stmt_bind_param($stmt2, "s", $idab);
mysqli_query("START TRANSACTION");
mysqli_stmt_execute($stmt1);
mysqli_stmt_execute($stmt2);
mysqli_query("COMMIT");

I've also shown how to use a prepared statement to prevent SQL injection.

You can't do it in a single query unless there's a relationship between the tables, such as a foreign key. In that case you would probably make use of ON DELETE CASCADE to automatically delete from the child table when you delete from the parent table.

If the data is related, you can use a JOIN

DELETE a, b
FROM skb AS a
JOIN skb_pengikut AS b ON a.id = b.id_skb
WHERE a.id = ?

But if you declare id_skb as a foreign key with the ON DELETE CASCADE option, then you just have to do:

DELETE FROM skb
WHERE id = ?

and it will automatically delete from id_pengikut.

Barmar
  • 741,623
  • 53
  • 500
  • 612