Crazy Web App Developer: Deleting Multiple Records using PHP and jQuery

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.

View Demo | Download Source

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.

Crazy Web App Developer

Labels

Search This Blog

Sunday, October 20, 2013

Deleting Multiple Records using PHP and jQuery