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.

Unchecked checkboxes are not considered "successful controls" and are thus not submitted to the server. http://www.w3.org/TR/html401/interact/forms.html#h-17.13.2 You'll need to pass something else. — scunliffe, Jul 27 '14 at 15:34
Does anything show up in your HTML with `value=" . $row['ID'] . "`? — Funk Forty Niner, Jul 27 '14 at 15:34
is that separate `
` for each checkboxes? And the submit button is also in a separate form? — rlatief, Jul 27 '14 at 15:35
but if you click submit, form elements from the other forms are not going through... — rlatief, Jul 27 '14 at 15:36
Well I tried using one form that surrounds the whole PHP block but it does not submit anything like that. — Jessie Stalk, Jul 27 '14 at 15:37
Do `echo $delete = "DELETE FROM banned WHERE ID = $id";` see what's being passed through. Plus, are you trying to delete more than one at a time? If so, do `name='checkbox[]'` - either way if it's one or more, you may want to use that instead. — Funk Forty Niner, Jul 27 '14 at 15:39
As I said, this check doesn't even pass. I probably take the checkbox values the wrong way. — Jessie Stalk, Jul 27 '14 at 15:40
Doing `name='checkbox[]'` didn't work? See this Q&A http://stackoverflow.com/q/14475096/ and base yourself on that, which is what I used in the past and it worked for me. It's `mysqli_` mind you. — Funk Forty Niner, Jul 27 '14 at 15:42
Even if I try to delete a single entry, it doesn't submit the form. — Jessie Stalk, Jul 27 '14 at 15:44
Then move your closing brace in your `while` loop right after `mysql_query($delete);` — Funk Forty Niner, Jul 27 '14 at 15:46
@JessieStalk if you submit a form, only elements `inside that form` gets submitted. — rlatief, Jul 27 '14 at 15:47
I've tried adding the form before the checkboxes and after the submit button but in that way it doesn't submit anything. — Jessie Stalk, Jul 27 '14 at 15:50
Any which way, you're using `foreach($_POST['checkbox']` and this requires your checkbox to be treated as an array and your `name='checkbox'` isn't being treated as an array because of missing square brackets as I've already shown you. Whether you're checking one or more. I suggest you look at the link I've given you and base yourself on that, plus using your delete method inside another page. `rlatief` already said it about the form being submitted the way you're doing it now. — Funk Forty Niner, Jul 27 '14 at 15:53

score 1 · Accepted Answer · answered Jul 27 '14 at 16:39

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);
            }
        }

?>

@JessieStalk `mysqli_` isn't available for you in that version? — Funk Forty Niner, Jul 27 '14 at 16:40
@JessieStalk I've modified it to be as you had it, using `mysql_` - See under my **Edit** — Funk Forty Niner, Jul 27 '14 at 16:44

rlatief · Answer 2 · 2014-07-27T15:55:12.193

0

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 :-)

edited Jul 27 '14 at 15:55

answered Jul 27 '14 at 15:41

rlatief

715
5
14

Sorry I don't see how this can help me. Why should I display any $_POST content? – Jessie Stalk Jul 27 '14 at 15:56
To see the posted data that'll help you build the query. Remove it after you understand it. – rlatief Jul 27 '14 at 15:57
The basic mistake is, you have 1 form for every checkboxes. And also a separate form for the button. If you click the button, only the button element that'll get POSTed. You cannot submit more than 1 form at a time (without using some out of topic workaround). – rlatief Jul 27 '14 at 15:58
That's why I asked on stackoverflow. It is by far the only thing that hinders the code but I can't figure how to or where to define the form first and where to close it. – Jessie Stalk Jul 27 '14 at 16:02
Yes, you need to put all the checkboxes and the submit button into a single form. The code above should work. If it doesn't then there's a typo somewhere (that's why I prefer to explain by using pseudo as in the first two code blocks). – rlatief Jul 27 '14 at 16:06
`I can't figure how to or where to define the form first and where to close it` – Jessie Stalk Jul 27 '14 at 16:08
Define `
` before the table, and `
` after it. Putting `form` tags inside the table, between the `tr`, will work, but it'll make your HTML invalid. Putting `
` and `
` inside the same `` is valid HTML, but you won't be able to achieve what you want to achieve. – rlatief Jul 27 '14 at 16:11
Ok, let's leave it at that. – Jessie Stalk Jul 27 '14 at 16:14

Deleting database entries through checkboxes

2 Answers2

Linked