PHP MySQL Update Data

PHP MySQL Update Data Tutorial

In PHP MySQL Update Data is using to modify existing records in a MySQL table. You can update one or more columns and filter which rows to update using the WHERE clause.

Without a WHERE clause, all rows will be updated.

Using `mysqli` (Procedural)

<?php
$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Update a specific record
$sql = "UPDATE users SET age=29 WHERE name='Vipul'";

if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

<?php

$conn = mysqli_connect("localhost", "root", "", "test_db");

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

// Update a specific record

$sql = "UPDATE users SET age=29 WHERE name='Vipul'";

if (mysqli_query($conn, $sql)) {

echo "Record updated successfully";

} else {

echo "Error updating record: " . mysqli_error($conn);

}

mysqli_close($conn);

Updates the age of the user named Vipul to 29.

Using `mysqli` Object-Oriented

<?php
$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Update record
$sql = "UPDATE users SET email='vipul123@example.com' WHERE id=1";

if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

<?php

$conn = new mysqli("localhost", "root", "", "test_db");

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// Update record

$sql = "UPDATE users SET email='vipul123@example.com' WHERE id=1";

if ($conn->query($sql) === TRUE) {

echo "Record updated successfully";

} else {

echo "Error updating record: " . $conn->error;

}

$conn->close();

$conn->query() executes the SQL statement in object-oriented style.

Using PDO with Prepared Statements

<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare statement
$stmt = $conn->prepare("UPDATE users SET age=:age, email=:email WHERE name=:name");

// Bind parameters
$stmt->bindParam(':age', $age);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':name', $name);

// Set values and execute
$age = 30;
$email = "vipul.updated@example.com";
$name = "Vipul";
$stmt->execute();

echo "Record updated successfully";

} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

$conn = null;
?>

<?php

try {

$conn = new PDO("mysql:host=localhost;dbname=test_db", "root", "");

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare statement

$stmt = $conn->prepare("UPDATE users SET age=:age, email=:email WHERE name=:name");

// Bind parameters

$stmt->bindParam(':age', $age);

$stmt->bindParam(':email', $email);

$stmt->bindParam(':name', $name);

// Set values and execute

$age = 30;

$email = "vipul.updated@example.com";

$name = "Vipul";

$stmt->execute();

echo "Record updated successfully";

} catch(PDOException $e) {

echo "Error: " . $e->getMessage();

}

$conn = null;

Using prepared statements prevents SQL injection and allows dynamic updates.

Update Multiple Columns

Updates name, age, and email for a specific record.

Key Points

Use UPDATE table_name SET column=value to modify data.
Always include a WHERE clause to avoid updating all rows.
Prepared statements are safer for user input.
Can update single or multiple columns at once.
Check for errors using mysqli_error(), $conn->error, or PDOException.

PHP MySQL Update Data