Delete Data From SQL Table Using PHP & MySQL

by

Hello Developer, In this tutorial, You will learn to delete data from SQL Table using PHP & MySQL. Even You will know more to delete existing rows of a table using MySQLi procedure, MySQLi Object-oriented, PDO & prepared statement with a new concept & an example.

delete data using php & Mysql

 

Steps to Delete Data using Using PHP & MYSQL

In this step, you will learn to delete data dynamically with an advanced coding concept that will help you to improve your coding skills for writing smart & standard code in PHP.

Before getting started, make sure that the local server (xamp/wamp) must be installed in your system and start it if it is not being started already.

Learn Also –

Preview an Image before Uploading using PHP

PHP Image Gallery With MySQL Database

Update data in SQL Table with Form using PHP & MySQL

You can test yourself to delete data with the following folder structure –

codingstatus/
    |__database.php
    |__table.php
    |__developers.php
    |

1. Connect PHP to MySQL database

You can use the following database connection query to connect PHP to the MySQL database

  • $hostName – It contains hostname
  • $userName – It contains database username
  • $password  – It contains database password
  • $databaseName – It contains a database name.

File Name – database.php

<?php
$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";
 $conn = new mysqli($hostName, $userName, $password, $databaseName);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
?>

3. Create Delete Button in HTML Table

Before creating the delete button, you have to insert data and display them in the HTML Table. You have not done it, you can do it with the help of the following tutorials –

Insert Data into Database using PHP & MySQL

Display data in HTML table using PHP & MySQL

Then you have to add the following code in the HTML Table in which you have already show records.

File Name – table.php

<td><a href="form.php?delete=<?php echo $data['id']; ?>" class="btn btn-danger">Delete</a></td>

2.  Delete Data using PHP & MySQLi

Now, go through the next points –

Delete Data –

You have to understand the following points to edit data –

  • First of all, include a database connection file database.php
  • assign $conn to a new variable $db and table name to another variable $table
  • access edit id with $_GET[‘delete’] and check it is set or not using isset() method. then implements the next point within if condition. this step will be executed when you click delete button in HTML table.
  • validate $_GET[‘delete’] using validate() function and assign it to the $id.
  • declare $id in an associative array and assign to the $condition. but array key name must be same as column name i.e id
  • call delete_data() function & assign to the $deleteMsg.

delete_data() – This function accepts three parameters like $db, $tableName, $column & $condition and It will delete data based on the specified id while you click delete button and return an message.

File Name – developers.php

<?php

$db= $conn;
$tableName="developers";
if(isset($_GET['delete']))
{
  $id= validate($_GET['delete']);
  $condition =['id'=>$id];
  $deleteMsg=delete_data($db, $tableName, $condition);
  header("location:form.php");

}
function delete_data($db, $tableName, $condition){

    $conditionData='';
    $i=0;
    foreach($condition as $index => $data){
        $and = ($i > 0)?' AND ':'';
         $conditionData .= $and.$index." = "."'".$data."'";
         $i++;
    }

  $query= "DELETE FROM ".$tableName." WHERE ".$conditionData;
  $result= $db->query($query);
  if($result){
  	$msg="data was deleted successfully";
  }else{
  	$msg= $db->error;
  }
  return $msg;
}

function validate($value) {
$value = trim($value);
$value = stripslashes($value);
$value = htmlspecialchars($value);
return $value;
}
?>

5. Test Yourself to update data

After Implementing previous steps, You can test it yourself to open the following URL in your web browser

https://localhost/codingstatus/table.php