Deleting database entries through checkboxes

Question

I have been trying to delete certain rows from the database using a few checkboxes. So far I've managed to echo out the content of the MySQL table but deleting rows through the checkboxes doesn't seem to work.

<table class="ts">
        <tr>
            <th class="tg-031e" style='width:1px'>ID</th> 
            <th class="tg-031e">IP address</th>
            <th class="tg-031e">Date added</th>
            <th class="tg-031e">Reason</th>
        </tr>

        <?php include 'connect.php';

            $SQL = "SELECT `ID`, `IPaddress`, `DateAdded`, `Reason` FROM `banned`";
            $exec = mysql_query($SQL, $connection);

            while ($row = mysql_fetch_array($exec)){
                    echo "<tr class='tg-031h'>";
                    echo "<td class='tg-031e'><form method='post'><input type='checkbox' name='checkbox' value=" . $row['ID'] . "></form></td>";
                    echo "<td class='tg-031e'>" . $row['IPaddress'] . "</td>";
                    echo "<td class='tg-031e'>" . $row['DateAdded'] . "</td>";
                    echo "<td class='tg-031e'>" . $row['Reason'] . "</td>";
                    echo "</tr>"; 
            }

            echo "</table><form method='post'><input name='delete' type='submit' value='Delete'></form>";

            if (isset($_POST['delete']) && isset($_POST['checkbox'])) {
                foreach($_POST['checkbox'] as $id){
                    $id = (int)$id;
                    $delete = "DELETE FROM banned WHERE ID = $id"; 
                    mysql_query($delete);
                }
            }

            ?>

I don't get any result as the check does not pass but I don't know what's wrong with it. The query is correct though, so the issue must be with selecting the checkboxes and getting their ID.

Answer 1

Here, it's tested and working while using mysqli_ instead of mysql_

Replace with your own credentials.

A few things, your checkbox did need square brackets around the named element as I mentioned in my comment(s), i.e. name='checkbox[]' otherwise you would receive an invalid foreach argument error.

Sidenote: There stands to do a bit of formatting, but it works.

<table class="ts">
        <tr>
            <th class="tg-031e" style='width:1px'>ID</th> 
            <th class="tg-031e">IP address</th>
            <th class="tg-031e">Date added</th>
            <th class="tg-031e">Reason</th>
        </tr>

<?php

$DB_HOST = "xxx";
$DB_NAME = "xxx";
$DB_USER = "xxx";
$DB_PASS = "xxx";

$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($con->connect_errno > 0) {
  die('Connection failed [' . $con->connect_error . ']');
}

$SQL = "SELECT `ID`, `IPaddress`, `DateAdded`, `Reason` FROM `banned`";
$exec = mysqli_query($con,$SQL);

echo "<form method='post'>";

        while ($row = mysqli_fetch_array($exec)){
                echo "<tr class='tg-031h'>";
                echo "<td class='tg-031e'><input type='checkbox' name='checkbox[]' value='" . $row[ID] . "'></td>";

                echo "<td class='tg-031e'>" . $row['IPaddress'] . "</td>";
                echo "<td class='tg-031e'>" . $row['DateAdded'] . "</td>";
                echo "<td class='tg-031e'>" . $row['Reason'] . "</td>";
        }

echo "</tr></table>"; 

        echo "<input name='delete' type='submit' value='Delete'></form>";

        if (isset($_POST['delete']) && isset($_POST['checkbox'])) {
            foreach($_POST['checkbox'] as $id){
                $id = (int)$id;

                $delete = "DELETE FROM banned WHERE ID = $id"; 
                mysqli_query($con,$delete);
            }
        }

?>

---

Do use mysqli_* with prepared statements, or PDO with prepared statements for this.

---

Edit: mysql_ version

<table class="ts">
        <tr>
            <th class="tg-031e" style='width:1px'>ID</th> 
            <th class="tg-031e">IP address</th>
            <th class="tg-031e">Date added</th>
            <th class="tg-031e">Reason</th>
        </tr>

<?php

include 'connect.php';

$SQL = "SELECT `ID`, `IPaddress`, `DateAdded`, `Reason` FROM `banned`";
$exec = mysql_query($SQL, $connection);

echo "<form method='post'>";

        while ($row = mysql_fetch_array($exec)){
                echo "<tr class='tg-031h'>";
                echo "<td class='tg-031e'><input type='checkbox' name='checkbox[]' value='" . $row[ID] . "'></td>";

                echo "<td class='tg-031e'>" . $row['IPaddress'] . "</td>";
                echo "<td class='tg-031e'>" . $row['DateAdded'] . "</td>";
                echo "<td class='tg-031e'>" . $row['Reason'] . "</td>";
        }

echo "</tr></table>"; 

        echo "<input name='delete' type='submit' value='Delete'></form>";

        if (isset($_POST['delete']) && isset($_POST['checkbox'])) {
            foreach($_POST['checkbox'] as $id){
                $id = (int)$id;

                $delete = "DELETE FROM banned WHERE ID = $id"; 
                mysql_query($delete);
            }
        }

?>

Answer 2

You should do something similar to this (semi pseudo):

<form method="post">

    /begin loop/

    echo '<input type="checkbox" name="row[' . $row['ID'] . ']" />';

    /end loop/

    <input type="submit" />

</form>

Then try to put this on the processor page, on top of it:

if ( !empty( $_POST ) )
{
    echo '<pre>' . print_r( $_POST, true ) . '</pre>';
    die( 'see? :-)' );
}

. . . .

Okay here's your code, edited:

<?php if ( !empty( $_POST ) ) { echo '<pre>'.print_r( $_POST, true ).'</pre>'; die( 'See?' ); ?>
<form method="post">
<table class="ts">
    <tr>
        <th class="tg-031e" style='width:1px'>ID</th> 
        <th class="tg-031e">IP address</th>
        <th class="tg-031e">Date added</th>
        <th class="tg-031e">Reason</th>
    </tr>

    <?php include 'connect.php';

        $SQL = "SELECT `ID`, `IPaddress`, `DateAdded`, `Reason` FROM `banned`";
        $exec = mysql_query($SQL, $connection);

        while ($row = mysql_fetch_array($exec)){
                echo "<tr class='tg-031h'>";
                echo "<td class='tg-031e'><input type='checkbox' name='row[$row['ID']]'></td>";
                echo "<td class='tg-031e'>" . $row['IPaddress'] . "</td>";
                echo "<td class='tg-031e'>" . $row['DateAdded'] . "</td>";
                echo "<td class='tg-031e'>" . $row['Reason'] . "</td>";
                echo "</tr>"; 
        }

        echo "</table><input name='delete' type='submit' value='Delete'>";

        if (isset($_POST['delete']) && isset($_POST['checkbox'])) {
            foreach($_POST['checkbox'] as $id){
                $id = (int)$id;
                $delete = "DELETE FROM banned WHERE ID = $id"; 
                mysql_query($delete);
            }
        }

        ?>

</form>

The Deletion query hasn't been fixed yet. Try to fix it yourself :-)