Multiple Delete Data Using PHP/MySQL and PDO Query

Submitted by argie on Wednesday, December 4, 2013 - 18:16.
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");
  14. ?>
That's it, you've been successfully created a multiple delete data from database table using PHP/MySQL and PDO query.
Tags
multiple delete data
PHP/MySQL
multiple delete data using pdo query

Add new comment

About text formats
  • 786 views