How to delete multiple records in PHP

Introduction

When we want to make any sort of web application that uses database (MySql) and Php, data is inserted and deleted from the database. To insert and delete data into and from database we provide a interface that work as front end the client is unaware of what sort of code is running on backend. Today in this tutorial our focus is to first insert data into database and then delete multiple data from the database tables.

To achieve the above goal we divide our tutorial into two parts:

Create a Database to insert Data
Make an interface for inserting data to the database and Display the inserted data back on webpage and provide facility to delete multiple records from database.

Create a Database to insert Data

To create a database open Navicate for MySql or the other front end you use for your database. Create new database with name “technogeeks”. Create table with a name “emp” which should contain the following fields.

ID type: int
Name type: varchar
Fname type: varchar
Sal type: int

Making conection to MySql database

We will make new file with name conection.php and write the following code:

Listing 1:conection.php

<?php
$hostname_conection = "localhost";
 /* this is the server name(assigned to variable) which is localhost since it runs on local machine */
$database_conection = "technogeeks";
/* this is the database name( assigned to variable)*/
$username_conection = "root"; 
  /* user name (assigned to variable)*/
$password_conection = ""; 
     /*password (assigned to variable) */
$conection = mysql_connect($hostname_conection, $username_conection, $password_conection) or trigger_error(mysql_error(),E_USER_ERROR);  /* Mysql_connect function is used to conncet with database  it takes three parameters server/hostname, username,and password*/
mysql_select_db($database_conection,$conection) or die(mysql_error("could not connect to database!"));
/* Mysql_select is used to select the database it takes two parameters databasename and connection variable in this 	case $conection */
?>

Make an interface for inserting data to the database and display the inserted data back on webpage and provide facility to delete multiple records from database.

For interface we need to make use of a HTML forms. Make a new file and name it index.php containing the following code.

Note: include conection.php file to index.php file so that you can insert data into the database.

Listing 2: index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>MR.BOOl tutorails</title>
<?php require_once('conection.php'); ?>
</head>

<body>
<h1 align="center">
Learn how to delete mutiple recrods from the database.
</h1>
<?php  ?>
<form action="insert.php" method="post"  >
<table width="200" border="1" align="center">
  <tr>
    <td>Name</td>
    <td><label for="name"></label>
      <input type="text" name="name" id="name" /></td>
  </tr>
  <tr>
    <td>Fname</td>
    <td><label for="fname"></label>
      <input type="text" name="fname" id="fname" /></td>
  </tr>
  <tr>
    <td>salary</td>
    <td><label for="sal"></label>
      <input type="text" name="sal" id="sal" /></td>
  </tr>
  <tr>
    <td colspan="2" align="center"><input type="submit" name="button" id="button" value="Submit" />
      <input type="reset" name="reset" id="reset" value="reset" /></td>
    </tr>
</table>

</form>
<?php  /* now to display the data from the database which we inserted in above form we */
/* we make the query to select data from the table EMP */
$display ="select * from Emp"; 
$result=mysql_query($display,$conection) or die(mysql_error());
/* the query is executed and result of the query is stored in variable $result */
if($result == FALSE) {
    die(mysql_error()); /* displays error  */
}?>
<h1 align="center"> Displaying Recods in Table </h1>
<form method="get" action="" id="deleteform" >
<table width="245" border="1" align="center">
 <tr>
    <td width="51"><input type="submit" name="delete" id="button" value="delete"  onclick="document.getElementById('deleteform').action='delete.php'; document.getElementById('deleteform').submit();"/>
<!--- here on clicking the button the form is submitted and action is set to delete.php 
         Here we have used javaScript document refers to this whole page and now we can access any tag that has its id with help of getElementById() method and after the we specify the operation we want to perform in this case action and submit.
---> 
</td>
    <td width="50">NAME;</td>
    <td width="55">FNAME</td>
    <td width="47">SAL</td>
    
  </tr>
 <?php while($rows=mysql_fetch_array($result)){
/* here we make use of the while loop which fetch the data from the $result int array form and stores in $row now we can display each field from the table with $row[‘field_name’] as below */	
 ?>
  <tr>
  <td> <input  type="checkbox" name="empids[]" value="<?php echo $rows['id'];?>"  />
<!--here with each checkbox we send the id of the record in the  empids[] array --->
    </td>
    <td><?php echo $rows['name'] ?></td>/*display name of employee from the table Emp */
    <td><?php echo $rows['fname'] ?></td>/*display fname of employee from the table Emp */
    <td><?php echo $rows['sal'] ?></td> /*display sal of employee from the table Emp */
    <?php }?>
  </tr>
</table>
</form>
?>
</body>
</html>

Figure 1: displaying the interface

Now looking at the code we can see that user interface that is made is quite simple using forms text fields. But we should note the action of the form, it is set to insert.php it means when the data is inserted into the field , the form is submitted to the insert.php page. With method equal to post that is sending data to other page(insert.php ) without displaying it in the URL.

<form action="insert.php" method="post">

Now before going into detail lets first look at the insert.php file.

Which contain the following code shown below. The code is explained with comments.

Listing 3: insert.php

<?php include_once('conection.php');?> /* includes the conection.php file in order to establish the  connection to the database */  
<?php
$name = $_POST['name'];  /* the data sent from the index.php in the textfield name*/ 
$fname = $_POST['fname']; /* the data sent from the index.php in the textfield fname */
$sal = $_POST['sal']; /* the data sent from the index.php in the textfield sal */

$q = "insert into emp( name,fname,sal) values('$name','$fname',$sal)"; /* sql querywhich is saved in the variable $q */
If(mysql_query($q,$conection) ){
  /* mysql() function is used to run the query takes two parameter the query and the conection variable */
/* if the query is sucessfull the header() is used to go back to index.php */
	
header("location:index.php");
	exit();
/* exit() function exits the execution */
	}
else
{
/* if the in case the query fails the user is displayed a line of text that could not process
echo “could not process plzz check the code ”;
}
	?>

Figure 2: displaying data insertion

Now coming back to index.php if we look at the other form with id=” deleteform “the action of the form is set to delete.php it means when the data is inserted into the field, the form is submitted to the insert.php page. With method equal to GET that is sending data to other page (delete.php) displaying it in the URL?

Let’s look at the delete.php page that deletes the data from the database.

Listing 4: delete.php

<?php require_once('conection.php'); ?>
<?if (isset($_GET['delete']))  /* checks weather $_GET['delete'] is set*/ 
{if (isset($_GET['empids'])) /* checks weather $_GET['empids'] is set */
	{
		$checkbox = $_GET['empids']; /* value is stored in $checbox variable */
		if (is_array($checkbox)) {
		
			foreach ($checkbox as $key => $your_slected_id)
/* for each loop is used to get id and that id is used to delete the record below */
  {
$q="DELETE FROM emp WHERE id=$your_slected_id ";
/* Sql query to delete the records whose id is equal to $your_slected_id */
				 mysql_query($q,$conection) ;
/* runs the query */
			}
				header("location:index.php");
/* Goes back to index.php */
			}
		}
		else {
			echo" you have not selected reords  .. to delete";
			}
}
?>

Figure 3: displaying to delete the selected multiple records

Conclusion

Now you can delete multiple records from the database using the form end by just selecting the record with help of check box and the clicking the delete button.

Hope you liked the article, use the comments below if you have any question. See you next time.