Many times a situation comes while developing website where we want to insert or delete multiple rows simultaneously, here in this post i am going to explain you how you can delete multiple records from the MySQL database simultaneously using PHP code, its very simple we have to give an option to the user to select multiple records which they want to delete, we can give this type of option to the user using checkbox, user have to select the checkbox display in front of every records which they want to delete and after selecting the records press delete button then we have to run a loop and delete all the selected records from the database one by one, before doing this we have to associate a checkbox with one of the unique column in the database which we use to identify the records.
In our example we are using a table persons having three column first_name, last_name, email_address because its just a demo to explain how we delete multiple records from the database simultaneously so you can apply this code in your project and modify the columns according to your requirements its not a problem so here you just focus on the code of php how we implement it for doing such task.
First of all you have to create a database name test_db and table persons see the below mysql commands to create database and table.
Create database test_db;
Create table persons(first_name char(25),last_name char(25),email_address char(50));
Now in the persons table you have to insert some records here i am not going to tell you how you can insert records in MySQL database using PHP because i already explain it in my previous post you can check my previous post How We Insert Records in MySQL using PHP. here you can insert records manually using MySQL commands its not a problem, here we have to focus how we delete multiple records from MySQL database using PHP because its our target here.
In this example for creating connection with MySQL database we use five variables as given below:
$host="localhost"; // Host name
$username="root"; // Mysql username
$password="root"; // Mysql password
$db_name="test_db"; // Database name
$tbl_name="persons"; // Table name
so at all the places in the code we have to give only variable name instead of the database name and table name so its easy for you when you modify this code according to your requirements.
In this example for creating connection with MySQL database we use five variables as given below:
$host="localhost"; // Host name
$username="root"; // Mysql username
$password="root"; // Mysql password
$db_name="test_db"; // Database name
$tbl_name="persons"; // Table name
so at all the places in the code we have to give only variable name instead of the database name and table name so its easy for you when you modify this code according to your requirements.
Related Post:
1. Generating Captcha in PHP.2. Implode and Explode Function in PHP.
3. Dynamically Add Rows in Select Tag.
4. Handling Session In PHP Using Login Example.
5. Display Data in Select tag from database and Insert it in Another Table
Now create a file index.php and past the below code in that file.
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Delete Multiple Records Using Check Box</title>
</head>
<body>
<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password="root"; // Mysql password
$db_name="test_db"; // Database name
$tbl_name="persons"; // Table name
mysql_connect("$host", "$username", "$password")or die("Problem in Connecting With Database");
mysql_select_db("$db_name")or die("Problem in Selecting Table");
$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
$count=mysql_num_rows($result);
?>
<?php
if(isset($_POST['delete'])){
$arr = $_POST['checkbox'];
foreach($arr as $email){
$sql = "delete from $tbl_name where email_address='$email'";
$result1 = mysql_query($sql);
}
if($result1){
echo "<meta http-equiv=\"refresh\" content=\"0;URL=index.php\">";
}
}
mysql_close();
?>
<center>
<form method="post" action="">
<table width="400" border="1">
<tr>
<td colspan="5" align="center"><strong>Delete Multiple Records Simultaneously</strong> </td>
</tr>
<tr>
<td align="center" ><strong>Select</strong></td>
<td align="center" ><strong>First Name</strong></td>
<td align="center"><strong>Last Name</strong></td>
<td align="center"><strong>Email Address</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td align="center"><input name="checkbox[]" type="checkbox" value="<?php echo $rows['email_address']; ?>"></td>
<td><?php echo $rows['first_name']; ?></td>
<td><?php echo $rows['last_name']; ?></td>
<td><?php echo $rows['email_address']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
</table>
</form>
</center>
</body>
</html>
Output
 |
| Delete Multiple Records From MySQL Database Simultaneously Using PHP |
3 Comments
First i would like to Thank You for writing such a detailed post on this, Now i want to know how i can do this using Ajax, pls. write a post for doing this using Ajax.
ReplyDeleteThanks alot for this great article I used this code and it is working without any disappointing.
ReplyDeleteThe Big key is Time. Time for your improvement is one and the second is the time it takes your program to interpret your code. java programming
ReplyDelete