Delete rows from MySQL based on Checkboxes from PHP

[Da9L]

Hey everyone ! First post here and i hope im doing this right !

 

I've begun to learn PHP and MySQL .. Its going fine untill further but im currently stuck .. 

 

What i need is that a user is able to create "tasks".. Those tasks will be stored in a MySQL database table and shown to this user with a checkbox next to them. I've managed to create this, but what i want now is a way to delete those that has been checked after the user clicks a button. I thought this was rather simple but i simply cant figure out how to do it..

 

Easiest way would be to show you my code.. So here it is:

 

<?php
                
$opgaver_fra_db = mysql_query("SELECT *  FROM `opgaver`");

echo '<form method="post" action="opgave_delete.php">';

echo '<input type="submit" value="Slet opgaver" name="delete"/><p>';

while ($chkbx_values = mysql_fetch_array($opgaver_fra_db)) {
    echo '<input type="checkbox" value="'.$chkbx_values['opgave'].'" name="checkbox"> '.$chkbx_values['opgave'].'</br>';
}

echo '</form>';
?>

The above code seems to work great.. A list is shown with checkboxes form what has been created.

 

Heres some code on how to create a new task:

 

$nyopgave = $_POST['opgave_navn'];

include 'includes/connection.php';

    $tjek_query_opgaver_exists = mysql_query("SELECT * FROM opgaver WHERE opgave = '$nyopgave'") or die(mysql_error());
    $opgave_exists = mysql_fetch_array($tjek_query_opgaver_exists);
    if($opgave_exists['opgave'] > 0) {
                echo "! - Opgaven er allerede oprettet i systemet!";
    } else {
            
            $opretopgave = "INSERT INTO opgaver (`opgave`) VALUES ('$nyopgave')";
            $opretopgave_query = mysql_query($opretopgave);
        
            if($opretopgave_query) {
                 echo "* - Opgaven blev oprettet korrekt!";
                 include "includes/redirection_admin.php";
           } else {
                 "! - Opgaven blev IKKE oprettet korrekt!";
           }
         
        

        

    } 

 

This also works great and it is shown in the list that is then loaded when redirected back to the admin page.. The code that loads the list is the code shown first.

 

But heres what i cant get to work .. I need to be able to delete multiple rows when those checkboxes have been selected and a button is clicked.. But i dont know how to do this .. This is the code i've tried so far..:

 

<?php
if(isset($_POST['delete'])){//check to see if the delete button has been pressed
   
    include "includes/connection.php";
   
    if(isset($_POST['checkbox'])){ //check to see if any boxes have been checked 
        $num = 0;//used to count the number of rows that were deleted
        $box = $_POST['checkbox'];
        while (list ($key,$val) = @each ($box)) { //loop through all the checkboxes
              $num++;
              $sqldel="DELETE FROM logins WHERE id='$val'";//delete any that match id
              $resdel=mysql_query($sqldel);//send the query to mysql
        }
        //print the logs that were deleted
        echo"$num record(s) have been deleted.";
   }
   else{//no boxes checked
   echo "No records selected.";
   }
}
?>

 

I dont think this is how you would do it as it doesnt work at all ..

 

Any suggestions ?

[Barand]

 

<?php
    // assumes the checkbox values are numeric id values
    
    $idList = join (',', array_map('intval', $_POST['checkbox']));
    
    $sql = "DELETE FROM logins WHERE id IN ($idlist)";
    mysql_query($sql);
?>

And the form would look something like this (note the naming of the checkboxes) and the values should be the row id values

<input type="checkbox" name="checkbox[]" value='1'> record 1
<input type="checkbox" name="checkbox[]" value='2'> record 2
etc

[Da9L]

 

<?php
    // assumes the checkbox values are numeric id values
    
    $idList = join (',', array_map('intval', $_POST['checkbox']));
    
    $sql = "DELETE FROM logins WHERE id IN ($idlist)";
    mysql_query($sql);
?>

 

And the form would look something like this (note the naming of the checkboxes) and the values should be the row id values

 

<input type="checkbox" name="checkbox[]" value='1'> record 1
<input type="checkbox" name="checkbox[]" value='2'> record 2
etc

 

but the problem is that i use an array to list the checkboxes too .. and from the looks of it it looks like you list the checkboxes as fixed in the above..

 

as you can see in the first code  box i listed thats where the checkboxes are generated on the page.. how would i give the individual id's ?

[Barand]

It is an example of how to do it. I am not suggesting you paste it into your code. The important things are the naming and values, so the values (ids) are posted as an array.

[Da9L]

It is an example of how to do it. I am not suggesting you paste it into your code. The important things are the naming and values, so the values (ids) are posted as an array.

 

So i should name the checkboxes after their ID in MySQL which would look like this 

 

 

$opgaver_fra_db = mysql_query("SELECT *  FROM `opgaver`");

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

echo '<input type="submit" value="Slet opgaver" name="delete"/><p>';

while ($chkbx_values = mysql_fetch_array($opgaver_fra_db)) {
    echo '<input type="checkbox" value="'.$chkbx_values['id'].'" name="opgaver_chkbx"> '.$chkbx_values['opgave'].'</br>';
    
}

 

Should i also use the variable in the "name" field of the checkbox ?

 

If i try the above it seems that the boxes get each their own id, but how do i go about deleting the checked checkboxes then?

[Barand]

You missed the important part of the name, the [] at the end

 

while ($chkbx_values = mysql_fetch_array($opgaver_fra_db)) {
    echo '<input type="checkbox" value="'.$chkbx_values['id'].'" name="opgaver_chkbx[]"> '.$chkbx_values['opgave'].'</br>';
    
}

[Da9L]

 

You missed the important part of the name, the [] at the end

 

while ($chkbx_values = mysql_fetch_array($opgaver_fra_db)) {
    echo '<input type="checkbox" value="'.$chkbx_values['id'].'" name="opgaver_chkbx[]"> '.$chkbx_values['opgave'].'</br>';
    
}

Yes i figured that out sorry.. 

 

But im still not able to figure out how to make it working.. 

[Barand]

Use the code I gave you only using your checkbox name

 

Spoonfed version

$idList = join (',', array_map('intval', $_POST['opgaver_chkbx']));
    
    $sql = "DELETE FROM logins WHERE id IN ($idlist)";
    mysql_query($sql);

 

The query will look like

 

DELETE FROM logins WHERE id IN (x,y,z)

 

where x,y,z etc are the ids you selected in the checkboxes.

 

So you delete the records in a single query instead of looping through values doing many separate delete queries

[Da9L]

Use the code I gave you only using your checkbox name

 

Spoonfed version

$idList = join (',', array_map('intval', $_POST['opgaver_chkbx']));
    
    $sql = "DELETE FROM logins WHERE id IN ($idlist)";
    mysql_query($sql);

 

The query will look like

 

DELETE FROM logins WHERE id IN (x,y,z)

 

where x,y,z etc are the ids you selected in the checkboxes.

 

So you delete the records in a single query instead of looping through values doing many separate delete queries

Thanks a bunch ! This seems to work.. 

 

Im sorry you had to give me the "spoonfed" version . Im new to this and have a lot to learn! I just still dont understand why it works, but i can look that up my self in the php manual for the syntax The line i dont get is this

 

$idList = join (',', array_map('intval', $_POST['opgaver_chkbx']));

 

But i will look that up .. thanks a million !

[Barand]

 

$idList = join (',', array_map('intval', $_POST['opgaver_chkbx']));

 

the join() converts the array of ids into a string of ids separated by commas.

 

The array_map() applies the intval() function to each of the ids to ensure they are numbers to protect aginst "SQL injection" (Google it)