This function fetches a result row as an associative array, where the keys correspond to the column names of the fetched row. In this article, we will explore mysqli_fetch_assoc(), providing detailed examples and various use cases to illustrate its practical applications.
Understanding mysqli_fetch_assoc()
The mysqli_fetch_assoc() function fetches a result row as an associative array. This means that the array keys are the column names from the result set, and the values are the corresponding row values. This method is particularly useful when you need to access the data by column names, making your code more readable and easier to maintain.
Syntax
The basic syntax of mysqli_fetch_assoc() is as follows:
mysqli_fetch_assoc(result);
result: This parameter is the result resource that is being evaluated. This result comes from a call to mysqli_query(), mysqli_store_result(), or mysqli_use_result().
Setting Up a Connection with MySQLi
Before you can fetch data, you need to establish a connection to the MySQL database. Here’s a basic example of how to connect to a MySQL database using MySQLi:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
In this example, replace localhost, username, password, and database_name with your actual database connection details.
Fetching Data Using mysqli_fetch_assoc()
Let’s consider a table named users with the following structure:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT NOT NULL
);
Example 1: Basic Fetching of Data
In this example, we will fetch data from the users table and display it using mysqli_fetch_assoc().
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT id, username, email, age FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "ID: " . $row["id"] . " - Username: " . $row["username"] . " - Email: " . $row["email"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
In this example, mysqli_fetch_assoc() fetches each row from the result set as an associative array. The keys of the array are the column names (id, username, email, age).
Example 2: Using Data in HTML Table
Fetching data and displaying it in an HTML table is a common requirement in web applications. Here’s how you can do it:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT id, username, email, age FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
<th>Age</th>
</tr>";
while($row = mysqli_fetch_assoc($result)) {
echo "<tr>
<td>" . $row["id"] . "</td>
<td>" . $row["username"] . "</td>
<td>" . $row["email"] . "</td>
<td>" . $row["age"] . "</td>
</tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>
This code retrieves data from the users table and displays it in an HTML table. Each row of the result set is fetched using mysqli_fetch_assoc() and printed inside the table rows.
Example 3: Fetching Data for JSON API Responses
When building APIs, it is common to fetch data from a database and return it as a JSON response. Here’s an example of how to do this using mysqli_fetch_assoc():
<?php
header('Content-Type: application/json');
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT id, username, email, age FROM users";
$result = $conn->query($sql);
$users = array();
if ($result->num_rows > 0) {
while($row = mysqli_fetch_assoc($result)) {
$users[] = $row;
}
echo json_encode($users);
} else {
echo json_encode(array());
}
$conn->close();
?>
This code fetches data from the users table, stores it in an array, and returns it as a JSON response. This is particularly useful for building RESTful APIs.
Use Cases for mysqli_fetch_assoc()
Use Case 1: Processing Data for Further Operations
Sometimes, you may need to fetch data from a database, process it, and then perform additional operations based on the retrieved data. Here’s an example where we calculate the average age of users:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all users
$sql = "SELECT age FROM users";
$result = $conn->query($sql);
$total_age = 0;
$count = 0;
if ($result->num_rows > 0) {
while($row = mysqli_fetch_assoc($result)) {
$total_age += $row["age"];
$count++;
}
$average_age = $total_age / $count;
echo "The average age of users is: " . $average_age;
} else {
echo "0 results";
}
$conn->close();
?>
In this example, we fetch the age column from the users table, sum the ages, and calculate the average age.
Use Case 2: Combining Data from Multiple Tables
Fetching and combining data from multiple tables is a common task in database operations. Here’s an example where we join data from the users table with an orders table:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select users and their orders
$sql = "SELECT users.username, orders.order_id, orders.product_name
FROM users
JOIN orders ON users.id = orders.user_id";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "Username: " . $row["username"] . " - Order ID: " . $row["order_id"] . " - Product: " . $row["product_name"] . "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
This code joins the users and orders tables on the user_id field and fetches the data. The result is then printed using mysqli_fetch_assoc().
Use Case 3: Pagination of Results
When dealing with large datasets, it is often necessary to paginate results. Here’s an example of how to paginate data using mysqli_fetch_assoc():
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Set the number of results per page
$results_per_page = 10;
// Find out the number of results stored in the database
$sql = "SELECT COUNT(id) AS total FROM users";
$result = $conn->query($sql);
$row = mysqli_fetch_assoc($result);
$total_pages = ceil($row["total"] / $results_per_page);
// Determine which page number visitor is currently on
if (isset($_GET['page']) && is_numeric($_GET['page'])) {
$page = $_GET['page'];
} else {
$page = 1;
}
// Determine the sql LIMIT starting number for the results on the displaying page
$starting_limit_number = ($page - 1) * $results_per_page;
// SQL query to fetch selected results
$sql = "SELECT id, username, email, age FROM users LIMIT " . $starting_limit_number . ',' . $results_per_page;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo "<table border='1'><tr><th>ID</th><th>Username</th><th>Email</th><th>Age</th></tr>";
while($row = mysqli_fetch_assoc($result)) {
echo "<tr><td>" . $row["id"] . "</td><td>" . $row["username"] . "</td><td>" . $row["email"] . "</td><td>" . $row["age"] . "</td></tr>";
}
echo "</table>";
// Display the pagination links
for ($page = 1; $page <= $total_pages; $page++) {
echo '<a href="index.php?page=' . $page . '">' . $page . '</a> ';
}
} else {
echo "0 results";
}
$conn->close();
?>
In this example, we fetch a limited number of rows per page and display pagination links to navigate through the pages.