Welcome to our comprehensive PHP tutorial on the mysqli_fetch_array() function! In this lesson, we'll dive deep into understanding this versatile function and learn how to use it to fetch rows from a result set. By the end of this tutorial, you'll be equipped to handle real-world database operations using PHP and MySQL. 📝
mysqli_fetch_array()? 📝mysqli_fetch_array() is a function provided by the PHP MySQL extension to fetch data from a result set, which is returned when executing a database query. It returns an associative array, a numeric array, or both, depending on the options passed.
mysqli_fetch_array()? 💡Using mysqli_fetch_array() allows you to access the fetched rows in an easy-to-handle array format. This function simplifies the process of iterating through the result set and accessing the data, making it more convenient for you to manage and work with the fetched data.
Let's dive into a simple example to get started.
<?php
$conn = new mysqli("localhost", "username", "password", "database_name");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_array(MYSQLI_ASSOC)) {
echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>In the above example, we create a connection to the database, execute a query to fetch user data, and use mysqli_fetch_array() to iterate through the fetched rows. The MYSQLI_ASSOC option is used to fetch an associative array, which maps the column names to array keys.
📝 Note: Make sure to replace "localhost", "username", "password", and "database_name" with your actual database connection details.
mysqli_fetch_array() supports fetching data in three different types of arrays: associative, numeric, and both. To fetch data in one of these formats, simply pass the appropriate constant as the second argument:
MYSQLI_ASSOC: Fetches data as an associative array.MYSQLI_NUM: Fetches data as a numeric array.MYSQLI_BOTH: Fetches data as both an associative and numeric array.Here's an example demonstrating the usage of the three options:
<?php
// Using MYSQLI_ASSOC
$row_assoc = $result->fetch_array(MYSQLI_ASSOC);
echo "id: " . $row_assoc["id"]. " - Name: " . $row_assoc["name"]. " - Email: " . $row_assoc["email"]. "<br>";
// Using MYSQLI_NUM
$row_num = $result->fetch_array(MYSQLI_NUM);
echo "id: " . $row_num[0]. " - Name: " . $row_num[1]. " - Email: " . $row_num[2]. "<br>";
// Using MYSQLI_BOTH
$row_both = $result->fetch_array(MYSQLI_BOTH);
echo "id: " . $row_both["id"]. " - Name: " . $row_both["name"]. " - Email: " . $row_both["email"]. "<br>";
?>📝 Note: If you don't specify an option, MYSQLI_BOTH is the default.
mysqli_fetch_array() can also be used to fetch multiple rows at once, rather than iterating through the result set one row at a time. To do this, use the MYSQLI_MULTI_RESULT option when executing multiple queries:
<?php
$conn = new mysqli("localhost", "username", "password", "database_name");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql1 = "SELECT id, name FROM users";
$sql2 = "SELECT email FROM users_emails";
$conn->query("SET @rownum = 0");
if ($result1 = $conn->query($sql1)) {
while ($row = $result1->fetch_array(MYSQLI_ASSOC)) {
$id = $row["id"];
$email_query = $conn->query("SELECT email FROM users_emails WHERE user_id = $id LIMIT 1");
$email = $email_query->fetch_array(MYSQLI_ASSOC)["email"];
echo "id: " . $id . " - Name: " . $row["name"]. " - Email: " . $email . "<br>";
$conn->query("SET @rownum = @rownum + 1");
}
}
$conn->close();
?>In this example, we use mysqli_fetch_array() to fetch multiple rows from two different tables and combine the results to create a single output. We first fetch user data, then use a subquery to fetch the corresponding email data for each user.
What does the `mysqli_fetch_array()` function do?
In this tutorial, you learned about the mysqli_fetch_array() function in PHP, and how to use it to fetch data from a result set. We covered the basic usage, fetching multiple types of arrays, and fetching multiple rows at once.
Now that you've got a solid understanding of mysqli_fetch_array(), you can tackle real-world database operations with confidence. Happy coding! 💡