This article will explore various techniques for fetching results using prepared statements in MySQLi, with detailed examples to illustrate each method.
Why Use Prepared Statements?
- Security: Prepared statements prevent SQL injection by treating user inputs as data rather than executable SQL code.
- Performance: They allow the database to parse and compile the SQL query once and execute it multiple times with different parameters.
- Maintainability: Separating SQL logic from data makes the code cleaner and easier to manage.
Setting Up the Environment
Before diving into fetching results, 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
);
INSERT INTO users (username, age, email) VALUES
('john_doe', 30, 'john@example.com'),
('jane_doe', 25, 'jane@example.com'),
('alice', 28, 'alice@example.com'),
('bob', 35, 'bob@example.com');
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);
}
?>
Fetching Results Using Prepared Statements
Let’s explore different methods of fetching results from prepared statements in MySQLi.
Fetching a Single Row
To fetch a single row, use the fetch method. This is useful when you expect only one result, such as fetching a user by their unique username.
<?php
// Assume $conn is already created and connected
// 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 result
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();
?>
Fetching Multiple Rows
When dealing with queries that return multiple rows, use a loop to fetch all results. This is common when retrieving a list of users or any other dataset.
<?php
// Assume $conn is already created and connected
// 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 in a loop
while ($stmt->fetch()) {
echo "ID: $id, Username: $username, Age: $age, Email: $email\n";
}
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Fetching Results into an Array
Fetching results into an array can be more convenient for further processing. Use the get_result method, which requires the MySQL native driver (mysqlnd).
<?php
// Assume $conn is already created and connected
// Check if the MySQL native driver is available
if (!$conn->query("SELECT 1")) {
die("MySQL native driver not available.");
}
// 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();
// Get the result set
$result = $stmt->get_result();
// Fetch all results into an array
$users = $result->fetch_all(MYSQLI_ASSOC);
foreach ($users as $user) {
echo "ID: {$user['id']}, Username: {$user['username']}, Age: {$user['age']}, Email: {$user['email']}\n";
}
// Free the result set
$result->free();
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Advanced Fetching Techniques
Fetching with fetch_assoc()
The fetch_assoc method fetches a result row as an associative array, where the keys are the column names.
<?php
// Assume $conn is already created and connected
// 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();
// Get the result set
$result = $stmt->get_result();
// Fetch results row by row
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['id']}, Username: {$row['username']}, Age: {$row['age']}, Email: {$row['email']}\n";
}
// Free the result set
$result->free();
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Fetching with fetch_object()
The fetch_object method fetches a result row as an object, where the properties correspond to the column names.
<?php
// Assume $conn is already created and connected
// 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();
// Get the result set
$result = $stmt->get_result();
// Fetch results row by row
while ($row = $result->fetch_object()) {
echo "ID: {$row->id}, Username: {$row->username}, Age: {$row->age}, Email: {$row->email}\n";
}
// Free the result set
$result->free();
// Close the statement
$stmt->close();
// Close the connection
$conn->close();
?>
Error Handling and Best Practices
- Error Checking: Always check if the statement preparation and execution were successful.
- Freeing Results: Free the result set to free up resources.
- Closing Statements: Always close the prepared statements to ensure proper resource management.
- Using Native Driver: Ensure that the MySQL native driver (mysqlnd) is available to use advanced fetching methods like get_result.