Delete Data Using PHP/MySQL with PDO Query

Submitted by argie on Wednesday, November 27, 2013 - 12:09.

This tutorial will teach you on how to delete data from database table. I used PHP/MySQL with PDO query in this delete system. To start 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.

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(100) NOT NULL,
  `lname` varchar(100) NOT NULL,
  `age` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) 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.

<?php
/* Database config */
$db_host                = 'localhost';
$db_user                = 'root';
$db_pass                = '';
$db_database    = 'pdo_ret'; 
 
/* End config */
 
$db = new PDO('mysql:host='.$db_host.';dbname='.$db_database, $db_user, $db_pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
?>

Creating Our Display With Delete Action

The code bellow will retrieve data from our database table and provide an delete action. Copy the code bellow and save it as "index.php".

<table border="1" cellspacing="0" cellpadding="2" >
<thead>
        <tr>
                <th> First Name </th>
                <th> Last Name </th>
                <th> Age </th>
                <th> Action </th>
        </tr>
</thead>
<tbody>
        <?php
                include('connect.php');
                $result = $db->prepare("SELECT * FROM members ORDER BY id DESC");
                $result->execute();
                for($i=0; $row = $result->fetch(); $i++){
        ?>
        <tr class="record">
                <td><?php echo $row['fname']; ?></td>
                <td><?php echo $row['lname']; ?></td>
                <td><?php echo $row['age']; ?></td>
                <td><a href="delete.php?id=<?php echo $row['id']; ?>"> delete </a></td>
        </tr>
        <?php
                }
        ?>
</tbody>
</table>

Writing Our Delete Script

This script will Delete the data in our database table. Copy the code bellow and save it as "delete.php".

<?php
        include('connect.php');
        $id=$_GET['id'];
        $result = $db->prepare("DELETE FROM members WHERE id= :memid");
        $result->bindParam(':memid', $id);
        $result->execute();
        header("location: index.php");
?>

That's it you've been successfully created your delete script using PHP/MySQL with PDO query.

Add new comment

682 views