Multiple Data Insert, Update, Delete with Example

In this tutorial, I will show you how to perform multiple CRUD operations at a time using PHP, Javascript and MySQLI database.

Overview

To continue on our task we will need to create following files,

  • Add-data.php
  • Dbcon.php
  • Delete_mul.php
  • Edit_mul.php
  • Generate.php
  • Index.php
  • Update_mul.php
  • Js-script.js

Database Schema

Create a database named “DBmultiple “. Then add tables to the database to store the data for the sample. I will be using “users” to store users information.
Copy and paste the source code to create a database for yourself.

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 ;

Dbcon.php

After creating necessary database and tables its time to establish a connection between the database and files.
This file contains the database configuration code. Copy and save the file as Dbcon.php.

<
?php

$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "dbmultiple";

$SQLConn = new MySQLi($DB_host,$DB_user,$DB_pass,$DB_name);

if($SQLConn->connect_errno)
{
die("ERROR : -> ".$SQLConn->connect_error);
}

?>

Add-data.php

This file contains the code to add multiple data at a time, the code uses generate.php to get a number of data fields that the user is willing to insert at a time.
Copy the code and save as “Add-data.php”.

<?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 = $SQLConn->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>

Delete_mul.php

This file contains the code that is used to delete multiple records a time.
Here, a POST request is sent to server with a number of checkboxes checked for deletion. Then, inside a loop, it will increment all the values until the desired number of checkboxes are deleted and the MySQLI query will execute in order to delete the selected entries.

<?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=$SQLConn->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
}

}
?>

Edit_mul.php

After adding the add and delete codes to the project its time to edit the available data.
The code below is used to edit multiple records at a time.
In this part also POST request is sent to the server with a number of checkboxes checked for an update.Then, inside a loop, it will increment all the values until the desired number of checkboxes are met to edit and the MySQLI query will execute in order to update the selected entries.
Copy and save the file as “Edit_mul.php”.

<?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);

?>
<title>Tutorial-09</title>
<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=$SQLConn->query("SELECT * FROM users WHERE id=".$id);
while($row=$res->fetch_array())
{
?>
<tr align="center">
<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>

Generate.php

This file contains the code that is used generate field according to user’s input on how many records he/she is willing to add.
After getting the input from the user values are send to add-data.php file using POST method.
Copy and save the file as “Generate.php”.

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

<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>

Index.php

Now that we have created the insert, update, delete pages its time, to sum up, them and display the available data in the main page.
This page contains the HTML code which displays all the records available in the database as well as the options to select and edit or delete records accordingly.
During the initial load of the pageit’sit queries all the databases and gather the data in order to show it in the table so users can view all data in one grid.
Copy and Save the code as “Index.php”.

<?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>Tutorial-09</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 = $SQLConn->query("SELECT * FROM users");
$count = $res->num_rows;

if($count > 0)
{
while($row=$res->fetch_array())
{
?>
<tr align="center">
<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 Fields Available</td>
</tr>
<?php
}
?>

<?php

if($count > 0)
{
?>
<tr>
<td colspan="3">
<label id="actions">
<button type="button" onClick="edit_records();" alt="edit">edit </button>
<button type="button" onClick="delete_records();" alt="delete">delete </button>
</label>
</td>
</tr>
<?php
}

?>

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

Update_mul.php

This file contains the code that is used to update multiple records at a time.
It reads the number of records we need to insert and also the values we need to insert through the POST method. Then, all the values will be iterated using the for loop in order to save in the database.

<?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++)
{
$SQLConn->query("UPDATE users SET first_name='$fn[$i]', last_name='$ln[$i]' WHERE id=".$id[$i]);
}
header("Location: index.php");
?>

Js-script.js

This file contains the Javascript code that handles the checkbox selections as well as page redirect according to button clicks by the user.
Copy and save the codes as “Js-script.js”.

$('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");
}
});
});

// 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();
}

Now that you have seen how to perform multiple selections and crude operation using PHP and JavaScript in MwSQLI, its time to try it yourself.
Tell me if you come upon a trouble during the tutorial.
If you feel it helped you share it with your friends and community. I really appreciate it.
You can download the complete working model here.

Download the complete source code.

Grab the the free working demo Intantly!

You have Successfully Suscribed!

5 thoughts on “Multiple Data Insert, Update, Delete with Example

Leave a Reply

Your email address will not be published. Required fields are marked *