This article provides a comprehensive guide to creating prepared statements in MySQLi, including syntax and practical examples.
What Are Prepared Statements?
Prepared statements in MySQLi involve two main steps: preparing the SQL statement and executing it. During the preparation phase, the SQL statement template is sent to the database server where it is parsed and compiled. In the execution phase, the prepared statement is executed with specific parameter values.Benefits of Prepared Statements
- Security: Prevents SQL injection by separating SQL logic from data.
- Performance: Reduces parsing and compilation overhead by reusing the prepared statement.
- Maintainability: Leads to cleaner and more readable code.
Syntax of Prepared Statements
Preparation
The prepare() method is used to create a prepared statement. It takes an SQL statement as a parameter and returns a mysqli_stmt object.
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
Binding Parameters
The bind_param() method binds variables to the parameter markers (?) in the SQL statement. It takes two arguments:- A string that specifies the types of the variables (e.g., s for string, i for integer).
- The variables themselves.
$stmt->bind_param("ss", $username, $email);
Executing the Statement
The execute() method executes the prepared statement with the bound parameters.
$stmt->execute();
Closing the Statement
The close() method is used to close the prepared statement when it is no longer needed.
$stmt->close();
Example: Inserting Data
Let’s create a complete example of inserting data into a users table using a prepared statement.
Step-by-Step Guide
1. Establish a Database Connection
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
2. Prepare the SQL Statement
<?php
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
if ($stmt === false) {
die("Error preparing statement: " . $conn->error);
}
?>
3. Bind Parameters
<?php
$username = "johndoe";
$email = "johndoe@example.com";
$stmt->bind_param("ss", $username, $email);
?>
4. Execute the Statement
<?php
if ($stmt->execute() === false) {
die("Error executing statement: " . $stmt->error);
} else {
echo "Record inserted successfully.";
}
?>
5. Close the Statement and Connection
<?php
$stmt->close();
$conn->close();
?>
Complete Example Code
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare the statement
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
if ($stmt === false) {
die("Error preparing statement: " . $conn->error);
}
// Bind parameters
$username = "johndoe";
$email = "johndoe@example.com";
$stmt->bind_param("ss", $username, $email);
// Execute the statement
if ($stmt->execute() === false) {
die("Error executing statement: " . $stmt->error);
} else {
echo "Record inserted successfully.";
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Example: Retrieving Data
Prepared statements can also be used for retrieving data. Let’s create an example to fetch data from the users table.Step-by-Step Guide
1. Establish a Database Connection
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
2. Prepare the SQL Statement
<?php
$stmt = $conn->prepare("SELECT id, username, email FROM users WHERE username = ?");
if ($stmt === false) {
die("Error preparing statement: " . $conn->error);
}
?>
3. Bind Parameters
<?php
$username = "johndoe";
$stmt->bind_param("s", $username);
?>
4. Execute the Statement
<?php
$stmt->execute();
?>
5. Bind Result Variables
<?php
$stmt->bind_result($id, $username, $email);
?>
6. Fetch Values
<?php
while ($stmt->fetch()) {
echo "ID: $id, Username: $username, Email: $email<br>";
}
?>
7. Close the Statement and Connection
<?php
$stmt->close();
$conn->close();
?>
Complete Example Code
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare the statement
$stmt = $conn->prepare("SELECT id, username, email FROM users WHERE username = ?");
if ($stmt === false) {
die("Error preparing statement: " . $conn->error);
}
// Bind parameters
$username = "johndoe";
$stmt->bind_param("s", $username);
// Execute the statement
$stmt->execute();
// Bind result variables
$stmt->bind_result($id, $username, $email);
// Fetch values
while ($stmt->fetch()) {
echo "ID: $id, Username: $username, Email: $email<br>";
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Example: Updating Data
Prepared statements can also be used for updating data in a table. Let’s create an example to update the email address of a user.
Step-by-Step Guide
1. Establish a Database Connection
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
2. Prepare the SQL Statement
<?php
$stmt = $conn->prepare("UPDATE users SET email = ? WHERE username = ?");
if ($stmt === false) {
die("Error preparing statement: " . $conn->error);
}
?>
3. Bind Parameters
<?php
$new_email = "newemail@example.com";
$username = "johndoe";
$stmt->bind_param("ss", $new_email, $username);
?>
4. Execute the Statement
<?php
if ($stmt->execute() === false) {
die("Error executing statement: " . $stmt->error);
} else {
echo "Record updated successfully.";
}
?>
5. Close the Statement and Connection
<?php
$stmt->close();
$conn->close();
?>
Complete Example Code
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare the statement
$stmt = $conn->prepare("UPDATE users SET email = ? WHERE username = ?");
if ($stmt === false) {
die("Error preparing statement: " . $conn->error);
}
// Bind parameters
$new_email = "newemail@example.com";
$username = "johndoe";
$stmt->bind_param("ss", $new_email, $username);
// Execute the statement
if ($stmt->execute() === false) {
die("Error executing statement: " . $stmt->error);
} else {
echo "Record updated successfully.";
}
// Close the statement and connection
$stmt->close();
$conn->close();
?>
Conclusion
Prepared statements in MySQLi provide a secure and efficient way to interact with databases. They prevent SQL injection by separating SQL logic from data and improve performance by reducing parsing and compilation overhead. This article has provided a detailed guide on creating prepared statements in MySQLi, covering the syntax and providing examples for inserting, retrieving, and updating data.
By integrating prepared statements into your PHP applications, you can ensure that your database interactions are secure, efficient, and maintainable. Start using prepared statements today to take advantage of their powerful features and enhance the overall quality of your code.