This article will explore the execution of prepared statements in MySQLi, covering various execution methods and providing detailed examples to illustrate their usage.
Why Use Prepared Statements?
- Security: Prepared statements prevent SQL injection attacks by ensuring that user input is treated as data, not executable code.
- Performance: Once a prepared statement is created, it can be executed multiple times with different parameters, reducing the overhead of parsing and compiling SQL queries repeatedly.
- Maintainability: Prepared statements make the code cleaner and easier to maintain by separating SQL logic from data processing.
Key Concepts in Executing Prepared Statements
- Preparation: The SQL query template with placeholders (?) for parameters is prepared.
- Binding: Parameters are bound to the placeholders.
- Execution: The prepared statement is executed with the bound parameters.
Preparing the Environment
Before we dive into executing prepared statements, let’s set up a sample environment. Assume you have a MySQL database and a users table:
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
age INT NOT NULL,
email VARCHAR(100) NOT NULL
);
Connecting to the Database
First, establish a connection to the MySQL database using the MySQLi extension in PHP.
<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "example_db";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
Executing Prepared Statements
Let’s explore different methods of executing prepared statements in MySQLi.
Inserting Data
To insert data into the users table, follow these steps:
- Prepare the SQL statement with placeholders.
- Bind the parameters to the placeholders.
- Execute the statement.
<?php
// Prepare an insert statement
$stmt = $conn->prepare("INSERT INTO users (username, age, email) VALUES (?, ?, ?)");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$username = "john_doe";
$age = 30;
$email = "john@example.com";
$stmt->bind_param("sis", $username, $age, $email); // "sis" denotes string, integer, string
// Execute the statement
if ($stmt->execute()) {
echo "New record created successfully";
} else {
echo "Execute failed: " . $stmt->error;
}
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Updating Data
To update a user’s email based on their username:
<?php
// Prepare an update statement
$stmt = $conn->prepare("UPDATE users SET email = ? WHERE username = ?");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$new_email = "john.new@example.com";
$username = "john_doe";
$stmt->bind_param("ss", $new_email, $username); // "ss" denotes two strings
// Execute the statement
if ($stmt->execute()) {
echo "Record updated successfully";
} else {
echo "Execute failed: " . $stmt->error;
}
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Selecting Data
To retrieve data, you need to bind the result variables and fetch the results:
<?php
// Prepare a select statement
$stmt = $conn->prepare("SELECT id, username, age, email FROM users WHERE username = ?");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$username = "john_doe";
$stmt->bind_param("s", $username); // "s" denotes string
// Execute the statement
$stmt->execute();
// Bind the result variables
$stmt->bind_result($id, $username, $age, $email);
// Fetch the results
if ($stmt->fetch()) {
echo "ID: $id, Username: $username, Age: $age, Email: $email";
} else {
echo "No results found";
}
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Deleting Data
To delete a user record:
<?php
// Prepare a delete statement
$stmt = $conn->prepare("DELETE FROM users WHERE username = ?");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$username = "john_doe";
$stmt->bind_param("s", $username); // "s" denotes string
// Execute the statement
if ($stmt->execute()) {
echo "Record deleted successfully";
} else {
echo "Execute failed: " . $stmt->error;
}
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Executing Prepared Statements with Multiple Rows
When dealing with select queries that return multiple rows, use a loop to fetch all results:
<?php
// Prepare a select statement
$stmt = $conn->prepare("SELECT id, username, age, email FROM users WHERE age > ?");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$min_age = 25;
$stmt->bind_param("i", $min_age); // "i" denotes integer
// Execute the statement
$stmt->execute();
// Bind the result variables
$stmt->bind_result($id, $username, $age, $email);
// Fetch the results
while ($stmt->fetch()) {
echo "ID: $id, Username: $username, Age: $age, Email: $email\n";
}
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Handling Errors and Best Practices
- Error Checking: Always check if the statement preparation and execution were successful.
- Parameter Binding: Ensure you use the correct data types when binding parameters.
- Closing Statements: Always close the prepared statements to free up resources.
- Reusing Statements: You can prepare a statement once and execute it multiple times with different parameters, which enhances performance.
- Debugging: Use $stmt->error to get detailed error messages if something goes wrong.
Example: Inserting Multiple Records Efficiently
Suppose you need to insert multiple records efficiently. Instead of preparing the statement each time, prepare it once and execute it multiple times:
<?php
// Assume $conn is already created and connected
// Prepare an insert statement
$stmt = $conn->prepare("INSERT INTO users (username, age, email) VALUES (?, ?, ?)");
// Check if the statement was prepared successfully
if ($stmt === false) {
die("Prepare failed: " . $conn->error);
}
// Bind parameters to the statement
$stmt->bind_param("sis", $username, $age, $email); // "sis" denotes string, integer, string
// Array of user data to insert
$users = [
["alice", 28, "alice@example.com"],
["bob", 35, "bob@example.com"],
["charlie", 22, "charlie@example.com"]
];
// Loop through the user data and execute the statement for each user
foreach ($users as $user) {
list($username, $age, $email) = $user;
if ($stmt->execute()) {
echo "New record for $username created successfully\n";
} else {
echo "Execute failed for $username: " . $stmt->error . "\n";
}
}
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Conclusion
Executing prepared statements in MySQLi is a robust method for interacting with a MySQL database in PHP. By separating the SQL logic from the data, you enhance the security, performance, and maintainability of your code. Whether you are inserting, updating, selecting, or deleting data, prepared statements provide a reliable way to handle database operations. Remember to follow best practices such as error checking, proper parameter binding, and resource management to ensure your applications are efficient and secure.