PHP

Deleting Multiple Records with mysqli in PHP Using Form Input

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.

The Web Form for Deleting Multiple Records with mysqli in PHP
The Web Form for Deleting Multiple Records with mysqli in PHP

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

Selecting Records for Deletion
Selecting Records for Deletion

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.

Records After Deletion
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

Basic Programs 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

REST API Concepts

Creating a Classified Ads Application in PHP

programmingempire

princites.com

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *