Multiple Insert, Update, Delete example using PHP & MySQLi | Coding Cage

Multiple Insert, Update, Delete example using PHP & MySQLi

On By
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.




Like This Post? Please share!
Subscribe through email and get Free updates

You May Also Like...

49 comments:

  1. hello sir,

    your article is too gud but where is database ?? i want to use it.!!

    ReplyDelete
    Replies

    1. hi Ajay,

      database is there at the starting of this tutorial :)

      or copy paste below sql code in phpMyAdmin and use it

      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 ;

      Delete

  2. your code is great and it's working. yeheey

    ReplyDelete

  3. Hello friend! Thanks a lot for sharing this very useful code with us! I've been searching for this for a long time... Could explain me how can I introduce a modal windows effect in the opening of image and pdf files? I have the jQuery code for this, but I do not know how make it works in your code... (Sorry for my english... Greetings from Brazil!)

    ReplyDelete

  4. Hi

    The Multiple Insert, Update, Delete (CRUD) using PHP & MySQLi is well done Thanks.

    Any update on how to avoid the following:

    - same record can be enter x times (double records)
    - empty record can be enter
    - record delete without modal windows

    - PDO instead of mysqli
    Best

    ReplyDelete

  5. Very nicely written CODE and useful to learn. The example is based on users table that has two fields, first name and last name. Therefore there is not "where" in the mysqli, so display and edit all the records from "users" table.
    How can I used a table with where, for example, where city = 1 and pass the 1 on all the php files (add-data.php, edit-mul.php, update-mul.php, delete-mul.php;

    ReplyDelete
    Replies

    1. hello valis,
      just alter users table and add new field as user_city , that's it, there you can use this table with where clause
      like this :
      select * from users where user_city = "xyz"

      here is the 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 ,
      `user_city` VARCHAR( 35 ) NOT NULL
      ) ENGINE = MYISAM ;

      Delete

  6. sir can u give this script for mysql

    ReplyDelete

  7. Hello Sir can you pls tell me that how to convert that into sql

    ReplyDelete
    Replies

    1. hello himanshu,
      why you want to use deprecated MySQL extension, there is improved extensions like MySQLi or PDO , that's why i use MySQLi in this tutorial, i suggest you to use MySQLi or PDO , please don't use MySQL

      Delete

  8. Hi. I am using this now, but I'm trying to use it for data from multiple table CRUD. I have so far done displaying, but for the insert part, do I make two queries? Or do I use transactions as stated here.

    The same goes for edit and delete.

    Can you make tutorial for mutiple table CRUD? Thank you!

    ReplyDelete

  9. Hi. Under index.php, line 62:
    onClick="edit();" should be onClick="edit_records();"
    It is correct in the download files though.

    ReplyDelete
    Replies

    2. how about the delete? my code is error. pls help

      Delete

    3. how about the delete? my code is error. pls help

      Delete

  10. how to insert multiple data in php mysql

    ReplyDelete

  11. script is good but the JS and style files missing and also download link disabled.
    please fixed this.
    Thanks

    ReplyDelete

  12. Hi, well done Thanks.

    Any update on PDO version instead of mysqli?

    ReplyDelete

  13. Hello,
    Your code is awesome can you please guide me in one more in this code?
    How will add search input field in this code?
    Can you provide me with the code?
    Thabk you.

    ReplyDelete

  14. its great tutorial,, but i can't download skrip from box,, i have try many time to download it,, please,, help me

    ReplyDelete

  15. Hi, This file cannot download through Box.com, Please sort it out asap. Thank You

    ReplyDelete

  16. i have done like u but my edit and delete query not work alert show that at least one checkbox must be selected please guide me

    ReplyDelete

  17. Hello admin,,
    can not be downloaded. try uploading again min..

    ReplyDelete

  18. Hi, great article, thanks! For some reason box is not allowing a download just moves a bit when the download button is clicked. I saw you used dropbox on another post, any way you can put this one on dropbox? Thanks!

    ReplyDelete

  20. Partner, How can I download the code. It is not downloading in the BOX.

    ReplyDelete

  21. its great tutorial,, but i can't download skrip from box,, i have try many time to download it,, please,, help me

    ReplyDelete

  22. hi admin, great article. but your link can't downloaded.
    could you send it to me in my email ?
    thanks

    ReplyDelete

  23. In index:
    62. onClick="edit();"
    63. onClick="delete_rec();"


    IS:

    62. onClick="edit_record();"
    63. onClick="delete_record();"

    ReplyDelete

  24. Hi ,I cannot download your files from app.box.com showing we cant preview your files.Please help to download

    ReplyDelete

  25. Pradeep : Sorry buddy, your download link is not working, but I have a Mirror link for it for all visitor can download source Code via this link
    DOWNLOAD SCRIPT

    ReplyDelete

  26. Hi Guys

    Is anyone having problems downloading the sample files?

    ReplyDelete

  27. Hello Sir ...Download link no works ...any update please???

    ReplyDelete

  28. Thank you. I not downloading in the BOX.

    ReplyDelete

  29. i can't download source file please fix link.

    ReplyDelete

  30. "check/uncheck all" checkbox is not working. please help

    ReplyDelete

  31. "check/uncheck all" checkbox is not working. please help

    ReplyDelete

  33. Hello, can you explain the code written on line 11-12 in add-data.php? Thanks.

    ReplyDelete

  34. i can't download source file please fix link.

    ReplyDelete

  35. hello Sir,
    How can I add multiple fields with image?

    ReplyDelete

  36. TRY THIS DEMO:
    http://www.sanwebcorner.com/2014/03/insert-delete-edit-update-operation.html

    ReplyDelete

  38. hi, your code was awesome but i was thinking how to add function that don't allow the user to add if the input is empty..
    and a restriction where in no same value can be inserted..
    is it possible todo?

    ReplyDelete

  39. Wonderful code there..
    It could even be better if you could add some validation

    ReplyDelete

  40. how can i insert image to this application

    ReplyDelete