Delete Data From MySQL Using PHP


To delete data from MySQL the DELETE statement is used. We can delete data from specific column or all column of a table.

To delete selected column data from database the SQL query is

DELETE FROM table_name WHERE some_column=some_value;

To delete all the column data from a table the SQL query is

DELETE FROM table_name;

or

DELETE * FROM table_name;

In the below example we delete the employee data from MySQL database.

In this example we used 2 file for retrieve data

database.php

<?php
$url='127.0.0.1:3306';
$username='root';
$password='';
$conn=mysqli_connect($url,$username,$password,"crud");
if(!$conn){
 die('Could not Connect My Sql:' .mysql_error());
}
?>

delete.php

<?php
include_once 'database.php';
$result = mysqli_query($conn,"SELECT * FROM employee");
?>
<!DOCTYPE html> <html> <head> <link rel="stylesheet" href="style.css"> <title>Delete employee data</title> </head> <body> <table> <tr> <td>Employee Id</td> <td>First Name</td> <td>Last Name</td> <td>City</td> <td>Email id</td> <td>Action</td> </tr> <?php $i=0; while($row = mysqli_fetch_array($result)) { ?> <tr class="<?php if(isset($classname)) echo $classname;?>"> <td><?php echo $row["userid"]; ?></td> <td><?php echo $row["first_name"]; ?></td> <td><?php echo $row["last_name"]; ?></td> <td><?php echo $row["city_name"]; ?></td> <td><?php echo $row["email"]; ?></td> <td><a href="delete-process.php?userid=<?php echo $row["userid"]; ?>">Delete</a></td> </tr> <?php $i++; } ?> </table> </body> </html>

delete-process.php

<?php
include_once 'database.php';
$sql = "DELETE FROM employee WHERE userid='" . $_GET["userid"] . "'";
if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>

delete-process.php Alternative Code (PDO)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "crud";

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 employee WHERE userid='" . $_GET["userid"] . "'";

    /*use exec() because no results are returned*/
    $conn->exec($sql);
    echo "Record deleted successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "
" . $e->getMessage(); } $conn = null; ?>