update multiple rows using PHP MySQL
PHP

How To Update Multiple Rows using PHP MySQL

By Deepak Raj
0 48,257

In this Article we are discuss about How to Update Multiple Rows using PHP MySQL. we are well expertise with the PHP CRUD system by accessing MySQL using PHP Logic . We have been seen about update Multi Table rows one at a time. this articles deals with selecting multiple rows applying update operation. For selecting multiple rows we are going to use checkbox input for submit selected rows. We can get selected checkbox values via Form from PHP after page refresh. To learn this please follow these steps.

How To Update Multiple Rows using PHP MySQL

Setup Database

1-Creating Database

  • Open Phpmyadmin in your Browser
  • Click on Database Tab Display on Top side
  • Give the Database name “multi_edit“.
  • After Creating Database Open it.
  • Click on SQL Tab on Top area
  • Copy the Below Source Code and paste it.
  • Then Click on Go.
-- phpMyAdmin SQL Dump
-- version 4.2.7.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jun 28, 2016 at 09:38 AM
-- Server version: 5.6.20
-- PHP Version: 5.5.15

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `multi_edit`
--

-- --------------------------------------------------------

--
-- Table structure for table `member`
--

CREATE TABLE IF NOT EXISTS `member` (
`member_id` int(11) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `middlename` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `member`
--

INSERT INTO `member` (`member_id`, `firstname`, `lastname`, `middlename`, `address`, `email`) VALUES
(1, 'Abhi', 'Singh', 'Kumar', 'India', 'example@mail.com'),
(2, 'Kriss', 'Singh', 'Kumar', 'United States', 'example@mail.com'),
(3, 'Deepak', 'Raj', 'Kumar', 'India', 'example@mail.com'),
(4, 'Ricky', 'Mishra', 'Kumar', 'Mexico', 'example@mail.com'),
(5, 'Priyanshu', 'Raj', 'Kumar', 'India', 'example@mail.com'),
(6, 'Priyanshu', 'Raj', 'Kumar', 'India', 'example@mail.com'),
(7, 'Priya', 'Raj', 'Kr.', 'India', 'example@email.com'),
(8, 'Arman', 'Singh', 'Kumar', 'India', 'example@email.com'),
(9, 'Rupesh', 'Raj', 'Kumar', 'INDIA', 'example@email.com');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `member`
--
ALTER TABLE `member`
 ADD PRIMARY KEY (`member_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `member`
--
ALTER TABLE `member`
MODIFY `member_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

2- Import DB File

After Downloading the source code extract it in your root folder.

  • Open Phpmyadmin in your Browser
  • Click on Database Tab Display on Top side
  • Give the Database name “multi_edit“.
  • After Creating Database Open it.
  • Click on Import Tab on Top area
  • You can Find Db file in  Downloaded source code Select it.
  • Then Click on Go.

3- Creating Database Connection

After import Database File then next step is creating database connection using php copy the below code and save it is as “dbcon.php”.

<?php
mysql_select_db('multi_edit',mysql_connect('localhost','root',''))or die(mysql_error());
?>

 

Display Data From Database

After Setup database next step is Display all data on browser from Database using PHP. You can easily do it after copy source code and paste in HTML Editor then save it is as “index,php”.

<?php 
include('header.php');
?>
<body>
<div class="container">
<br>
<br>
<form action="edit.php" method="post">
	<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">
		<div class="alert alert-info">
			<h2 style="text-align:center; font-family:Lobaster;">Update Multiple Rows in PHP/MySQL with Checkbox</h2>
		</div>
		<thead>
			<tr>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">FirstName</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">LastName</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">MiddleName</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">Address</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">Email</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">Action</th>
			</tr>
		</thead>
		<tbody>
		<?php 
		$query=mysql_query("select * from member")or die(mysql_error());
		while($row=mysql_fetch_array($query)){
		$id=$row['member_id'];
		?>
			<tr>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['firstname'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['lastname'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['middlename'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['address'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['email'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;">
					<input name="selector[]" type="checkbox" value="<?php echo $id; ?>">
				</td>
			</tr>
		<?php  } ?>						 
		</tbody>
	</table>
	<br />				
	<button class="btn btn-success pull-right" style="font-family:Lobaster;" name="submit_mult" type="submit">
		Update Data
	</button>
</form>



</div>
</body>
</html>

Creating Update page

Next step is creating update page where update multiple data through Form copy the below source code and save it is as “edit.php”.

<?php 
include('header.php');
?>
<body>
<div class="container">
<br>
<br>
<form action="edit.php" method="post">
	<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">
		<div class="alert alert-info">
			<h2 style="text-align:center; font-family:Lobaster;">Update Multiple Rows in PHP/MySQL with Checkbox</h2>
		</div>
		<thead>
			<tr>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">FirstName</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">LastName</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">MiddleName</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">Address</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">Email</th>
				<th style="text-align:center; font-family:Lobaster; font-size:18px; color:blue;">Action</th>
			</tr>
		</thead>
		<tbody>
		<?php 
		$query=mysql_query("select * from member")or die(mysql_error());
		while($row=mysql_fetch_array($query)){
		$id=$row['member_id'];
		?>
			<tr>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['firstname'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['lastname'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['middlename'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['address'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;"><?php echo $row['email'] ?></td>
				<td style="text-align:center; font-family:Lobaster; font-size:18px;">
					<input name="selector[]" type="checkbox" value="<?php echo $id; ?>">
				</td>
			</tr>
		<?php  } ?>						 
		</tbody>
	</table>
	<br />				
	<button class="btn btn-success pull-right" style="font-family:Lobaster;" name="submit_mult" type="submit">
		Update Data
	</button>
</form>



</div>
</body>
</html>

Update Query in PHP

in this step we are validating form who created in update page using PHP copy the below source code and save it is as “edit_save.php”.

<?php
include('dbcon.php');
$member_id=$_POST['member_id'];
$firstname=$_POST['firstname'];
$lastname=$_POST['lastname'];
$middlename=$_POST['middlename'];
$email=$_POST['email'];
$address=$_POST['address'];

$N = count($member_id);
for($i=0; $i < $N; $i++)
{
	$result = mysql_query("UPDATE member SET firstname='$firstname[$i]', lastname='$lastname[$i]', middlename='$middlename[$i]' ,address='$address[$i]' , email='$email[$i]'  where member_id='$member_id[$i]'")or die(mysql_error());
}
header("location: index.php");

?>

Preview

update multiple rows using php mysql update multiple rows using php mysql

If you want to Download Complete Source Code then you can Click on Below Button.

Download Source Code

You might also like More from author
Leave A Reply

Your email address will not be published.