delete a same data from multiple table in php

Question

i have go through answer in here but i confuse... what is INNERJOIN or LEFTJOIN when delete a data. i have problem to delete same data in multiple table... this example i have

Table user_information contain user_id. primary key

table registration contain user_id

this 2 table, i didn't create foreign key.. i want to delete the row that have specific user_id data.. i try to create this php code

<?php

include ('dbconnect.php');


$matric=$_GET['user_id'];


$sql="DELETE FROM user_information, registration WHERE user_id='$matric'";

$result=mysql_query($sql);


if($result){
header("location:../adminMuser.php");

}

else {
echo "ERROR";
}
?>

<?php

mysql_close();
?>

how to make a delete than when we delete specific user_id it can delete all data that have that user_id in both table. how to use the INNERJOIN or LEFTJOIN or what necessary i need to do... my php just show error

i also try to do this

$sql="DELETE user_id FROM user_information RIGHT JOIN registration ON user_information.user_id=registration.user_id WHERE user_id='$matric' ";

still it just show "ERROR"

possible duplicate of [delete a record from two tables in php/mysql](http://stackoverflow.com/questions/12407039/delete-a-record-from-two-tables-in-php-mysql) — Peter, Dec 22 '14 at 09:49
possible duplicate of [Deleting rows with MySQL LEFT JOIN](http://stackoverflow.com/questions/2763206/deleting-rows-with-mysql-left-join) — Kuro, Dec 22 '14 at 09:51

Arun · Answer 1 · 2014-12-22T09:58:34.607

2

$sql1="DELETE FROM  registration WHERE user_id='$matric'";
$result=mysql_query($sql1); // delete the first registration table

$sql2="DELETE FROM user_information WHERE user_id='$matric'";
$result=mysql_query($sql2); // delete the user information table

edited Dec 22 '14 at 09:58

answered Dec 22 '14 at 09:50

Arun

750
5
12

how can i combine sql 1 and sql 2 to delete both at same time? what i need to change here $result=mysql_query($sql1,sql2); because previously it show problem... – farid rozman Dec 22 '14 at 11:42

Neels · Accepted Answer · 2014-12-22T10:13:16.930

0

You have three options here:

Delete when a user_id is found in both the tables.

DELETE a.*, b.* FROM user_information a
INNER JOIN registration b ON a.user_id = b.user_id
WHERE a.user_id='$matric';

Delete when a user_id is found in both tables along with NULL user_id in second table.

DELETE a.*, b.* FROM user_information a
LEFT OUTER JOIN registration b ON a.user_id = b.user_id
WHERE a.user_id='$matric';

Delete when a user_id is found in both tables along with NULL user_id in first table.

DELETE a.*, b.* FROM user_information a
RIGHT OUTER JOIN registration b ON a.user_id = b.user_id
WHERE a.user_id='$matric';

Please see this sqlfiddle for clarity.

http://www.sqlfiddle.com/#!8/97cdd/1

edited Dec 22 '14 at 10:13

answered Dec 22 '14 at 09:51

Neels

2,547
6
33
40

Oh my bad!! I dint see the question correctly initially! – Neels Dec 22 '14 at 10:00
thanks.. this help... owh that the way to use join, what a,b means? is it variable? – farid rozman Dec 22 '14 at 17:28
a, b are alias names used for the tables mentioned in the query. Its just a way of assigning a short name to a table so that you don't have to use the entire big name again and again!! – Neels Dec 22 '14 at 18:20

delete a same data from multiple table in php

2 Answers2