Deleting multiple rows from mysql with checkbox?

Question

Hi, I would like to apologize if the duplicate of this question exist. i tried to find and could find anything here that could solve my problem..

I am using a form to get the input and update it in the mysql database, and then retrieve the records in the html form, and have defined the code for deleting the records individually through hyperlinks. however i want to do more, i want to use the checkboxes to delete the multiple records.

my code goes like this.

<?php
//include connection string
include('connection.php');
?>
<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post"/>
Username : <input type="text" name="user"/><br />
Password : <input type="password" name="pass"/><br />
<input type="submit" name="submit" value="Send"/>
</form>
<?php
// query to insert into database
if(isset($_POST['user']) && isset($_POST['pass'])) {
    $user = empty($_POST['user']) ? die(mysql_error()) : mysql_escape_string($_POST['user']);
    $pass = empty($_POST['pass']) ? die(mysql_error()) : sha1(mysql_escape_string($_POST['pass']));
    $query = "INSERT INTO users(name, pass) VALUES ('$user', '$pass')";
    $result = mysql_query($query) or die(mysql_error());
}
if(isset($_GET['id'])) {
    //query to delete the records
    $query = "DELETE FROM users WHERE id = " . intval($_GET['id']);
    $result = mysql_query($query);
}  
//query to retrieve records
$query = "SELECT * FROM users";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0 ) {
    echo "<table cellpadding=10 border=1>";
    while ($row = mysql_fetch_row($result)) {
        echo "<tr>";
        echo "<td>" . $row[0] . "</td>";
        echo "<td>" . $row[1] . "</td>";
        echo "<td>" . $row[2] . "</td>";
        echo "<td><a href = " . $_SERVER['PHP_SELF'] . "?id=" .$row[0] . ">delete</a>";
        echo "</tr>";
    }
    echo "</table>";
}
?>

i would like you to know that i am a newbie to programming world and i am not so sure of how exactly html checkbox work and how do i use it to delete the multiple records. i want to know what extra code do i have to write for it, and i would appreciate a lot if someone explains me that extra code in brief..

thank you..

Answer 1

First of all you need a checkbox and the id you want to delete:

<input id="delete" type="checkbox" name="delete" /><label for="delete">Delete user</label>
<input type="hidden" name="user_id" value="12345" />

You can then test if the checkbox has been set and then manually set the GET parameter to reuse your existing code:

if(isset($_POST['delete'])){
    $_GET['id'] = $_POST['user_id'];
}

That's not the most elegant solution but a really simple one that should work with your code.

Answer 2

try an SQL query with a list of IDs

... WHERE id=$sentIds[0] OR id=$sentIds[1] OR ...

or use a set operation

 ... WHERE id IN ($i1,$i2 ... );

You sure have to send ids in the form for this to work, but You know that ;)

Answer 3

This is probably a good time for another form:

<?php
// query to insert into database ...
// ... etc...

if(isset($_POST["formDeleteSelected"])) {
    //query to delete the records
    $query = "DELETE FROM users WHERE id IN (" . implode(", ",$_POST["rowid"]) . ")";
    $result = mysql_query($query);
    header("Location: mycode.php");    // just so 'refresh' doesn't try to run delete again
    exit();
}
?>

<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post">

<?php
//query to retrieve records
$query = "SELECT * FROM users";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0 ) {
    echo "<table cellpadding=10 border=1>";
    while ($row = mysql_fetch_row($result)) {
        echo "<tr>";
        echo "<td><input type="checkbox" name="rowid[]" value=\"" . $row[0] . "\" /></td>";
        echo "<td>" . $row[0] . "</td>";
        echo "<td>" . $row[1] . "</td>";
        echo "<td>" . $row[2] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
}
?>

<input type="submit" name="formDeleteSelected" text="Delete Selected" />
</form>

Or something like that (I haven't actually tried that code so there may be a typo). Also note that you should make sure to sanitize any form/get inputs for SQL Injection (plenty of information on that in other Stack Overflow questions).