Advertisement
Google Ad Slot: content-top
MySQL Update Data
Updating data in a MySQL table using PHP involves executing an SQL UPDATE statement through either procedural or object-oriented approaches.
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
table_name: The name of the table (e.g.,users).SET: Specifies the columns and their new values.WHERE: Limits which rows are updated (use it carefully to avoid updating all rows).
The following example updates the name and age of the user with id=1.
Example (Procedural Method)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase"; // Change to your database name
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Update query
$sql = "UPDATE users SET name='John Doe', email='johndoe@example.com' WHERE id=1";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}
// Close connection
mysqli_close($conn);
?>
Example (Object-Oriented Method)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Update query
$sql = "UPDATE users SET name='John Doe', email='johndoe@example.com' WHERE id=1";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
// Close connection
$conn->close();
?>
Example (PDO)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydatabase";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// Set PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Update query with placeholders
$sql = "UPDATE users SET name=:name, email=:email WHERE id=:id";
// Prepare statement
$stmt = $conn->prepare($sql);
// Bind parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':id', $id);
// Values to update
$name = "John Doe";
$email = "johndoe@example.com";
$id = 1;
// Execute update
$stmt->execute();
echo "Record updated successfully";
} catch (PDOException $e) {
echo "Error updating record: " . $e->getMessage();
}
// Close connection
$conn = null;
?>