Delete record from table using Checkbox

Question

Possible Duplicate:
Deleing Multiple Rows Using Checkboxes, PHP and MySQL

I wonder whether someone may be able to help me please.

I'm trying to put together a script which creates a form which gives the user the ability to delete a record via the selection of a checkbox and then pressing a 'submit' button.

From reading through many articles, I've put together the following script which is the section of code that builds the table, checkboxes and submit button.

 <?php

$query = "SELECT  l.*, COUNT(f.locationid) totalfinds FROM detectinglocations l LEFT JOIN finds f ON f.locationid = l.locationid WHERE l.userid = '$idnum' GROUP BY l.locationname";

$result=mysql_query($query);

$count=mysql_num_rows($result);
?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="del" id="del" action="deletelocation.php" method="post">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF">&nbsp;</td>
<td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['locationid']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['locationid']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['locationname']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['returnedaddress']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['totalfinds']; ?></td>
</tr>

<?php
}
?>

<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input type="submit" value="Delete" /></td>
</tr>
</table>
</form>
</td>
</tr>
</table>

The code below, then deals with the deletion of the record.

<?php

$del_id = $_POST['checkbox']; 

for($i=0;$i<$count;$i++){
$del_id = $checkbox[$i];
$sql = "DELETE FROM $detectinglocations WHERE locationid='$del_id'";
$result = mysql_query($sql);
}


?>

The correct information is retrieved and shown in the form table, but the problem I'm having is that I'm unable to get the deletion of the record to work. I've run this through JavaScript Console, but unfortunately I don't receive an error message which may help me to solve the problem.

I just wondered whether someone could possibly take a look at this please and let me know where I'm going wrong.

Where do you define `$detectinglocations`? What do you get if you echo out `$sql` and run it right in the database? — andrewsi, Sep 23 '12 at 15:09
Too many questions already exist about that: http://stackoverflow.com/questions/10145717/deleing-multiple-rows-using-checkboxes-php-and-mysql?rq=1 http://stackoverflow.com/questions/9239648/delete-selected-data-in-a-checkbox?rq=1 http://stackoverflow.com/questions/8870741/deleting-a-row-from-a-database-table-using-checkbox?rq=1 http://stackoverflow.com/questions/1296149/delete-multiple-item-by-checkbox?rq=1 http://stackoverflow.com/questions/8529005/php-multiple-checkbox-delete?rq=1 http://stackoverflow.com/questions/8522070/php-checkbox-multiple-delete?rq=1 — Jocelyn, Sep 23 '12 at 15:14
Hi @andrewsi, thank your for replying to my post so quickly. 'detectinglocations is my table name, and from the articles I've read I didn't see anywhere that defined the table. I am however relatively new to this, so maybe this is the problem. If I take out the 'sql' line. There is no change the initial error. Kind regards — IRHM, Sep 23 '12 at 15:14
@IRHM - `detectinglocations` and `$detectinglocations` are not the same. Try removing the `$` from your DELETE query and try again? — andrewsi, Sep 23 '12 at 15:16
Hi thank you for your reply. I've managed to get this to work using @GBD solution. All the best and kind regards — IRHM, Sep 23 '12 at 15:18
Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the [*red box*](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — tereško, Sep 23 '12 at 15:19
Hi @tereško, thank you very much for this. I am aware of the deprecation. I've only just started to get my head around MySQL, so I'm getting the code to work with this. I will then start to learn about the MySQLI and make the changes to my code. Kind regards — IRHM, Sep 23 '12 at 15:22
@IRHM , I would actually recommend PDO, but the choice of API is not so important. That IS important for you is to understand *prepared statements*. The "why" and "how". If used properly, they would guard your code against SQL injections. At least the 1st order SQL injection. The prevention of [2nd order injections](http://st-curriculum.oracle.com/tutorial/SQLInjection/html/lesson1/les01_tm_attacks2.htm) relays more on your distrust of data that comes from database. — tereško, Sep 23 '12 at 15:36
Hi @tereško, thank you very much for the links and advice. I'll be looking at these. Kind regards — IRHM, Sep 24 '12 at 13:56

score 0 · Accepted Answer · answered Sep 23 '12 at 15:13

0

Change your PHP code as below

$del_id = $_POST['checkbox']; 
$detectinglocations = 'your database table name';

foreach($del_id as $value){
   $sql = "DELETE FROM ".$detectinglocations." WHERE id='".$value."'";
   $result = mysql_query($sql);
}

answered Sep 23 '12 at 15:13

GBD

15,847
2
46
50

Hi GBD, thank you very much for replying to my post and for the solution. This did the trick. Once again, many thanks and kind regards. – IRHM Sep 23 '12 at 15:17
Hi @GBD, it certainly wasn't me. I've cancelled this out. Perhaps it was the same person who down voted my question. When the time delay allows I'll accept the answer. Kind regards – IRHM Sep 23 '12 at 15:20

Delete record from table using Checkbox

1 Answers1