Multiple Delete Data Using PHP/MySQL and PDO Query

[mikegreensky]

This tutorial will teach you on how to create an application in PHP/MySQL using PDO query that delete multiple data using checkbox as selector. I used checkbox as a selector in every row that u want to delete from database table. To understand more about this tutorial, fallow the steps bellow.

Creating Our Database

First we are going to create our database which stores our data.
To create a database:

1. Open phpmyadmin
2. Then create database and name it as "pdo_ret".
3. After creating a database name, click the SQL and paste the following code.

1. CREATE TABLE IF
   
   NOT EXISTS `members` (
   
2. `id` int(
   
   11
   
   )
   
   NOT NULL
   
   AUTO_INCREMENT,
   
3. `fname` varchar(
   
   100
   
   )
   
   NOT NULL
   
   ,
   
4. `lname` varchar(
   
   100
   
   )
   
   NOT NULL
   
   ,
   
5. `age` int(
   
   5
   
   )
   
   NOT NULL
   
   ,
   
6. PRIMARY KEY
   
   (
   
   `id`)
   
7. )
   
   ENGINE=
   
   InnoDB DEFAULT
   
   CHARSET=
   
   latin1 AUTO_INCREMENT=
   
   1
   
   ;
   

Creating our Database Connection

Next step is to create a database connection and save it as "connect.php". In this Step, we will write our connection script in PDO format.

1. <?php
   
2. /* Database config */
   
3. $db_host
   
   =
   
   'localhost'
   
   ;
   
4. $db_user
   
   =
   
   'root'
   
   ;
   
5. $db_pass
   
   =
   
   ''
   
   ;
   
6. $db_database
   
   =
   
   'pdo_ret'
   
   ;
   
7. 
   
8. /* End config */
   
9. 
   
10. $db
    
    =
    
    new
    
    PDO(
    
    'mysql:host='
    
    .
    
    $db_host
    
    .
    
    ';dbname='
    
    .
    
    $db_database
    
    ,
    
    $db_user
    
    ,
    
    $db_pass
    
    )
    
    ;
    
11. $db
    
    ->
    
    setAttribute
    
    (
    
    PDO::
    
    ATTR_ERRMODE
    
    ,
    
    PDO::
    
    ERRMODE_EXCEPTION
    
    )
    
    ;
    
12. ?>
    

Creting our Display Page

In this steps we will write our script that retrieve our data from database table and display the data with selectbox as a selector. Save this page as "index.php".

1. <form action="delete.php" method="post">
   
2. <table border="1" cellspacing="0" cellpadding="2" >
   
3. <thead>
   
4. <tr>
   
5. <th> &nbsp; </th>
   
6. <th> First Name </th>
   
7. <th> Last Name </th>
   
8. <th> Age </th>
   
9. </tr>
   
10. </thead>
    
11. <tbody>
    
12. <?php
    
13. include
    
    (
    
    'connect.php'
    
    )
    
    ;
    
14. $result
    
    =
    
    $db
    
    ->
    
    prepare
    
    (
    
    "SELECT * FROM members ORDER BY id DESC"
    
    )
    
    ;
    
15. $result
    
    ->
    
    execute
    
    (
    
    )
    
    ;
    
16. for
    
    (
    
    $i
    
    =
    
    0
    
    ;
    
    $row
    
    =
    
    $result
    
    ->
    
    fetch
    
    (
    
    )
    
    ;
    
    $i
    
    ++
    
    )
    
    {
    
17. ?>
    
18. <tr class="record">
    
19. <td><input name="selector[]" type="checkbox" value="<?php
    
    echo
    
    $row
    
    [
    
    'id'
    
    ]
    
    ;
    
    ?>
    
    "></td>
    
20. <td><?php
    
    echo
    
    $row
    
    [
    
    'fname'
    
    ]
    
    ;
    
    ?>
    
    </td>
    
21. <td><?php
    
    echo
    
    $row
    
    [
    
    'lname'
    
    ]
    
    ;
    
    ?>
    
    </td>
    
22. <td><?php
    
    echo
    
    $row
    
    [
    
    'age'
    
    ]
    
    ;
    
    ?>
    
    </td>
    
23. </tr>
    
24. <?php
    
25. }
    
26. ?>
    
27. </tbody>
    
28. </table>
    
29. <input type="submit" value="delete" />
    
30. </form>
    

Writing Our Multiple Delete Script

The code bellow will delete multiple data from database table. Copy the code bellow and save it as "delete.php".

1. <?php
   
2. include
   
   (
   
   'connect.php'
   
   )
   
   ;
   
3. 
   
4. $edittable
   
   =
   
   $_POST
   
   [
   
   'selector'
   
   ]
   
   ;
   
5. $N
   
   =
   
   count
   
   (
   
   $edittable
   
   )
   
   ;
   
6. for
   
   (
   
   $i
   
   =
   
   0
   
   ;
   
   $i
   
   <
   
   $N
   
   ;
   
   $i
   
   ++
   
   )
   
7. {
   
8. $result
   
   =
   
   $db
   
   ->
   
   prepare
   
   (
   
   "DELETE FROM members WHERE id= :memid"
   
   )
   
   ;
   
9. $result
   
   ->
   
   bindParam
   
   (
   
   ':memid'
   
   ,
   
   $edittable
   
   [
   
   $i
   
   ]
   
   )
   
   ;
   
10. $result
    
    ->
    
    execute
    
    (
    
    )
    
    ;
    
11. }
    
12. header
    
    (
    
    "location: index.php"
    
    )
    
    ;
    
13. mysql_close
    
    (
    
    $con
    
    )
    
    ;
    
14. ?>
    

That's it, you've been successfully created a multiple delete data from database table using PHP/MySQL and PDO query.

Download