Multiple Insert, Update, Delete example using PHP & MySQLi

codernew
This tutorial will guide you that how to perform multiple insert, update and delete operations using PHP & MySQLi, using this script you can update or delete multiple rows of MySQL database at a time on checkboxes selection with multiple insert, in my previous tutorial we have seen that how to Select / Deselect all checkboxes using jQuery, so using jQuery we can select all or multiple records from MySQL database and perform update or delete operations with the selected records, for better user interface i have used here again Bootstrap framework, we already have CRUD tutorials in this blog but i haven’t cover this topic yet, so let’s see the code.

Multiple Insert, Update, Delete (CRUD) using PHP & MySQLi

 

Read also : Add, Update and Delete Example using jQuery and PHP

Let’s start coding…

create a new database called “dbmultiple” and create “users” table inside it.
copy the following sql code and paste it in your phpMyAdmin to create database and table. 

CREATE DATABASE `dbmultiple` ;
CREATE TABLE `dbmultiple`.`users` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 25 ) NOT NULL ,
`last_name` VARCHAR( 35 ) NOT NULL 
) ENGINE = MYISAM ;

Now we have to create following files.

-dbcon.php
-index.php
-generate.php
-add-data.php
-edit_mul.php
-update_mul.php
-delete_mul.php

dbcon.php
contains simple database configuration code with MySQLi. 

<?php

  $DB_host = "localhost";
  $DB_user = "root";
  $DB_pass = "";
  $DB_name = "dbmultiple";
  
  $MySQLiconn = new MySQLi($DB_host,$DB_user,$DB_pass,$DB_name);
    
     if($MySQLiconn->connect_errno)
     {
         die("ERROR : -> ".$MySQLiconn->connect_error);
     }

?>

index.php
this is main index page which displays all the records from “users” table along with checkbox for each record, and by selecting the multiple checkbox user can update or delete the selected multiple records. 

<?php
 include_once 'dbcon.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>Multiple Insert, Update, Delete(CRUD) using PHP & MySQLi</title>
<link rel="stylesheet" href="style.css" type="text/css" />
<script src="jquery.js" type="text/javascript"></script>
<script src="js-script.js" type="text/javascript"></script>
</head>

<body>
<form method="post" name="frm">
<table width="50%" align="center" border="0">
<tr>
<td colspan="3"><a href="generate.php">add new records...</a></td>
</tr>
<tr>
<th>##</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
<?php
 $res = $MySQLiconn->query("SELECT * FROM users");
 $count = $res->num_rows;

 if($count > 0)
 {
  while($row=$res->fetch_array())
  {
   ?>
   <tr>
   <td><input type="checkbox" name="chk[]" class="chk-box" value="<?php echo $row['id']; ?>"  /></td>
   <td><?php echo $row['first_name']; ?></td>
   <td><?php echo $row['last_name']; ?></td>
   </tr>
   <?php
  } 
 }
 else
 {
  ?>
        <tr>
        <td colspan="3"> No Records Found ...</td>
        </tr>
        <?php
 }
?>

<?php

if($count > 0)
{
 ?>
 <tr>
    <td colspan="3">
    <label><input type="checkbox" class="select-all" /> Check / Uncheck All</label>
    <label id="actions">
    <span style="word-spacing:normal;"> with selected :</span>
    <span><img src="edit.png" onClick="edit();" alt="edit" />edit</span> 
    <span><img src="delete.png" onClick="delete_rec();" alt="delete" />delete</span>
    </label>
    </td>
 </tr>    
    <?php
}

?>

</table>
</form>
</body>
</html>

multiple insert

generate.php
contains simple HTML form which let’s you to enter number of records to input, ex 1 ,2 ,4, and accepts only two digit number, after inputting the number it will redirects you to “add-data.php” which will create multiple input tags to insert the data. 

<link rel="stylesheet" href="style.css" type="text/css" />
<form method="post" action="add-data.php">

<table width="50%" align="center" border="0">

<tr>
<td>Enter how many records you want to insert</td>
</tr>

<tr>
<td>
<input type="text" name="no_of_rec" placeholder="how many records u want to enter ? ex : 1 , 2 , 3 , 5" maxlength="2" pattern="[0-9]+" required />
</td>
</tr>

<tr>
<td><button type="submit" name="btn-gen-form">Generate</button> 
&nbsp;
<a href="index.php">back</a>
</td>
</tr>

</table>

</form>

add-data.php
this is the main file which helps you to insert multiple values as your choice, and the rest process was done in “for()” loop. 

<?php
error_reporting(0);
include_once 'dbcon.php';

