Some of web applications, there have a single of deleting of record, what if there have a many record? In this tutorial we will show you, using PHP and jQuery to develop a simple web application a function can able to multiple delete records, using check box.
Step 1: Setting up the database
First thing to do we need some data, go your to your phpMyAdmin create a database let say your database name is ‘multiple_delete’, after creating the database create a table let say your table name is ‘products’ and we add the following fields to the table.
- id
- product_title
- product_price
After creating the table ‘products’, insert some of sample products.
INSERT INTO products (product_title, product_price) VALUES
('iphone 4s 16GB', '$500'),
('Playstation 3 120GB', '$200'),
('Nintendo Wii', '$190'),
('Nintendo 3DS', '$50'),
('Mac Book', '$800'),
('Ipod 3', '$700'),
('Ipod 2', '$300'),
('Iphone 4', '$300'),
('Mp3 Player', '$20'),
('GPS', '$350');
Step 2: Create Database Connection
We have our product sample from the database, so we need to connect from database. Create a file named ‘database.php’, and input the following code.
database.php
<?php
$db = mysql_connect("localhost", "root", ""); // your host, user, password
if(!$db) { echo mysql_error(); }
$select_db = mysql_select_db("multiple_delete"); // database name
if(!$select_db) { echo mysql_error(); }
?>
We have assigning the database credentials. the MySQL host, username, password and database name.
Step 3: Displaying the Product from the Database
To grab the data from the database, create file name index.php, our home page, and input the following code snippet.
index.php
<?php
require_once("database.php"); // 1. require the database file
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Deleting Multiple Records using PHP and jQuery | iStockphp.com</title>
<link href="style/style.css" rel="stylesheet" type="text/css" media="all" /> <!--- include the css file -->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"> </script> <!--- include the live jquery library -->
<script type="text/javascript" src="js/script.js"></script> <!--- include the our jquery file -->
</head>
<body>
<div id="wrap" align="center"> <!--wrap start-->
<h1>Deleting Multiple Records using PHP and jQuery</h1>
<form action="delete.php" method="post" name="data_table">
<table id="table_data">
<tr>
<td>id</td>
<td>Product name</td>
<td>Price</td>
<td>Check All <input type="checkbox" id="check_all" value=""></td>
</tr>
<?php
$query = mysql_query("SELECT `id`, `product_title`, `product_price` FROM `products`"); // 2. perform a query
while($row = mysql_fetch_array($query)) {
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['product_title']; ?></td>
<td><?php echo $row['product_price']; ?></td>
<td><input type="checkbox" value="<?php echo $row['id']; ?>" name="data[]" id="data"></td>
</tr>
<?php
} unset($row); // unset the query after perform
?>
</table>
<br />
<input name="submit" type="submit" value="Delete" id="submit">
</form>
</div> <!--wrap end-->
</body>
</html>
We require the ‘database.php’ for our database connection, and perform a simple query to fetch the data, and also we need to include the CSS for simple style and jQuery live script and jQuery script.
style.css
body {
background: url(../images/bg.jpg);
font-family: Arial, Helvetica, sans-serif;
font-size:13px
}
h1 {
color:#000000;
font-family:"Helvetica Neue",Helvetica,Arial,sans-serif;
}
p {
margin:10px;
padding:10px;
color:#000000;
}
table#table_data {
border: 1px solid #CCCCCC;
width: 485px;
}
table#table_data tr:first-child {
font-weight:bold;
text-transform:uppercase
}
table#table_data tr td {
border: 1px solid #CCCCCC;
text-align:center
}
table#table_data tr:first-child {
text-align:center;
border:1px solid #999
}
Step 4: Creating the jQuery Script
We create the jQuery script to triggered the all check boxes in the form, create file name ‘script.js’ and input the code snippet below.
script.js
jQuery(function($) {
$("form input[id='check_all']").click(function() { // triggred check
var inputs = $("form input[type='checkbox']"); // get the checkbox
for(var i = 0; i < inputs.length; i++) { // count input tag in the form
var type = inputs[i].getAttribute("type"); // get the type attribute
if(type == "checkbox") {
if(this.checked) {
inputs[i].checked = true; // checked
} else {
inputs[i].checked = false; // unchecked
}
}
}
});
$("form input[id='submit']").click(function() { // triggred submit
var count_checked = $("[name='data[]']:checked").length; // count the checked
if(count_checked == 0) {
alert("Please select a product(s) to delete.");
return false;
}
if(count_checked == 1) {
return confirm("Are you sure you want to delete these product?");
} else {
return confirm("Are you sure you want to delete these products?");
}
});
}); // jquery end
Step 5: The Deleting Process
After posting the data in the form action attribute, we create the PHP script to catch the post data and process the deleting script. Create the file name ‘delete.php’ and input the code snippet below.
delete.php
<?php
require_once("database.php");
if(isset($_POST['submit'])) {
$id_array = $_POST['data']; // return array
$id_count = count($_POST['data']); // count array
for($i=0; $i < $id_count; $i++) {
$id = $id_array[$i];
$query = mysql_query("DELETE FROM `products` WHERE `id` = '$id'");
if(!$query) { die(mysql_error()); }
}
header("Location: index.php"); // redirent after deleting
}
?>
Step 5: Complete
And now were done we creating the dynamic deleting multiple records using PHP and jQuery. Let’s have a look at what we’ve achieved:
- We’ve set up a database, table and products for our data.
- We’ve create a simple form to retrieve the data
- We’ve create the jQuery script to triggered the check boxes.
- We’ve create the deleting process to delete the post data array.
