Today I will discuss an example of Deleting Multiple Records with mysqli in PHP Using Form Input.
Suppose we have a database table and we want to allow the user to delete some or all of the records. Since user will access the application through a web interface, the delete option should be available on the web page. So, first we make all the records displayed to the user. Further, a checkbox is also there with each record and user need to select the records he want to delete. The following screenshot shows the form that displays the records.

When user clicks on the ‘Delete Records’ button, the SQL Delete command runs for all the selected records. The following code shows how to display the result set of a. SQL Select query in an HTML form and how to run the delete command for multiple records.
The Program for Deleting Multiple Records with mysqli in PHP Using Form Input
deleterecords.php
<html>
<head>
<title>Deleting Records</title>
</head>
<body>
<?php
$host='localhost';
$user='root';
$pass='';
$db='institute';
$con=new mysqli($host, $user, $pass, $db);
if($con->error)
{
die('Error connecting to the database '.$con->error);
}
else
{
echo 'Connected!<br>';
}
$q='select * from emp';
$retrieved_data=$con->query($q);
?>
<form name="f1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<table border="0" cellpadding="5" cellspacing="5">
<tr>
<th>Employee ID</th>
<th>Employee Name</th>
<th>Salary</th>
<th>Designation</th>
<th>Department</th>
<th>Remove</th>
</tr>
<?php
while($r=$retrieved_data->fetch_assoc())
{
$t=$r['emp_id'];
echo '<tr>';
echo '<td>', $r['emp_id'], '</td>';
echo '<td>', $r['emp_name'], '</td>';
echo '<td>', $r['salary'], '</td>';
echo '<td>', $r['designation'], '</td>';
echo '<td>', $r['dept_no'], '</td>';
echo '<td><input type="checkbox" name="ch[]" value="'.$t.'"/></td>';
echo '<tr/>';
}
?>
</table>
<input type="submit" name="submit" value="Delete Records"/>
</form>
<?php
if(isset($_POST['submit']))
{
$query="delete from emp where emp_id=?";
$statement=$con->prepare($query);
if(!empty($_POST['ch'])){
foreach($_POST['ch'] as $p)
{ echo $p;
$statement->bind_param("i", $p);
$statement->execute();
}
}
header("Location: display.php");
}
$con->close();
?>
</body>
</html>
At first, we make a connection with the database. After that, the query is executed using the query() method and the variable retrieved_data gets the result set. The form displays the retrieved records in an HTML table. Also, note the use of fetch_assoc() function that returns a record from the result set at a time. Therefore, the the while loop creates a row of HTML table and displays all the fields of that row in each iteration. Each row also displays a checkbox. Furthermore, the selections made by the user can be obtained as an array. the following image shows the user selection.
Output

Once, the user makes the selection, he presses the Delete Record button. As a result, the specific PHP script executes and it runs the delete query as a prepared statement for all the selected records in a loop. When the queries finish executing, the header() function opens the display.php file that displays the records after deletion.

display.php
<html>
<head>
<title>Employee Records</title>
</head>
<body>
<?php
$host='localhost';
$user='root';
$pass='';
$db='institute';
$con=new mysqli($host, $user, $pass, $db);
if($con->error)
{
die('Error connecting to the database '.$con->error);
}
else
{
echo 'Connected!<br>';
}
$q='select * from emp';
$retrieved_data=$con->query($q);
?>
<table border="0" cellpadding="5" cellspacing="5">
<tr>
<th>Employee ID</th>
<th>Employee Name</th>
<th>Salary</th>
<th>Designation</th>
<th>Department</th>
</tr>
<?php
while($r=$retrieved_data->fetch_assoc())
{
echo '<tr>';
echo '<td>', $r['emp_id'], '</td>';
echo '<td>', $r['emp_name'], '</td>';
echo '<td>', $r['salary'], '</td>';
echo '<td>', $r['designation'], '</td>';
echo '<td>', $r['dept_no'], '</td>';
echo '<tr/>';
}
?>
</table>
</body>
</html>
Further Reading
Examples of Array Functions in PHP
Registration Form Using PDO in PHP
Inserting Information from Multiple CheckBox Selection in a Database Table in PHP
PHP Projects for Undergraduate Students
Architectural Constraints of REST API
Creating a Classified Ads Application in PHP
- Angular
- ASP.NET
- C
- C#
- C++
- CSS
- Dot Net Framework
- HTML
- IoT
- Java
- JavaScript
- Kotlin
- PHP
- Power Bi
- Python
- Scratch 3.0
- TypeScript
- VB.NET
