Delete Multiple *selected* Fields/columns/rows

[White_Lily]

Hi, i was just wondering how i go about deleteing multiple fields/columns/rows using checkboxes...

 

 

say i had a list of names, i wondered to just click a checkbox and then press a button named "Delete" at the bottom of the page that would then *obviously* delete the selected rows

 

how would i do this?

[Christian F.]

Use implode () to create a comma-delimited list, and then use MySQL's IN() to select them.

[White_Lily]

guna have to read up on those functions, never used them - thanks though

[Christian F.]

You're welcome.

[The Little Guy]

It would look something like this:

 

<input type="checkbox" name="imAnArray[]" value="1" />
<input type="checkbox" name="imAnArray[]" value="2" />
<input type="checkbox" name="imAnArray[]" value="3" />

 

<?php
function sanitize(&$value){
   $value = (int)$value;
}

$values = implode(",", array_walk($_POST["imAnArray"], "sanitize");
mysql_query("delete from my_table where my_table_id in($values)");
?>

Edited October 2, 2012 by The Little Guy

[Psycho]

<?php
function sanitize(&$value){
$value = (int)$value;
}

$values = implode(",", array_walk($_POST["imAnArray"], "sanitize");
mysql_query("delete from my_table where my_table_id in($values)");
?>

 

@The Little Guy,

 

That won't work. array_walk() applies the function results to the array elements - i.e. it modifies the variable. It returns either true or false. So, the implode above is trying to implode a true/false as returned from array_walk() - not the actual array. Instead, array_map() would be a more appropriate solution since it returns an array with the modified values. I was also going to suggest just using intval() as the callback function, but by using a custom function it makes it easier to modify as needed later on. But, I would also add a process to remove empty/false values from the resulting array

 

function parseIntIDs($idsArray)
{
   //Force values to INTs
   $idsArray = array_map('intval', $idsArray);
   //Filter out false/empty values
   $idsArray = array_filter($idsArray);
   return $idsArray;
}

$valuesList = implode(', ', parseIntIDs($_POST["imAnArray"]));
$query = "DELETE FROM my_table WHERE record_id in ($valuesList)";
mysql_query($query);

Edited October 2, 2012 by Psycho

[White_Lily]

Thank you - I will take a look at your suggestions. If there's anything else that may be useful for me to know please do say so.

 

More info on what I'm achieving:

 

i have created a site where people can create albums and put images into them (as you do).

 

But I want people to be able to delete images that they no longer want, and I thought maybe the best way to go about this would be to just have a check box next to each image and a delete button at the end of the page so that they can click on the check box of any images they wish to delete, then click delete and a small deletion script will run the process and delete any images that the user had checked, if there's is a simpler way of doing please do let me know.

[The Little Guy]

@The Little Guy,

 

That won't work. array_walk() applies the function results to the array elements - i.e. it modifies the variable. It returns either true or false. So, the implode above is trying to implode a true/false as returned from array_walk() - not the actual array. Instead, array_map() would be a more appropriate solution since it returns an array with the modified values. I was also going to suggest just using intval() as the callback function, but by using a custom function it makes it easier to modify as needed later on. But, I would also add a process to remove empty/false values from the resulting array

 

Ah, yes you are correct, so I modified so it would work.

 

<?php
function sanitize($array){
   array_walk($array, function(&$val){
       $val = (int)$val;
   });
   return $array;
}

$userid = (int)$_SESSION["userid"];
$values = implode(",", sanitize($_POST["imAnArray"]));
mysql_query("delete from my_table where my_table_id in($values) where userid = $userid");
?>

[Psycho]

@The Little Guy,

 

That really is overcomplicated don't you think? All you are doing it creating a custom function that does the exact same thing as intval().I provided what is arguable a more full-featured solution, but if all you want to do is force the values to an integer then this would be much simpler:

$values = implode(",", array_map('intval', $_POST["imAnArray"]));

 

No need for the sanitize function, no need to create the dynamic function, use the bult

 

@White_Lily,

 

Yes, this would be the easiest approach. Just create your checkboxes as an array using the record ID as the value. Then run a single delete query as shown. Just be sure to provide any validation needed. For example if these are associated with a user and users should only be able to delete their own images then make sure the user ID is for the person logged in (in fact, you would want to have the user id as a session variable rather than have it passe din the form data if that is the case)

[jazzman1]

@White_Lily, don't forget to use post method, when you are trying to delete or update some data in your DB.

[Psycho]

@White_Lily, don't forget to use post method, when you are trying to delete or update some data in your DB.

 

Why do you say that? POST is no safer/secure than GET if that is what you were insinuating. It is a trivial process to modify POST data outside of the form.

[The Little Guy]

POST was initially meant for: Saving/Updating/Deleting data and GET was initially meant for retrieving data. They are not required for that use though. There is no security difference between the two, as they are both passed as a key/value string split by "=" and each group is split by "&".

Edited October 2, 2012 by The Little Guy

[jazzman1]

Well, in reality you can use either method and I've seen scripts using the GET method to delete database information and POST method just to retrieve information from the server.

I think (and not only me) that the GET method is suited to request that don't affect the state of database or files on the server. In other words use it when you want to get information.

The POST is suited for sending data that will change information on the server.

That's why I said, he/she to use POST data method.

Edited October 2, 2012 by jazzman1

[White_Lily]

Just quickly again, would this be a similar process for deleting multiple albums to? (the images associated with this album should also be deleted along with the album).

[The Little Guy]

Just quickly again, would this be a similar process for deleting multiple albums to? (the images associated with this album should also be deleted along with the album).

 

Yup!

[Christian F.]

The process will be the same regardless of what you delete, as long as we're talking about multiple rows from a single table.

 

As for deleting the associated pictures, that's really something that should be handled by the database itself via the foreign key relation (ON DELETE CASCADE).