In this tutorial, we will learn how to use the mysqli_fetch_assoc function in PHP to fetch the next row of a result set as an associative array.
In PHP, an associative array refers to an array that uses a named key and respective value. In other programming languages, associative arrays are also known as dictionaries.
Function Syntax
The following shows the syntax for the mysqli_fetch_assoc function:
The function fetches one row of data from a given result set and returns it as an associative array. The function will return the next row within the result set on each subsequent function call.
If there are no more rows to fetch, the function will return a NULL value.
NOTE: The keys of the associative arrays are case-sensitive. Therefore, it is good to reference an existing key from the associative array.
Example Usage
Let us illustrate with an accurate database how best to use the mysqli_fetch_assoc function.
Consider the table and sample data shown below:
use src;
drop table if exists stack_mapping;
create table stack_mapping(
id int auto_increment primary key,
server_name varchar(50) not null,
address varchar(100) not null,
installed_version varchar(50),
tool_id int
);
insert into stack_mapping(server_name, address, installed_version, tool_id)
values ('SQL Server', 'localhost:1433', '15.0', 1),
('Elasticsearch', 'localhost:9200', '8.4', 2),
('Redis', 'localhost:6379', '6.0', 3),
('PostgreSQL', 'localhost:5432', '14.5', 4),
('MySQL', 'localhost:3306', '8.0', NULL);
Fetch Table Rows as an Associative Array
The following example demonstrates using the mysqli_fetch_assoc function to fetch the next row of a result set as an associative array.
//connect to localhost:3306 as root user with an empty password (target database: src)
$conn = mysqli_connect("localhost", "root", "", "src");
// target query
$query = "SELECT * FROM stack_mapping ORDER BY tool_id";
$result = mysqli_query($conn, $query);
// loop over each item in the result set as an assoc array
while ($row = mysqli_fetch_assoc($result)) {
printf("%s (%s)\n", $row["server_name"], $row["address"], $row["installed_version"], $row["tool_id"]);
Once we run the code above, we should get the output as shown:
SQL Server (localhost:1433)
Elasticsearch (localhost:9200)
Redis (localhost:6379)
PostgreSQL (localhost:5432)
In the script above, we start by creating a connection object to the MySQL Server using the provided credentials.
Next, we create the query we wish to execute on the target table. In our example, we select all columns from the stack_mapping table ordered by the tool_id column.
Next, we use the mysqli_query function to execute the query on the database and store the result set to the $result variable.
Finally, we iterate over each item in the result set using the mysqli_fetch_assoc() function as an associative array.
We then print each item for the specified keys of the array.
Conclusion
In this post, we discussed the basics of working with the mysqli_fetch_assoc function in PHP to fetch the next row in the result set as a dictionary.
Thanks for reading!!
