Today, you will learn How to Update Data into Database Using PHP/MySQLi. This method will show you on how to Update Data into Database with the use of update query statement in PHP and MySQLi.
Below are following step by step guide on how to Update Data Into Database Using PHP and MySQLi.
Lets Begin:
First Step: Create a MySQL Database and name it “userdb“.
Second: Do the following code for creating a table in the database that you have created.
[mysql]
CREATE TABLE IF NOT EXISTS `tbluser` (
`UserID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(30) NOT NULL,
`Username` varchar(30) NOT NULL,
`Pass` varchar(90) NOT NULL,
PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
[/mysql]
Third Step:Insert the data using the following code below.
[mysql]
INSERT INTO `tbluser` (`UserID`, `Name`, `Username`, `Pass`) VALUES
(1, 'Janno Palacios', 'janobe', 'admin'),
(2, 'Joken villanueva', 'joken', 'joken'),
(3, 'kejie palacios', 'kenjie', 'kenjie');
[/mysql]
Fourth Step: Create a CSS file for the layout of the page and name it “style.css“.
[css]
#box{
width: 100%;
}
.column {
width: 33.33%;
display: inline-block;
}
.form{
padding: 2px;
width: 100%;
display: inline-block;
}
.first-column {
display: inline-block;
width: 100px;
height: 2px;
margin: 2px;
position: inherit;
}
.second-column{
display: inline-block;
width: 150px;
height: 2px;
margin: 2px;
position: inherit;
}
.btn{
width: 50px;
height: 40px;
margin: 2px;
float: left;
}
.clear{
clear: left;
height: 15px;
}
.table{
width: 100%;
border: solid 1px #ddd;
}
.table tr,
.table td {
position: inherit;
border: solid 1px #ddd;
}
[/css]
Fifth Step: Create a landing page and name it “index.php“.
Sixth Step: Do the following code for retrieving data in the database.
[php]
<html>
<title>Update Data into Database Using PHP</title>
<head>
<a href="http://style.css">http://style.css</a>
<h1 align="center">List of Users</h1>
</head>
<?php
$server="localhost";
$userid ="root";
$Password = "";
$myDB = "userdb";
$con = mysqli_connect($server,$userid,$Password,$myDB);
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
<body>
<div id="box">
<div class="column"></div>
<div class="column">
$sqli="SELECT * FROM tbluser";
$result=mysqli_query($con,$sqli) or die("query error");
while($row=mysqli_fetch_array($result)) {
echo '
';
echo '
';
echo '
';
echo '
';
echo '
';
echo '
';
}
}
?>
<table class="table">
<tbody>
<tr>
<td>User ID</td>
<td>Name</td>
<td>Username</td>
<td>Action</td>
</tr>
<tr>
<td>'.$row['UserID'].'</td>
<td>'.$row['Name'].'</td>
<td>'.$row['Username'].'</td>
<td><a href="edit.php?id='.$row['UserID'].'">Edit</a></td>
</tr>
</tbody>
</table>
</div>
<div class="column"></div>
</div>
</body>
</html>
<?php mysqli_close($con); ?>[/php]
Seventh Step: Do the following code for filling the corresponding data into the text field.
[php]
<?php
$server="localhost";
$userid ="root";
$Password = "";
$myDB = "userdb";
$con = mysqli_connect($server,$userid,$Password,$myDB);
if (mysqli_connect_errno()) {
# code...
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
if (isset($_GET['id'])) {
# code...
$sqli = "SELECT * FROM tbluser WHERE UserID=".$_GET['id'];
$result = mysqli_query($con, $sqli);
$details = mysqli_fetch_assoc($result);
}else{
header('Location: index.php');
}
?>
<a href="http://style.css">http://style.css</a>
<h1 align="center">Update Users</h1>
<div id="box">
<div class="column"></div>
<div class="column">
<form class="form" action="edit.php " method="POST">
<label class="first-column ">User ID:</label>
" >
<div class="clear"></div>
<label class="first-column ">Name:</label>
<input class="second-column" type="text" name="name" value="<?php echo $details['Name']; ?>" >
<div class="clear"></div>
<label class="first-column">Username:</label>
<input class="second-column" type="text" name="username" value="<?php echo $details['Username']; ?>" >
<div class="clear"></div>
<label class="first-column ">Password:</label>
<input class="second-column" type="password" name="pass" value="<?php echo $details['Pass']; ?>" >
<div class="clear"></div>
<div class="first-column "></div>
<button class="btn second-column" name="save" type="submit">Save</button>
</form>
<h1 align="center">List of Users</h1>
<table class="table" >
<tr>
<td>User ID</td>
<td>Name</td>
<td>Username</td>
<td>Action</td>
</tr>
<?php list(); ?>
</table>
</div>
<div class="column"></div>
</div>
<?php
function list(){
$sqli = "SELECT * FROM `tbluser`";
$result = mysqli_query($con,$sqli) or die("query error");
if ($result) {
# code...
while ($row = mysqli_fetch_array($result)) {
# code...
echo '<tr>';
echo '<td>'.$row['UserID'].'</td>';
echo '<td>'.$row['Name'].'</td>';
echo '<td>'.$row['Username'].'</td>';
echo '<td><a href="edit.php?id='.$row['UserID'].'">Edit</a>
</td>';
echo '</tr>';
}
}
}
mysqli_close($con);
?>
[/php]
Eight Step: Do the following code for updating data in the database.
[php]
<?php
if (isset($_POST['save'])) {
# code...
$id = $_POST['UserID'];
$name = $_POST['name'];
$username = $_POST['username'];
$pass = $_POST['pass'];
$sqli = "UPDATE `tbluser` SET `Name`='{$name}', `Username`='{$username}', `Pass`='{$pass}' WHERE `UserID`='{$id}'";
$res = mysqli_query($con,$sqli);
if ($res) {
# code...
echo "Data has been updated in the database.";
header('Location: index.php');
}
}
?>
[/php]
If you have any questions or suggestion about how to Update Data Into Database Using PHP and MySQLi, please feel free to contact us at our contact page.You can subscribe this site to see more of my tutorials.