This article explores how to perform basic UPDATE operations using MySQLi, covering both the object-oriented and procedural approaches. We will also discuss the importance of prepared statements for secure data updates and best practices for updating data in your database.
Prerequisites
Before we begin, ensure you have the following:
1. A web server with PHP installed (such as Apache or Nginx).
2. MySQL server installed and running.
3. Basic knowledge of PHP and MySQL.
4. A sample database and table to work with.
For demonstration purposes, let’s assume we have a database named test_db with a table users:
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_doe', 'jane@example.com');
Basic UPDATE Queries Using MySQLi
1. Object-Oriented Approach
Step 1: Establish a Connection
First, establish a connection to the MySQL database. Create a config.php file to store your database credentials:
<?php
// config.php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_NAME', 'test_db');
?>
Include this configuration file and create a connection in your script:
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
?>
Step 2: Perform a Basic UPDATE Query
To update data in the users table, use the following code:
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "UPDATE users SET email = 'john_new@example.com' WHERE username = 'john_doe'";
if ($mysqli->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $mysqli->error;
}
$mysqli->close();
?>
Explanation
$sql: The SQL query string for updating data.
$mysqli->query($sql): Executes the query.
$mysqli->error: Returns the error description if the query fails.
2. Procedural Approach
Step 1: Establish a Connection
Create a connection using mysqli_connect():
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>
Step 2: Perform a Basic UPDATE Query
Use mysqli_query() to execute the query:
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "UPDATE users SET email = 'jane_new@example.com' WHERE username = 'jane_doe'";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "ERROR: Could not execute $sql. " . mysqli_error($conn);
}
mysqli_close($conn);
?>
3. Using Prepared Statements
Prepared statements are essential for preventing SQL injection, especially when dealing with user input.
Object-Oriented Approach
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$stmt = $mysqli->prepare("UPDATE users SET email = ? WHERE username = ?");
$stmt->bind_param("ss", $new_email, $username);
$new_email = 'john_updated@example.com';
$username = 'john_doe';
$stmt->execute();
if ($stmt->affected_rows > 0) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $stmt->error;
}
$stmt->close();
$mysqli->close();
?>
Procedural Approach
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "UPDATE users SET email = ? WHERE username = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $new_email, $username);
$new_email = 'jane_updated@example.com';
$username = 'jane_doe';
mysqli_stmt_execute($stmt);
if (mysqli_stmt_affected_rows($stmt) > 0) {
echo "Record updated successfully";
} else {
echo "ERROR: Could not execute query. " . mysqli_stmt_error($stmt);
}
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>
Best Practices for UPDATE Queries
Use Prepared Statements: Always use prepared statements to protect against SQL injection.
Check Connection Errors: Ensure your script properly handles connection errors.
Close Connections: Always close your database connections to free up resources.
Validate and Sanitize Inputs: Always validate and sanitize user inputs before using them in your queries.
Check Affected Rows: Use the affected_rows property or function to verify that the update was successful.
Updating Multiple Columns
You can update multiple columns in a single query by specifying additional column-value pairs.
Object-Oriented Approach
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "UPDATE users SET email = 'john_new@example.com', username = 'john_new' WHERE id = 1";
if ($mysqli->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $mysqli->error;
}
$mysqli->close();
?>
Procedural Approach
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "UPDATE users SET email = 'jane_new@example.com', username = 'jane_new' WHERE id = 2";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "ERROR: Could not execute $sql. " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Using Transactions
For critical operations, use transactions to ensure that your updates are performed reliably.
Object-Oriented Approach
<?php
require_once 'config.php';
$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$mysqli->begin_transaction();
try {
$sql1 = "UPDATE users SET email = 'john_new@example.com' WHERE id = 1";
$mysqli->query($sql1);
$sql2 = "UPDATE users SET email = 'jane_new@example.com' WHERE id = 2";
$mysqli->query($sql2);
$mysqli->commit();
echo "Transaction completed successfully";
} catch (Exception $e) {
$mysqli->rollback();
echo "Transaction failed: " . $e->getMessage();
}
$mysqli->close();
?>
Procedural Approach
<?php
require_once 'config.php';
$conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
if ($conn === false) {
die("ERROR: Could not connect. " . mysqli_connect_error());
}
mysqli_begin_transaction($conn);
try {
$sql1 = "UPDATE users SET email = 'john_new@example.com' WHERE id = 1";
mysqli_query($conn, $sql1);
$sql2 = "UPDATE users SET email = 'jane_new@example.com' WHERE id = 2";
mysqli_query($conn, $sql2);
mysqli_commit($conn);
echo "Transaction completed successfully";
} catch (Exception $e) {
mysqli_rollback($conn);
echo "Transaction failed: " . $e->getMessage();
}
mysqli_close($conn);
?>
Conclusion
Performing UPDATE operations in MySQLi is a fundamental task in PHP development. By following the examples and best practices outlined in this article, you can efficiently and securely update data in your MySQL database. Whether you prefer the object-oriented or procedural approach, MySQLi provides the tools you need to interact with your database effectively. Remember to use prepared statements to safeguard your application against SQL injection and always handle connection errors gracefully. Additionally, closing your database connections, validating user inputs, and using transactions for critical operations are essential practices for maintaining the performance and security of your application.