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.
Read also : Add, Update and Delete Example using jQuery and PHP
copy the following sql code and paste it in your phpMyAdmin to create database and table.
-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.
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.
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.
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.
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.
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.
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.
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.
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.
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.phpcontains 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>
<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.phpthis 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>
<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.phpthis 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.
hello sir,
ReplyDeleteyour article is too gud but where is database ?? i want to use it.!!
hi Ajay,
Deletedatabase 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 ;
your code is great and it's working. yeheey
ReplyDeleteHello 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!)
ReplyDeleteHi
ReplyDeleteThe 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
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.
ReplyDeleteHow 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;
hello valis,
Deletejust 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 ;
sir can u give this script for mysql
ReplyDeleteHello Sir can you pls tell me that how to convert that into sql
ReplyDeletehello himanshu,
Deletewhy 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
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.
ReplyDeleteThe same goes for edit and delete.
Can you make tutorial for mutiple table CRUD? Thank you!
Hi. Under index.php, line 62:
ReplyDeleteonClick="edit();" should be onClick="edit_records();"
It is correct in the download files though.
THANK YOU !!!!
Deletehow about the delete? my code is error. pls help
Deletehow about the delete? my code is error. pls help
Deletehow to insert multiple data in php mysql
ReplyDeletescript is good but the JS and style files missing and also download link disabled.
ReplyDeleteplease fixed this.
Thanks
Hi, well done Thanks.
ReplyDeleteAny update on PDO version instead of mysqli?
Hello,
ReplyDeleteYour 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.
its great tutorial,, but i can't download skrip from box,, i have try many time to download it,, please,, help me
ReplyDeleteHi, This file cannot download through Box.com, Please sort it out asap. Thank You
ReplyDeletei 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
ReplyDeleteHello admin,,
ReplyDeletecan not be downloaded. try uploading again min..
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!
ReplyDeleteplease reload for dl
ReplyDeletePartner, How can I download the code. It is not downloading in the BOX.
ReplyDeleteits great tutorial,, but i can't download skrip from box,, i have try many time to download it,, please,, help me
ReplyDeletehi admin, great article. but your link can't downloaded.
ReplyDeletecould you send it to me in my email ?
thanks
In index:
ReplyDelete62. onClick="edit();"
63. onClick="delete_rec();"
IS:
62. onClick="edit_record();"
63. onClick="delete_record();"
thanks
DeleteHi ,I cannot download your files from app.box.com showing we cant preview your files.Please help to download
ReplyDeletePradeep : 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
ReplyDeleteDOWNLOAD SCRIPT
Hi Guys
ReplyDeleteIs anyone having problems downloading the sample files?
Hello Sir ...Download link no works ...any update please???
ReplyDeleteThank you. I not downloading in the BOX.
ReplyDeletei can't download source file please fix link.
ReplyDelete"check/uncheck all" checkbox is not working. please help
ReplyDelete"check/uncheck all" checkbox is not working. please help
ReplyDeletehtml css codings with examples
ReplyDeletethanks for site
DeleteHello, can you explain the code written on line 11-12 in add-data.php? Thanks.
ReplyDeletei can't download source file please fix link.
ReplyDeletehello Sir,
ReplyDeleteHow can I add multiple fields with image?
TRY THIS DEMO:
ReplyDeletehttp://www.sanwebcorner.com/2014/03/insert-delete-edit-update-operation.html
How to change link edit?
ReplyDeletehi, 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..
ReplyDeleteand a restriction where in no same value can be inserted..
is it possible todo?
Wonderful code there..
ReplyDeleteIt could even be better if you could add some validation
how can i insert image to this application
ReplyDeletehow to add image there
ReplyDelete