Today we are Learning to Update or Delete multiple rows using PHP
We need to delete and update multiple rows in the backend of our website at times. Suppose you can manually delete and update a single project, but in a large project, you have to use delete and update multiple rows.
This can also save our time and we can also make your backend more attractive.
let’s start Update or Delete Multiple Rows Using PHP.
Table of Contents
Step 1: Create New Database and Table
Create a new chart named database
Then
Create a new table name for its users and add some value to it.
CREATE TABLE `users` (
`userId` int(8) NOT NULL,
`userName` varchar(55) NOT NULL,
`password` varchar(55) NOT NULL,
`firstName` varchar(55) NOT NULL,
`lastName` varchar(55) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Step 2: Create config.php File.
<?php
// Enter your Host, username, password, database below.
// I left password empty because i do not set password on localhost.
$connect=mysqli_connect('localhost','root','','abcd') or die("connection failed : ".mysqli_connect_error());
if ($connect) {
//echo "Connection Successfully";
}
else{
echo "Sorry Some Mistakes is";
}
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>Step 3: Create index.php File.
<?php
require_once "config.php";
$result = mysqli_query($connect, "SELECT * FROM users");
?>
<?php
if (isset($_POST['submit'])) {
$userName=$_POST['userName'];
$password=$_POST['password'];
$firstName=$_POST['firstName'];
$lastName=$_POST['lastName'];
$sql="INSERT INTO `users`( `userName`, `password`, `firstName`, `lastName`) VALUES ('$userName','$password','$firstName','$lastName')";
$con=mysqli_query($connect,$sql);
if ($con) {
echo "<script>alert('Insert Data');</script>";
}else{
echo "error";
}
}
?>
<html>
<head>
<title>Insert Data</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
<script language="javascript" src="users.js" type="text/javascript"></script>
</head>
<body>
<div class="insert">
<h1>Insert Your Data</h1>
<form method="post" action="#">
<table border="1" cellpadding="5" cellspacing="1" class="tblListForm">
<tr>
<td >Enter Your UserName</td>
<td><input type="text" name="userName"></td>
</tr>
<tr>
<td >Enter Your Password</td>
<td><input type="text" name="password"></td>
</tr>
<tr>
<td >Enter Your FirstName</td>
<td><input type="text" name="firstName"></td>
</tr>
<tr>
<td >Enter Your LastName</td>
<td><input type="text" name="lastName"></td>
</tr>
<tr style="background: yellow; text-align: center;">
<td colspan="2"><input type="submit" name="submit" ></td>
</tr>
</table>
</form>
<button><a href="show_data.php">Show Your Data</a></button>
</div>
</body></html>Step 4: Create show_data.php File.
For each user row, we will use checkboxes here and show our data
<?php
require_once "config.php";
$result = mysqli_query($connect, "SELECT * FROM users");
?>
<html>
<head>
<title>Users Show Data List</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
<script language="javascript" src="users.js" type="text/javascript"></script>
</head>
<body>
<div class="show">
<h1>Show your DATA</h1>
<form name="frmUser" method="post" action="">
<table border="1" cellpadding="5" cellspacing="1" class="tblListForm">
<tr class="listheader">
<td></td>
<td>Username</td>
<td>First Name</td>
<td>Last Name</td>
</tr>
<?php
$i=1;
while($row = mysqli_fetch_array($result)) {
if($i%2==0)
$classname="evenRow";
else
$classname="oddRow";
?>
<tr class="<?php if(isset($classname)) echo $classname;?>">
<td><input type="checkbox" name="users[]" value="<?php echo $row["userId"]; ?>" ></td>
<td><?php echo $row["userName"]; ?></td>
<td><?php echo $row["firstName"]; ?></td>
<td><?php echo $row["lastName"]; ?></td>
</tr>
<?php
$i++;
}
?>
<tr class="listheader">
<td colspan="5"><input type="button" name="update" value="Update" onClick="setUpdateAction();" /> <input type="button" name="delete" value="Delete" onClick="setDeleteAction();" /></td>
</tr>
</table>
</form>
</div>
</body>
</html>Step 5: Create edit_user.php File.
<?php
require_once "config.php";
if(isset($_POST["submit"]) && $_POST["submit"]!="") {
$usersCount = count($_POST["userName"]);
for($i=0;$i<$usersCount;$i++) {
mysqli_query($connect, "UPDATE users set userName='" . $_POST["userName"][$i] . "', password='" . $_POST["password"][$i] . "', firstName='" . $_POST["firstName"][$i] . "', lastName='" . $_POST["lastName"][$i] . "' WHERE userId='" . $_POST["userId"][$i] . "'");
}
header("Location:show_data.php");
}
?>
<html>
<head>
<title>Edit Multiple User</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<form name="frmUser" method="post" action="">
<div style="margin-top: 15px;">
<table border="1" cellpadding="10" cellspacing="0" align="center">
<tr class="tableheader">
<td>Edit User</td>
</tr>
<?php
$rowCount = count($_POST["users"]);
for($i=0;$i<$rowCount;$i++) {
$result = mysqli_query($connect, "SELECT * FROM users WHERE userId='" . $_POST["users"][$i] . "'");
$row[$i]= mysqli_fetch_array($result);
?>
<tr>
<td>
<table border="0" cellpadding="10" cellspacing="0" align="center" class="tblSaveForm">
<tr>
<td><label>Username</label></td>
<td><input type="hidden" name="userId[]" class="txtField" value="<?php echo $row[$i]['userId']; ?>"><input type="text" name="userName[]" class="txtField" value="<?php echo $row[$i]['userName']; ?>"></td>
</tr>
<tr>
<td><label>Password</label></td>
<td><input type="password" name="password[]" class="txtField" value="<?php echo $row[$i]['password']; ?>"></td>
</tr>
<td><label>First Name</label></td>
<td><input type="text" name="firstName[]" class="txtField" value="<?php echo $row[$i]['firstName']; ?>"></td>
</tr>
<td><label>Last Name</label></td>
<td><input type="text" name="lastName[]" class="txtField" value="<?php echo $row[$i]['lastName']; ?>"></td>
</tr>
</table>
</td>
</tr>
<?php
}
?>
<tr>
<td colspan="2"><input type="submit" name="submit" value="Submit" class="btnSubmit"></td>
</tr>
</table>
</div>
</form>
</body></html>Step 6: Create delete_user.php File.
<?php
require_once "config.php";
$rowCount = count($_POST["users"]);
for($i=0;$i<$rowCount;$i++) {
mysqli_query($connect, "DELETE FROM users WHERE userId='" . $_POST["users"][$i] . "'");
}
header("Location:show_data.php");
?>Step 7: Create users.js File.
function setUpdateAction() {
document.frmUser.action = "edit_user.php";
document.frmUser.submit();
}
function setDeleteAction() {
if(confirm("Are you sure want to delete these rows?")) {
document.frmUser.action = "delete_user.php";
document.frmUser.submit();
}
}Step 8: Create a style.css File.
To make our UI design more attractive we will create a style.css file here and write the style code in it.
body {
font-family:Arial;
margin: 0;
padding: 0;
}
.insert{
text-align: center;
}
.insert
table{
margin: auto;
}
.insert h1{
text-transform: uppercase;
color: #000f65;
font-size: 25px;
}
.show{
text-align: center;
}
.show table{
margin: auto;
}
.show
h1{
text-transform: uppercase;
color: #000fff;
font-size: 25px;
}
input {
font-family:Arial;
font-size:14px;
}
label{
font-family:Arial;
font-size:14px;
color:#999999;
}
.tblSaveForm {
border-top:2px #999999 solid;
background-color: #f8f8f8;
}
.tableheader {
background-color: yellow;
}
.tablerow {
background-color: #A7D6F1;
color:white;
}
.btnSubmit {
background-color:#fd9512;
padding:5px;
border-color:#FF6600;
border-radius:4px;
color:white;
}
.message {
color: #FF0000;
text-align: center;
width: 100%;
}
.txtField {
padding: 5px;
border:#fedc4d 1px solid;
border-radius:4px;
}
.evenRow {
background-color: #E2EDF9;
font-size:12px;
color:#101010;
}
.evenRow:hover {
background-color: #ffef46;
}
.oddRow {
background-color: #B3E8FF;
font-size:12px;
color:#101010;
}
.oddRow:hover {
background-color: #ffef46;
}
.tblListForm {
border-top:2px #999999 solid;
}
.listheader {
background-color: yellow;
font-size:12px;
font-weight:bold;
}
