Bulk delete of records in PHP page?

[AdrianLe]

I have a client page I built using DW8's PHP behaviors.. listing all recordsets from a mySQL table. The client wants to add the famous check boxes so that he can either select individual records, or select all of them and delete them all with one click of a button.  I'm kind of new to PHP so don't know how to either tweak a Behavior to do this.. or if someone knows of such an extension to help me achieve this.. or even just a tutorial on how one might accomplish these things, that would be great. THanks-

[w1n78]

not sure how to do this within dreamweaver's built in utilities or a plugin. here are some suggestions but they might be more advanced.

you can create an array that represents the values of each of the checkboxes. run a loop. each loop will delete a record. let's say you have a users table with an id and username column and you tell it to display all the records from that table...

<?php

$rs = mysql_query("select * from users");

?>

<form>

<?php while ($row = mysql_fetch_assoc($rs)) { ?>

<input name="box[]" value="<?php echo $row['id']; ?>" /><?php echo $row['username']; ?>

<?php } ?>

<input id="submit" name="submit" type="submit" value="submit" />

</form>

the box[] let's you create arrows. in the page where you would delete it would look something like...

<?php

if (isset($_POST['submit'])) {

  $array_box = $_POST['box'];      // creates array based on the boxes checked from the form

  for ($i=0; $i<count($array_box); $i++) {     // loop through the array

    if ($array_box) {

      $rs = mysql_query("delete from users where id = ".$array_box[$i]); // deletes only records where id = the box checked

    }

  }

}

?>

your second option - which is arguably the preferred way, is to create a stored procedure in mysql. going this route the mysql server will do all the heavy lifting instead of php and the browser. if the array is big, the loop may cause php to timeout. there are other reasons why it's better to go this route. option 1 is a "quick and dirty" example.

hope that's enough to get you started.

[David_Powers]

w1n78 wrote:

<?php

if (isset($_POST['submit'])) {

  $array_box = $_POST['box'];      // creates array based on the boxes checked from the form

  for ($i=0; $i<count($array_box); $i++) {     // loop through the array

    if ($array_box) {

      $rs = mysql_query("delete from users where id = ".$array_box[$i]); // deletes only records where id = the box checked

    }

  }

}

?>

Using a loop to delete each record individually is inefficient. You also need to escape the values submitted from user input. Otherwise, there's a danger of SQL injection.

<?php
if (isset($_POST['submit'])) {
  $boxes = implode(',', $_POST['box']);
  mysql_query('DELETE FROM users WHERE id IN(' . mysql_real_escape_string($boxes) . ')');
}
?>

That joins the array elements as a comma-separated string, and creates a SQL query similar to this, which deletes the selected records in a single operation:

DELETE FROM users WHERE id IN(3,5,17)