PHP Deleting Data In MySQL

Submitted by alpha_luna on Thursday, May 12, 2016 - 16:08.

PHP Deleting Data In MySQL

We already did in the tutorial for PHP Inserting Data To MySQL and PHP Select Data In MySQL. So, I decided to create a follow-up tutorial for PHP Deleting Data In MySQL. We're gonna use Delete Statement to delete data from our database table. "tbl_registration" Example Database Table. This is the data. Data

Deleting Data In MySQL Using MySQLi and PDO

Deleting the data tbl_registration_id = 2 in our "tbl_registration" table.

Deleting Record Using MySQLi (Object-Oriented)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "add_query_pdo";
 
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
 
// sql to delete a record
$sql = "DELETE FROM tbl_registration WHERE tbl_registration_id = 2";
 
if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}
 
$conn->close();
?>

Deleting Record Using MySQLi (Procedural)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "add_query_pdo";
 
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
 
// sql to delete a record
$sql = "DELETE FROM tbl_registration WHERE tbl_registration_id = 2";
 
if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}
 
mysqli_close($conn);
?>

Deleting Record Using PDO (PHP Data Objects)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "add_query_pdo";
 
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    // sql to delete a record
    $sql = "DELETE FROM tbl_registration WHERE tbl_registration_id = 2";
 
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Record deleted successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }
 
$conn = null;
?>

In this tutorial, this is my simple source code and easy to understand on how to delete data in MySQL.

This is the Data Table and has a "Delete Record" button.

<table border="1" cellspacing="5" cellpadding="5" width="100%">
        <thead>
                <tr>
                        <th>No.</th>
                        <th>First Name</th>
                        <th>Middle Name</th>
                        <th>Last Name</th>
                        <th>Email</th>
                        <th>Contact Number</th>
                        <th>Action</th>
                </tr>
        </thead>
        <tbody>
        <?php
                require_once('connection.php');
                $result = $conn->prepare("SELECT * FROM tbl_registration ORDER BY tbl_registration_id ASC");
                $result->execute();
                for($i=0; $row = $result->fetch(); $i++){
                $id=$row['tbl_registration_id'];
        ?>
                <tr>
                        <td><label><?php echo $row['tbl_registration_id']; ?></label></td>
                        <td><label><?php echo $row['first_name']; ?></label></td>
                        <td><label><?php echo $row['middle_name']; ?></label></td>
                        <td><label><?php echo $row['last_name']; ?></label></td>
                        <td><label><?php echo $row['email']; ?></label></td>
                        <td><label><?php echo $row['contact_number']; ?></label></td>
                        <td>
                        <a href="delete.php<?php echo '?tbl_registration_id='.$id; ?>" class="btn btn-danger">
                                <button class="btn_delete">
                                        Delete Record
                                </button>
                        </a>
                        </td>
                </tr>
                <?php } ?>
        </tbody>
</table>

And, this is our PHP Delete Query to deleting data in our table.

<?php
require_once('connection.php');
 
$get_id=$_GET['tbl_registration_id'];
 
// sql to delete a record
$sql = "Delete from tbl_registration where tbl_registration_id = '$get_id'";
 
// use exec() because no results are returned
$conn->exec($sql);
echo "<script>alert('Successfully Deleted!'); window.location='index.php'</script>";
?>

And, this is the output after deleting data in the table. The tbl_registration_id = 2 was deleted. Result

Share us your thoughts and comments below. Thank you so much for dropping by and reading this tutorial post. For more updates, don’t hesitate and feel free to visit this website more often and please share this with your friends or email me at [email protected]. Practice Coding. Thank you very much.