Introduction to MySQLi
MySQLi (MySQL Improved) is an extension for PHP that allows interaction with MySQL databases. It offers both procedural and object-oriented interfaces, making it versatile and widely used. One of the key features of MySQLi is its ability to handle prepared statements, which enhances security by preventing SQL injection attacks.
Connecting to a MySQL Database
Before we can iterate over result sets, we need to establish a connection to the MySQL database. Here’s how you can do it:
Using Object-Oriented Approach
<?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);
}
echo "Connected successfully";
?>
Using Procedural Approach
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
Executing Queries and Iterating Over Result Sets
Once connected, the next step is to execute a query and fetch the results. We’ll look at two primary methods to fetch data: using mysqli_query with procedural style and query method with object-oriented style.
Using Procedural Style
In the procedural approach, mysqli_query is used to execute the query, and mysqli_fetch_assoc or similar functions are used to fetch rows from the result set.
Example 1: Fetching Rows with mysqli_fetch_assoc
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
Example 2: Fetching Rows with mysqli_fetch_row
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Output data of each row
while($row = mysqli_fetch_row($result)) {
echo "id: " . $row[0]. " - Name: " . $row[1]. " " . $row[2]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
Using Object-Oriented Style
In the object-oriented approach, the query method is used to execute the query, and fetch_assoc or similar methods are used to fetch rows from the result set.Example 1: Fetching Rows with fetch_assoc
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Example 2: Fetching Rows with fetch_row
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_row()) {
echo "id: " . $row[0]. " - Name: " . $row[1]. " " . $row[2]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Advanced Fetching Techniques
Fetching All Rows at Once
Sometimes, you might want to fetch all rows at once into an array. This can be done using the fetch_all method in the object-oriented approach or a combination of mysqli_fetch_all and procedural style.
Using Object-Oriented Style
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Fetch all rows into an array
$rows = $result->fetch_all(MYSQLI_ASSOC);
foreach ($rows as $row) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
Using Procedural Style
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// Fetch all rows into an array
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
foreach ($rows as $row) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
Using Prepared Statements
Prepared statements are a powerful way to execute queries safely and efficiently. They help prevent SQL injection attacks and can be reused with different parameters.
Example: Using Prepared Statements
<?php
// Prepare and bind
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests WHERE lastname = ?");
$stmt->bind_param("s", $lastname);
// Set parameters and execute
$lastname = "Doe";
$stmt->execute();
// Bind result variables
$stmt->bind_result($id, $firstname, $lastname);
// Fetch values
while ($stmt->fetch()) {
echo "id: " . $id. " - Name: " . $firstname. " " . $lastname. "<br>";
}
$stmt->close();
$conn->close();
?>
Error Handling
Proper error handling is crucial in database interactions to ensure that your application can gracefully handle and report issues.
Example: Error Handling in MySQLi
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result === FALSE) {
echo "Error: " . $conn->error;
} else {
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
}
$conn->close();
?>
Conclusion
Iterating over result sets in MySQLi is a fundamental aspect of working with databases in PHP. Whether you prefer the procedural or object-oriented approach, MySQLi provides robust tools to execute queries and fetch results efficiently. By understanding these techniques and implementing proper error handling, you can build more reliable and secure database-driven applications.Summary of Key Points
- MySQLi offers both procedural and object-oriented interfaces for database interaction.
- Fetching data can be done using methods like fetch_assoc, fetch_row, and fetch_all.
- Prepared statements enhance security by preventing SQL injection attacks.
- Proper error handling is essential for building robust applications.