Delete multiple table by given a specific id in php

Question

Good day. I'm new to php and now wanted to learn on how to delete 3 tables from android to php by given a specific ID.

Table Info (id, name) Table Good (id, sun, tf) Table Bad (id, moon, ts)

    <?php 
     //Getting Id
     $id = $_GET['id'];

     //Importing database
     require_once('dbConnect.php');

     //Creating sql query
     $sql = "DELETE FROM info WHERE id=$id;";
     $sql ="DELETE FROM Good WHERE tf=$id;";
     $sql ="DELETE FROM Bad WHERE ts=$id;";

     //Deleting record in database 
     if(mysqli_query($con,$sql)){
     echo 'Employee Deleted Successfully';
     }else{
     echo 'Could Not Delete Employee Try Again';
     }

     //closing connection 
     mysqli_close($con);
?>

Error

Latest code

<?php 
 //Getting Id
 $mysqli->multi_query(" Many SQL queries ; ");
 $id = (int)$_GET['id'];

 //Importing database
 require_once('dbConnect.php');

 //Creating sql query
 $sql  = "DELETE FROM informaation WHERE id=$id;";
 $sql .= "DELETE FROM work_force WHERE twf=$id;";
 $sql .= "DELETE FROM work_details WHERE ts_id=$id;";

 //Deleting record in database 
 if(multi_query($con,$sql)){
 echo ' Deleted Successfully';
 }else{
 echo 'Could Not Delete . Try Again.' . mysqli_error($con);
 }
 ?>

you're overwriting your first 2 queries. Use a multi query. http://php.net/manual/en/mysqli.multi-query.php — Funk Forty Niner, Jan 04 '16 at 03:07
No, that would require a JOIN and you don't need to and would require quite a bit more code. I left you a link up there as a link. It's all in there. — Funk Forty Niner, Jan 04 '16 at 03:12
@Fred-ii- how about this `$sql = "DELETE FROM info INNER JOIN Good INNER JOIN Bad WHERE info.id='$id' AND Good.tf ='$id' AND Bad.ts='$id';"`; — , Jan 04 '16 at 03:18
TBH, I am awful with JOINS but you can try it and check for errors also. I posted something for you below in the answers area. — Funk Forty Niner, Jan 04 '16 at 03:21
as per your image error, the fault is in how you assigned your connection. Once you fix that, the multi query will work. Please consult the manual on mysqli connection http://php.net/manual/en/function.mysqli-connect.php — Funk Forty Niner, Jan 04 '16 at 03:38
basically, you did not connect `$mysqli->multi_query(" Many SQL queries ; ");` you need to connect to your database first as per the link I left you just above. However you need to place `require_once('dbConnect.php');` as your first line, and not below where you are doing `$mysqli->multi_query(" Many SQL queries ; ");` that needs to be removed. The other code was only an example. — Funk Forty Niner, Jan 04 '16 at 03:40
You added `$mysqli->multi_query(" Many SQL queries ; ");` on top and that is why you got that error about the undefined variable mysqli. reload my answer below. — Funk Forty Niner, Jan 04 '16 at 03:46

score 0 · Accepted Answer · edited May 23 '17 at 12:31

0

You need to use a multi-query because you are presently overwriting your first 2 queries.

I modified your code to concatenate the query statements and the proper function for it, including error checking. Plus, assuming you did successfully connect using the mysqli_ library.

Your require_once('dbConnect.php');

should contain this below and replace the credentials below with your own.

 $con = mysqli_connect("yourhost", "my_user", "my_password", "my_db");

Then do:

require_once('dbConnect.php');

 $id = (int)$_GET['id'];

 $sql  = "DELETE FROM info WHERE id=$id;";
 $sql .= "DELETE FROM Good WHERE tf=$id;";
 $sql .= "DELETE FROM Bad WHERE ts=$id;";

 //Deleting record in database 
 if(mysqli_multi_query($con,$sql)){
 echo 'Employee Deleted Successfully';
 }else{
 echo 'Could Not Delete Employee Try Again.' . mysqli_error($con);
 }

Reference:

http://php.net/manual/en/mysqli.multi-query.php

~~Example from the manual:~~

<?php
// WORKING CODE:
$mysqli->multi_query(" Many SQL queries ; "); // OK
while ($mysqli->next_result()) {;} // flush multi_queries
$mysqli->query(" SQL statement #1 ; ") // now executed!
$mysqli->query(" SQL statement #2 ; ") // now executed!
$mysqli->query(" SQL statement #3 ; ") // now executed!
$mysqli->query(" SQL statement #4 ; ") // now executed!
?>

Plus, make sure you do have a value for your GET array.

If it is indeed an integer, replace it with:

$id = (int)$_GET['id'];

Your present code is open to SQL injection. Use mysqli_* with prepared statements, or PDO with prepared statements.

edited May 23 '17 at 12:31

Community

1
1

answered Jan 04 '16 at 03:20

Funk Forty Niner

74,450
15
68
141

Thanks for your help :) But I get error call to undefined function multi_query() – Jan 04 '16 at 03:27
@Seng you're welcome. I replaced it with `mysqli_multi_query` (in my answer). try it again. – Funk Forty Niner Jan 04 '16 at 03:28
@Seng Plus, you are using `mysqli_` to connect with, correct? – Funk Forty Niner Jan 04 '16 at 03:29
ya,I'm using mysqli_ – Jan 04 '16 at 03:30
@Seng I made an edit earlier where you might have seen the missing semi-colons and I added them back in. I also changed `multi_query` to `mysqli_multi_query`. reload it again to see it. – Funk Forty Niner Jan 04 '16 at 03:34
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/99652/discussion-between-seng-and-fred-ii). – Jan 04 '16 at 03:43
@Seng I rewrote it. Reload it again but forget about the example below that. It was only an example. I have to go, but I'll check back tomorrow morning. Keep me posted. – Funk Forty Niner Jan 04 '16 at 03:44
Thanks . Works perfectly – Jan 06 '16 at 01:33

score 0 · Answer 2 · answered Jan 04 '16 at 04:09

0

Delete from i.info, g.good,b.bad where i.id =$id AND g.id = $id AND b.id = $id

answered Jan 04 '16 at 04:09

Nijal Patel

31
6

Delete multiple table by given a specific id in php

2 Answers2