CODEWITHSUNDEEP
php
jmg0880
jmg0880

Reputation: 135

Deleting Multiple Records using Checkboxes in PHP

I am having an issue where I need to be able to delete multiple records using checkboxes.

Here is the code that I currently have.

<?php 
$host       = "localhost";
$user       = "root";
$pass       = "";
$dbName     = "ticket_history";
$table_name = "ticket_history";

################ Connect to the Database and SELECT DATA ####################################
$conn = mysql_connect($host, $user, $pass) or die ("Unable to connect");
mysql_select_db($dbName);
$query = "SELECT Date,Ticket_Number,Description,Result FROM $table_name";
$result = mysql_query($query);
$count=mysql_num_rows($result);
#############################################################################################
?>
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>
<table width=50%>
    <form method="post" action="insert_ticket.php">
        <table width border='0'>
            <tr><td> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date:<input type="text" name="date"/></td>
            <td>Ticket #:<input type="text" name="ticket"/></td></tr>
            <table>
                <tr><td>Description:<TEXTAREA COLS=50 name="description"></TEXTAREA></td></tr>
                <tr><td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Result     :<TEXTAREA COLS=50 name="result"></TEXTAREA></td></tr>
            <tr><td><input type="submit" name="submit" value="Add"/></td></tr>
            </table>
        </table>
    </form>
    <form method="post" action="delete_ticket.php">
    <input type="submit" name="delete" value="Delete"/>
    </form>
</table>

<?php
print "<table width=80% border=1>\n"; 
$cols = 0; 
while ($get_info = mysql_fetch_assoc($result)){ 
$id = $get_info->id;
if($cols == 0) 
{ 
  $cols = 1; 
  print "<tr>";
  print "<th>Select</th>";  
  foreach($get_info as $col => $value) 
  { 

    print "<th>$col</th>"; 
  } 
  print "<tr>\n"; 
} 
print "<tr>\n"; 
print "<td><input type='checkbox' name='selected[]' id='checkbox[]' value=$id></td>";
foreach ($get_info as $field) 
print "\t<td align='center'><font face=arial size=1/>$field</font></td>\n"; 
print "</tr>\n"; 
} 
print "</table>\n"; 
mysql_close();
?>

<!------------------------------------------------------------!>
</BODY>
</HTML>

Delete.php
<?php
$host       = "localhost";
$user       = "root";
$pass       = "";
$dbName     = "ticket_history";
$table_name = "ticket_history";

################ Connect to the Database and SELECT DATA ####################################
$conn = mysql_connect($host, $user, $pass) or die ("Unable to connect");
mysql_select_db($dbName);
$query = "SELECT Date,Ticket_Number,Description,Result FROM $table_name";
$result = mysql_query($query);
$count=mysql_num_rows($result);
#####################################


if($_POST['delete']) {
    $checkbox = $_POST['selected'];
    $countCheck = count($_POST['selected']);

    for($i=0;$i<$countCheck;$i++) {
        $del_id = $checkbox[$i];

    $sql = "DELETE FROM ticket_history WHERE Auto = $del_id";
    $result = mysql_query($sql);
    }
}
?>

I just want to be able to delete rows checked. How would I go about doing this effectively and efficiently?

Thank you in advance.

Upvotes: 0

Views: 3462

Answers (1)

Jacob
Jacob

Reputation: 8334

The simple answer to your question would be to use:

$sql = sprintf('DELETE FROM ticket_history WHERE Auto IN ()', 
    implode(',', $checkbox));

However as people will jump in and tell you, you are vulnerable to SQL injection. You should never trust user input. You are deleting using an ID, which I'm assuming must be an integer.

Using something like this will validate that:

$ids = array();
foreach($_POST['selected'] as $selected) {
    if (ctype_digit($selected)) {
        $ids[] = $selected;
    }
    else {
        // If one is invalid, I would assume nothing can be trusted
        // Depends how you want to handle the error.
        die('Invalid input');
    }
}

$sql = sprintf('DELETE FROM ticket_history WHERE Auto IN (%s)', 
    implode(',', $ids));

Other issues:

You seem to be using id's, but have not selected that field in your initial query.

$query = "SELECT Date,Ticket_Number,Description,Result FROM $table_name";

Then you reference:

$id = $get_info->id;

Check the HTML output is actually what you expect.

In your delete query, you are referencing the field Auto. Is that your ID field?

And lastly, there no checking if the user has permission to do so. If this is a public site anyone can delete from that table.

Example of using two submit buttons within one form:

<?php
if (isset($_POST['create'])) {
    echo "Create!";
}
elseif (isset($_POST['delete'])) {
    echo "Delete!";
}
?>
<html>
    <form method="post">
        <input type="submit" name="create" value="Create"/>
        <input type="submit" name="delete" value="Delete"/>
    </form>
</html>

Upvotes: 1

Related Questions