Run many mySQL queries through php

Question

I have a database with many tables. On side 1, I have a button that passes me to side 2. On side 2, I want the code to remove every row in every named table that has a specific pid that I have stored in a variable. I have a code on side 2 but it doesn't do anything.

Code on side 2:

$pid3 = $_POST['pid2'];

$del = "DELETE FROM answer_det WHERE pid='$pid3'";
$del .= "DELETE FROM project WHERE pid='$pid3'";
$del .= "DELETE FROM question WHERE pid='$pid3'";
$del .= "DELETE FROM respondent WHERE pid='$pid3'";
$del .= "DELETE FROM result WHERE pid='$pid3'";
$del .= "DELETE FROM users WHERE pid='$pid3'";

$run = mysqli_query($mysqli,$del);

if($run)
{
   echo "<h1>Project is deleted!</h1>";
}

Better than sanitizing, use prepared statements with placeholders. https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Andy Lester, Dec 23 '15 at 18:37

Nana Partykar · Accepted Answer · 2015-12-23T17:59:03.357

4

Use mysqli_multi_query

$pid3 = $_POST['pid2'];

$del = "DELETE FROM answer_det WHERE pid='$pid3';";
$del .= "DELETE FROM project WHERE pid='$pid3';";
$del .= "DELETE FROM question WHERE pid='$pid3';";
$del .= "DELETE FROM respondent WHERE pid='$pid3';";
$del .= "DELETE FROM result WHERE pid='$pid3';";
$del .= "DELETE FROM users WHERE pid='$pid3';";

$run = mysqli_multi_query($mysqli,$del);

if($run)
{
   echo "<h1>Project is deleted!</h1>";
}

edited Dec 23 '15 at 17:59

answered Dec 23 '15 at 17:53

Nana Partykar

10,175
8
43
73

3

This is going to fail because there is no separation between queries. The string will be `DELETE FROM answer_det WHERE pid='$pid3'DELETE FROM project WHERE pid='$pid3'` and so on. – Devon Dec 23 '15 at 17:54
Complementing @Devon's comment, it's missing `;` at the end of each one.. – FirstOne Dec 23 '15 at 17:56
Thanks @Devon, FirstOne. – Nana Partykar Dec 23 '15 at 17:58
@NanaPartykar, upvoted now that it's fixed. This should be the right answer for the OP. – Devon Dec 23 '15 at 17:58
Thanks @Devon, for finding out the mistake. *I'm embarrass* – Nana Partykar Dec 23 '15 at 18:00

score 1 · Answer 2 · answered Dec 23 '15 at 17:55

You might consider altering your mysql tables where pid exists to have that field as a foreign key which cascades on delete. Something like

ALTER TABLE `answer_det` 
ADD FOREIGN KEY (pid) 
REFERENCES table_where_pid_is_the_primary_key (pid)
ON DELETE CASCADE;

replacing answer_det with the other table names to create keys there too. If you don't have access to alter the tables then likely your queries are failing because you are missing semicolons to separate them:

$del = "DELETE FROM answer_det WHERE pid='$pid3';";
$del .= "DELETE FROM project WHERE pid='$pid3';";
$del .= "DELETE FROM question WHERE pid='$pid3';";
$del .= "DELETE FROM respondent WHERE pid='$pid3';";
$del .= "DELETE FROM result WHERE pid='$pid3';";
$del .= "DELETE FROM users WHERE pid='$pid3';";

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

Or, you could just run one query, with joins, something like this:

$pid3 = $_POST['pid2'];

$del = "DELETE a,b,c,d,e,f FROM answer_det a "
$del .= "JOIN project b on b.pid=a.pid "
$del .= "JOIN question c  on c.pid=a.pid "
$del .= "JOIN respondent d on d.pid=a.pid "
$del .= "JOIN result e on e.pid=a.pid "
$del .= "JOIN users f on f.pid=a.pid "
$del .= "WHERE a.pid='$pid3'";

$run = mysqli_query($mysqli,$del);

if($run)
{
   echo "<h1>Project is deleted!</h1>";

}

How to delete from multiple tables in MySQL?

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

0

simplify your query as given below

$pid3 = $_POST['pid2'];    
 $del= DELETE FROM answer_det 't1',project 't2',question 't3' USING t1, t2,t3 WHERE t3.pid = t2.pid and t1.pid = t2.pid AND t1.pid =  $pid3
$run = mysqli_query($mysqli,$del);
if($run)
{
echo "<h1>Project is deleted!</h1>";
}

refer. delete rows from multiple tables

edited May 23 '17 at 12:31

Community

1
1

answered Dec 23 '15 at 18:14

Vigneswaran S

1,942
1
17
32

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

0

There's a good solution:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;

edited May 23 '17 at 12:31

Community

1
1

answered Dec 23 '15 at 18:20

Denis Alexandrov

155
10

score -1 · Answer 6 · answered Dec 23 '15 at 17:56

You were missing the semi-colon at the end of the query. You need to put ';' after each query in order to execute multiple queries together.

Try this:

$pid3="1";

$del = "DELETE FROM answer_det WHERE pid='$pid3';";
$del .= "DELETE FROM project WHERE pid='$pid3';";
$del .= "DELETE FROM question WHERE pid='$pid3';";
$del .= "DELETE FROM respondent WHERE pid='$pid3';";
$del .= "DELETE FROM result WHERE pid='$pid3';";
$del .= "DELETE FROM users WHERE pid='$pid3';";

echo $del

Even though that's important, that alone won't solve the problem without [mysqli_multi_query](http://php.net/mysqli_multi_query). — FirstOne, Dec 23 '15 at 17:57

Run many mySQL queries through php

6 Answers6

Linked