if(isset($_POST['save_mul']))
{  
 $total = $_POST['total'];
  
 for($i=1; $i<=$total; $i++)
 {
  $fn = $_POST["fname$i"];
  $ln = $_POST["lname$i"];  
  $sql="INSERT INTO users(first_name,last_name) VALUES('".$fn."','".$ln."')";
  $sql = $MySQLiconn->query($sql);  
 }
 
 if($sql)
 {
  ?>
        <script>
  alert('<?php echo $total." records was inserted !!!"; ?>');
  window.location.href='index.php';
  </script>
        <?php
 }
 else
 {
  ?>
        <script>
  alert('error while inserting , TRY AGAIN');
  </script>
        <?php
 }
}
?>
<link rel="stylesheet" href="style.css" type="text/css" />
<div class="container">
<?php
if(isset($_POST['btn-gen-form']))
{
 ?>
    <form method="post">
    <input type="hidden" name="total" value="<?php echo $_POST["no_of_rec"]; ?>" />
 <table width="50%" align="center" border="0">
    
    <tr>
    <td colspan="3"><a href="generate.php">insert more records...</a></td>
    </tr>
    
    <tr>
    <th>##</th>
    <th>First Name</th>
    <th>Last Name</th>
    </tr>
 <?php
 for($i=1; $i<=$_POST["no_of_rec"]; $i++) 
 {
  ?>
        <tr>
        <td><?php echo $i; ?></td>
        <td><input type="text" name="fname<?php echo $i; ?>" placeholder="first name" /></td>
        <td><input type="text" name="lname<?php echo $i; ?>" placeholder="last name" /></td>
        </tr>
        <?php
 }
 ?>
    <tr>
    <td colspan="3">
    
    <button type="submit" name="save_mul">Insert all Records</button> 

    <a href="index.php" >Back to index</a>
    
    </td>
    </tr>
    </table>
    </form>
 <?php
}
?>
</div>

Multiple Update

edit_mul.php
this file will create multiple input tags which have selected to be edited.
“$_POST[‘chk’]” variable will selects multiple records from database to be edit, and form will generated dynamically in “for()” loop. 

<?php

 include_once 'dbcon.php';

 if(isset($_POST['chk'])=="")
 {
  ?>
        <script>
  alert('At least one checkbox Must be Selected !!!');
  window.location.href='index.php';
  </script>
        <?php
 }
 $chk = $_POST['chk'];
 $chkcount = count($chk);
 
?>
<form method="post" action="update_mul.php">
<link rel="stylesheet" href="style.css" type="text/css" />
<table width="50%" align="center" border="0">
<tr>
<th>First Name</th>
<th>Last Name</th>
</tr>
<?php
for($i=0; $i<$chkcount; $i++)
{
 $id = $chk[$i];   
 $res=$MySQLiconn->query("SELECT * FROM users WHERE id=".$id);
 while($row=$res->fetch_array())
 {
  ?>
  <tr>
  <td>
     <input type="hidden" name="id[]" value="<?php echo $row['id'];?>" />
  <input type="text" name="fn[]" value="<?php echo $row['first_name'];?>" />
        </td>
        <td>
  <input type="text" name="ln[]" value="<?php echo $row['last_name'];?>" />
  </td>
  </tr>
  <?php 
 }   
}
?>
<tr>
<td colspan="2">
<button type="submit" name="savemul">Update all</button>&nbsp;
<a href="index.php">cancel</a>
</td>
</tr>
</table>
</form>

update_mul.php
this file will update the multiple selected records and redirects to the index page.
the update query will execute in “for()” loop as below. 

<?php
include_once 'dbcon.php';
$id = $_POST['id'];
$fn = $_POST['fn'];
$ln = $_POST['ln'];
$chk = $_POST['chk'];
$chkcount = count($id);
for($i=0; $i<$chkcount; $i++)
{
 $MySQLiconn->query("UPDATE users SET first_name='$fn[$i]', last_name='$ln[$i]' WHERE id=".$id[$i]);
}
header("Location: index.php");
?>

Multiple Delete

delete_mul.php
this is simple script to delete multiple records as above update or insert this delete query will also execute in “for()” loop, and the rest script in this file is for alerting the user. 

<?php
 
 error_reporting(0);
 
 include_once 'dbcon.php';
 
 $chk = $_POST['chk'];
 $chkcount = count($chk);
 
 if(!isset($chk))
 {
  ?>
        <script>
  alert('At least one checkbox Must be Selected !!!');
  window.location.href = 'index.php';
  </script>
        <?php
 }
 else
 {
  for($i=0; $i<$chkcount; $i++)
  {
   $del = $chk[$i];
   $sql=$MySQLiconn->query("DELETE FROM users WHERE id=".$del);
  } 
  
  if($sql)
  {
   ?>
   <script>
   alert('<?php echo $chkcount; ?> Records Was Deleted !!!');
   window.location.href='index.php';
   </script>
   <?php
  }
  else
  {
   ?>
   <script>
   alert('Error while Deleting , TRY AGAIN');
   window.location.href='index.php';
   </script>
   <?php
  }
  
 }
?>

js-script.js
javascript code for select / deselect all checkbox, “edit_records()”,”delete_records()” function redirects user on specific page for edit/delete on submit action. 

// JavaScript Document


//  for select / deselect all

$('document').ready(function()
{
    $(".select-all").click(function ()
    {
        $('.chk-box').attr('checked', this.checked)
    });
        
    $(".chk-box").click(function()
    {
        if($(".chk-box").length == $(".chk-box:checked").length)
        {
            $(".select-all").attr("checked", "checked");
        }
        else
        {
            $(".select-all").removeAttr("checked");
        }
    });
});


//  for select / deselect all

//  dynamically redirects to specified page
function edit_records() 
{
 document.frm.action = "edit_mul.php";
 document.frm.submit();  
}
function delete_records() 
{
 document.frm.action = "delete_mul.php";
 document.frm.submit();
}

that’s it we have created here Simple Multiple insert, Update and Delete CRUD Script using MySQLi with Bootstrap Designing.
download the script by clicking following link and try it in your projects. Feel free to comment your suggestions regarding this tutorial.