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.
Secure UPDATE with Prepared Statements (PDO)
The legacy way of building UPDATE queries by concatenating $_POST values into a string is a SQL injection bug waiting to happen. Modern PHP code (2026) uses prepared statements with PDO or MySQLi. Here is the PDO version:
<?php
$dsn = "mysql:host=localhost;dbname=school;charset=utf8mb4";
$user = "root";
$pass = "";
try {
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]);
$sql = "UPDATE students
SET first_name = :first, last_name = :last, course = :course
WHERE student_id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':first' => $_POST['first_name'],
':last' => $_POST['last_name'],
':course' => $_POST['course'],
':id' => $_POST['student_id'],
]);
echo "Student record updated. Rows affected: " . $stmt->rowCount();
} catch (PDOException $e) {
error_log($e->getMessage());
echo "Update failed. Please try again.";
}
?>The :placeholder syntax keeps user input completely separate from the SQL structure. Even if someone enters '; DROP TABLE students;-- as their first name, it is treated as a literal string, not as SQL code.
Secure UPDATE with MySQLi (Alternative)
<?php
$mysqli = new mysqli("localhost", "root", "", "school");
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "UPDATE students SET first_name = ?, last_name = ?, course = ?
WHERE student_id = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("sssi",
$_POST['first_name'],
$_POST['last_name'],
$_POST['course'],
$_POST['student_id']
);
$stmt->execute();
echo $stmt->affected_rows . " row(s) updated.";
$stmt->close();
$mysqli->close();
?>The "sssi" string tells MySQLi the parameter types: three strings (s) and one integer (i). Get this wrong and you get unexpected NULL or zero values.
Updating Multiple Columns Conditionally
Sometimes you only want to update certain columns if the form provided new values. Build the SQL dynamically (still safely):
<?php
$updates = [];
$params = [];
if (!empty($_POST['first_name'])) {
$updates[] = "first_name = :first";
$params[':first'] = $_POST['first_name'];
}
if (!empty($_POST['last_name'])) {
$updates[] = "last_name = :last";
$params[':last'] = $_POST['last_name'];
}
if (!empty($_POST['course'])) {
$updates[] = "course = :course";
$params[':course'] = $_POST['course'];
}
if (!empty($updates)) {
$sql = "UPDATE students SET " . implode(", ", $updates) .
" WHERE student_id = :id";
$params[':id'] = $_POST['student_id'];
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
}
?>Notice the column names are HARDCODED in PHP. Never let user input determine column names — that would be SQL injection by a different door.
Common UPDATE Mistakes in PHP
- String concatenation with
$_POST."UPDATE x SET y = '" . $_POST['y'] . "'"is SQL injection. Always use prepared statements. - No WHERE clause.
UPDATE students SET course = 'BSIT'updates EVERY row in the table. Always include a WHERE clause unless you really mean to bulk-update everyone. - Echoing
$pdo->errorInfo()to users. Database errors expose your schema. Log to a file witherror_log(), show users a generic message. - Forgetting to check
rowCount(). An UPDATE with a WHERE that matches no rows succeeds but updates nothing. Always verify$stmt->rowCount() > 0before showing success. - Using
md5()for passwords. MD5 was broken decades ago. For password fields, usepassword_hash($pw, PASSWORD_BCRYPT)for storage andpassword_verify()for login. - Not using transactions for multi-table updates. If updating both
studentsandenrollmentsand the second update fails, you have inconsistent data. Wrap in$pdo->beginTransaction()/commit().
Frequently Asked Questions
How do I update data in a MySQL database using PHP?
Use PDO or MySQLi with prepared statements. The pattern is: connect to the database, prepare an UPDATE query with placeholders (:name for PDO or ? for MySQLi), execute with user input as parameters, and check rowCount() or affected_rows to confirm the update worked. Never concatenate user input directly into SQL strings.
What’s the difference between PDO and MySQLi for updating data?
PDO works with any database (MySQL, PostgreSQL, SQLite) using the same API, and uses named placeholders like :name. MySQLi is MySQL-only and uses positional ? placeholders plus an explicit type string. For BSIT capstone projects, PDO is recommended because it’s more portable and the named placeholders are easier to read. Both are equally secure when used with prepared statements.
How do I prevent SQL injection in PHP UPDATE queries?
Use prepared statements. With PDO: $pdo->prepare("UPDATE x SET y = :y WHERE id = :id"); $stmt->execute([':y' => $value, ':id' => $id]);. Never concatenate $_POST or $_GET values into the SQL string. Even if you trust the source, prepared statements are easier to read and faster (the prepared SQL gets cached by MySQL).
How do I know if my PHP UPDATE actually changed any rows?
Check the return of rowCount() (PDO) or the affected_rows property (MySQLi). If the value is 0, either the WHERE clause matched no rows, or the new values were the same as the existing ones (MySQL skips no-op updates). For most capstone forms, treat 0 as “record not found” and show the user an appropriate message.
Should I use PHP’s mysql_query() function in 2026?
No. The old mysql_* functions were removed in PHP 7.0 (released 2015). Modern PHP uses either PDO or MySQLi. If you find tutorial code using mysql_query() or mysql_connect(), the tutorial is at least 10 years out of date. Convert to PDO or MySQLi with prepared statements before submitting your BSIT capstone.