codescracker


php

PHP and MySQLi Delete Record from Table



« Previous Tutorial Next Tutorial »


Here you will learn about how to delete or remove record or data from MySQL table using PHP and MySQLi.

What to do Before Deleting Record from Table

Before deleting or removing any data from the table using PHP and MySQLi, be sure that you have learned our previous tutorial, and created database and table namely codescrackerTwo and codescrackerTable.

PHP and MySQLi Delete Record Example

Here is an example demonstrates about deleting the data or record from the MySQL table using PHP.

Important - Before deleting any record or before performing the query to delete any record from the MySQL database table, be sure that you have supplied or provided the primary key value such as id, student id, produce id, username, etc. to delete only single record of that primary key value. And in case if you will not provide any primary key value, then all the record from the table will be removed or deleted. Therefore be careful, before going to perform deletion operation on the table using PHP with MySQLi.

<?php 
	$server = "localhost";
	$user = "root";
	$pass = "";
	$database = "codescrackerTwo";
	$conn = mysqli_connect($server, $user, $pass, $database);
	if(mysqli_connect_error())
	{
		echo "<p>OOppss..some error are occurring...kindly try again later...exiting...</p>";
		exit();
	}
?>
<html>
<head>
	<title>PHP and MySQLi Delete Record from Table Example</title>
</head>
<body>
<?php
	$id = 1;
	$sqlQry = "delete from codescrackerTable where id=$id";
	$res = $conn->query($sqlQry);
	if($res)
	{
		echo "<p>Record with ID = $id, is deleted successfully.</p>";
	}
	else 
	{
		echo "<p>Error in deleting the record..exiting...</p>";
		exit();
	}
?>
</body>
</html>

When you run the above example code that is used to delete data with id number 1 from the table named codescrackerTable that is located in the database named codescrackerTwo using PHP and MySQLi, then here is the sample output that will produce in your browser:

php mysqli delete record

And here is the screenshot of the table where the operation was performed using the above example of PHP and MySQLi.

delete record from table php mysqli

You can also delete more than one row or record from the table just by applying condition, for example, if you want to delete all those records from the same table that have id value more than or equal to 10 (that is, id with more than 9 as its value) then replace the following code:

$id = 1;
$sqlQry = "delete from codescrackerTable where id=$id";
$res = $conn->query($sqlQry);
if($res)
{
	echo "<p>Record with ID = $id, is deleted successfully.</p>";
}

with

$id = 10;
$sqlQry = "delete from codescrackerTable where id>=$id";
$res = $conn->query($sqlQry);
if($res)
{
	echo "<p>All the records are deleted successfully.</p>";
}

Below is the output produced after replacing the above code and re-running the same example in your browser.

delete records php mysqli

And following is the screenshot of the table after deleting all the records with more than 9 as id value using the above example of PHP and MySQLi.

delete records php mysqli example

As you can see from the above output, all the records are deleted and only those records are available that have id value, less than 10 (or less than or equal to 9).

Now, let's take one more example, that will delete and then live display that how much record is left in table using PHP and MySQLi.

<?php 
	$server = "localhost";
	$user = "root";
	$pass = "";
	$database = "codescrackerTwo";
	$conn = mysqli_connect($server, $user, $pass, $database);
	if(mysqli_connect_error())
	{
		echo "<p>OOppss..some error are occurring...kindly try again later...exiting...</p>";
		exit();
	}
?>
<html>
<head>
	<title>PHP and MySQLi Delete Record from Table Example</title>
</head>
<body>
<?php
	for($id=2; $id<=9; $id++)
	{
		$q1 = "delete from codescrackerTable where id=$id";
		$r1 = $conn->query($q1);
		if($r1)
		{
			echo "Record with Id: <b>$id</b> is deleted successfully<br/>";
			$q2 = "select count(id) from codescrackerTable";
			$r2 = $conn->query($q2);
			if($r2)
			{
				$row2 = mysqli_fetch_row($r2);
				echo "Total Record Left = ". $row2[0] ."<br/>";
			}
			else 
			{
				echo "Error occurred in counting..exiting..<br/>";
				exit();
			}
		}
		else 
		{
			echo "Error occurred in deleting..exiting..<br/>";
			exit();
		}
	}
?>
</body>
</html>

Here is the sample output produced by the above deleting record from table using PHP example code:

php mysqli delete record from table

And below is the screenshot of the table, now this table contains nothing (no data or record):

delete record example php mysqli

« Previous Tutorial Next Tutorial »








Tools
Calculator

Quick Links
Signup - Login - Give Online Test