Table of Contents:
PHP MySQL Update Data – Complete Beginner Tutorial
Updating data in a MySQL database using PHP is essential for building edit profiles, update settings, admin panels, and dynamic web applications. Once data is inserted, users often need the ability to modify it.
This tutorial explains how to update data in MySQL using PHP in a clear, step-by-step manner.
What Does Update Data Mean in PHP MySQL?
Updating data means modifying existing records in a MySQL table using the SQL UPDATE statement.
Basic SQL syntax:
UPDATE table_name SET column_name = value WHERE condition;
The WHERE clause is important because it specifies which record(s) should be updated.
Prerequisites for PHP MySQL Update Tutorial
Before updating data, you should have:
- PHP installed
- MySQL database created
- A working database connection
- A table with existing records
Sample MySQL Table Used in This Tutorial
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
);
PHP MySQL Database Connection Code
<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");
if (!$conn) {
die("Database connection failed");
}
?>
Simple PHP MySQL Update Query Example
<?php
$query = "UPDATE users SET age = 28 WHERE id = 1";
if (mysqli_query($conn, $query)) {
echo "Record updated successfully";
} else {
echo "Update failed";
}
?>
Output:
Record updated successfully
Update Multiple Columns in PHP MySQL
<?php
$query = "UPDATE users SET name='Rahul Sharma', age=26 WHERE id=1";
if (mysqli_query($conn, $query)) {
echo "User details updated";
}
?>
Output:
User details updated
Update Data Using PHP Variables
<?php
$id = 2;
$name = "Amit Kumar";
$age = 32;
$query = "UPDATE users SET name='$name', age=$age WHERE id=$id";
mysqli_query($conn, $query);
echo "Data updated";
?>
Output:
Data updated
Update Form Data in MySQL Using PHP
Updating data using a form is one of the most common real-world use cases.
HTML Form for Updating Data
<form method="post">
Name: <input type="text" name="name"><br>
Age: <input type="number" name="age"><br>
<input type="submit" name="update">
</form>
PHP Update Script
<?php
if (isset($_POST['update'])) {
$name = $_POST['name'];
$age = $_POST['age'];
$query = "UPDATE users SET name='$name', age=$age WHERE id=1";
if (mysqli_query($conn, $query)) {
echo "Form data updated successfully";
}
}
?>
Output:
Form data updated successfully
Secure PHP MySQL Update Using Prepared Statements
Prepared statements prevent SQL injection attacks and improve security.
<?php
$stmt = mysqli_prepare($conn, "UPDATE users SET name=?, age=? WHERE id=?");
mysqli_stmt_bind_param($stmt, "sii", $name, $age, $id);
$name = "Neha";
$age = 23;
$id = 3;
mysqli_stmt_execute($stmt);
echo "Data updated securely";
?>
Output:
Data updated securely
Update Data Without WHERE Clause (Warning)
UPDATE users SET age = 30;
This updates all records in the table.
Always use a WHERE clause unless intentionally updating every row.
PHP MySQL update data, PHP update record MySQL, PHP MySQL UPDATE query, PHP MySQLi update example, PHP form update MySQL, PHP prepared statement update, PHP database tutorial
Common Errors While Updating Data in PHP MySQL
- Forgetting the WHERE clause
- Incorrect column names
- Invalid data types
- Missing quotes for string values
- Database connection issues
Best Practices for PHP MySQL Update Operations
- Always use WHERE clause carefully
- Use prepared statements
- Validate and sanitize user input
- Check affected rows
- Keep backup before bulk updates
This tutorial help you with:
Frequently Asked Questions (FAQ)
1. What does UPDATE do in MySQL?
It modifies existing records in a table.
2. Can I update multiple rows at once?
Yes, by using conditions in the WHERE clause.
3. Is WHERE clause mandatory?
Not mandatory, but highly recommended.
4. How do I know if update was successful?
Check query result or affected rows.
5. Is prepared UPDATE safer?
Yes, it prevents SQL injection.