CODEWITHSUNDEEP
phpmysql
S.Mason
S.Mason

Reputation: 3647

Multiple DELETES in one query are not working on PHP page, but working in phpMyAdmin

I want to run one query with multiple statements to delete records from my database all at once. I construct a mysql query like so:

$query  = "DELETE FROM myTable1 WHERE indexID = '".$myVar."' LIMIT 1; ";
$query .= "DELETE FROM myTable2 WHERE indexID = '".$myVar."' LIMIT 1; ";
$query .= "DELETE FROM myTable3 WHERE indexID = '".$myVar."' LIMIT 6; ";

Then, I call my query something like this:

if(!$result = mysqli_query($db, $query)) { 
   echo "error with $query"; 
}

Am I doing something incorrect? The query that gets printed out, when copied and run inside phpMyAdmin works just fine.

Upvotes: 0

Views: 134

Answers (3)

S.Mason
S.Mason

Reputation: 3647

The answer to this question was to use the built-in php function: 

mysqli_multi_query($db, $query)

It's not limited to just DELETE queries, but any multi-statement queries. But since DELETE doesn't return any values I am interested in like a SELECT would, we don't need to worry so much about the result set of the query.

In case we are concerned with the results set then array with loop answer provided by kushal could be a helpful starting point.

Upvotes: 1

Kushal Suthar
Kushal Suthar

Reputation: 423

I think you should create a array of query and then you execute in loop like 

       $query  = array["DELETE FROM myTable1 WHERE indexID = '".$myVar."' LIMIT 1",
         "DELETE FROM myTable2 WHERE indexID = '".$myVar."' LIMIT 1",
          DELETE FROM myTable3 WHERE indexID = '".$myVar."' LIMIT 6"];

and then you you execute in loop

        for($i=0; $i<sizeof($query);$i++)
            {
               $result[$i]=mysql_query($con,$query[$i]);
            }

Upvotes: 1

Crackertastic
Crackertastic

Reputation: 4913

You would need to execute the statements separately. I do not believe the mysqli_query() function supports multiple statement.

$query[] = "DELETE FROM myTable1 WHERE indexID = '".$myVar."' LIMIT 1; ";
$query[] = "DELETE FROM myTable2 WHERE indexID = '".$myVar."' LIMIT 1; ";
$query[] = "DELETE FROM myTable3 WHERE indexID = '".$myVar."' LIMIT 6; ";

foreach($query as $q) {
    $result = mysqli_query($db, $q);
}

Upvotes: 0

Related Questions