Multiple deletions with prepared statements

Fooglmog

I've got a database with a list of countries in it. Each country is identified by an ID number and a name.

I've got a form which displays all of these countries, with a checkbox next to it to delete some from the list. When you submit the form, the ID number for each country I want to delete from the list gets put into an array (if I'm deleting ID 10 and 12 print_r will show Array ( [0] => 10 [1] => 12 ))

My issue is that the code I'm using to make the deletions is running a loop and performing an SQL query on each one. Obviously inefficient, but I haven't used prepared statements in the past and am can't seem to figure out how to make it use only one statement.

Here's my current code:

	if(!empty($_POST['delete']))
	{
		$deletions = $_POST['delete'];
		foreach ($deletions as $country){
		$stmt = $mysqli->prepare("DELETE FROM qpawn_countries WHERE id = ?");
		$stmt->bind_param("i", $country);
		$stmt->execute();
		}
	}

Any help would be appreciated.

laserlight

Unfortunately, you can't. You have to either wrap the statements in a single transaction, or build the query yourself so you can use IN.

bradgrafelman

laserlight;11006602 wrote:
build the query yourself so you can use IN.

^ This is what I personally would do. Simple example:

// protect against SQL injections
$deletions = array_map('intval', $_POST['deletions']);

// build the SQL query string
$sql = 'DELETE FROM qpawn_countries WHERE id IN (' . implode(',', $deletions) . ')';

Weedpacket

...But in terms of your existing code the simplest change would be to prepare your statement once and then use it multiple times, instead of preparing it multiple times and using it once each time.

It would still result in multiple delete commands (so the other suggestions given would still be better), but at least it would be using the prepared statement as a prepared statement.

Derokorian

No matter what I would move the prepare to before the loop. Re-preparing the query in the loop every time through totally defeats the benefit of a prepared statement when it comes to performance.