How to create two dimensional array from database in PHP and MySQL

Sometimes it is not so easy to work on some related data which are stored in multiple tables in the database. But, it is easier to work on them if same data is available in an array, you can use various PHP functions on the array according to your needs.

In this topic we will show how you can use php code to populate a two-dimensional array with data from two different database tables.

We have two tables, category and items. For each category in category table, there are one or more items in items table. So we will have a two-dimensional array which will have category name and within category name there will be item id and item name (key - value association). Elements will be like below:

{'Futniture' => {1 => Sofa, 2 => Table, 3 => Bed}, 'Electronics' => {6 => TV, 7 => Mobile}, 'Food' => {9 => Tea, 10 => Coffee}}

Below is the output after populating the 2D array and then displaying it form the array.

populate two dimensional array in php from database

Create the tables

Let us create two tables named 'category' and 'items' in MySQL database. I have a database called 'demo'. So, these tables will be created in demo database. If you have an existing database other than demo you can also use it. Just make sure same tables do not exist already. Table structures and create table scripts are given below:

Table: category

fetch data from MYSQL to two-dimensional array in php

Table has 2 columns.

  1. category_id - it is the primary key and auto incremented
  2. category_name - Name of category like Furniture, Electronics etc.

category.sql

After you run the scripts, verify that data is inserted correctly, it should have below data in it:

Display two-dimensional array with two nested loops

Table: items

Making a Two-Dimensional Associative Array in php

Table has 3 columns.

  1. item_id - it is the primary key and auto incremented
  2. category_id - category id from category table
  3. item_name - Name of the item

items.sql

After you run the scripts, verify if data inserted correctly, it should have below data in it:

create multidimensional array in php Code Example

Connect to MySQL database (dbconnect.php)

Use below script to connect to the database. This is written in a separate php program as it can be used in every program where database connection is needed. This will be easier for maintenance and also will enable reusability of the code.

dbconnect.php

I am using mysqli_connect() function which needs 4 parameters.

  1. server - in our case it is localhost
  2. userid - we are using root user
  3. password - no password for user root
  4. database name - demo in our case.

For detail database connection understanding please read the topic How to Connect to MySQL database in PHP using mysqli_connect .

PHP program to populate two-dimensional array and display data (index.php)

This is the program which will populate 2D array from database and also will display data from the array.

index.php

There are two foreach loops to get data from the database. Outer loop is for all rows from category table and inner loop is for all items from items table for each category. In the inner loop we are populating an associative array $item_arr. Once all items are populated for that category in $item_arr array, $cat_item_arr is populated. This way once all categories are fetched, our $cat_item_arr are populated with all categories and all items.

Now, display all elements from the array in ordered and unordered lists. Categories are in ordered list (outer loop - line 39) and items are in unordered list(inner loop - line 42). This way, list of items will be displayed under each category.

Test the program

Create a folder called 'array' under xampp/htdocs and save index.php in that folder.

We have a simple stylesheet style.css and included index.php. See below styles:

Now, if you want to test it, just make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/array. You will see the below page displayed:

How to store data from database in 2D array in PHP?

You can verify the above data with the database tables category and items. It should match.

download source code to populate and display two dimensional array in phpDownload Source Code

I have put all codes in a zip file. You can download it by clicking on the Download button below. You do not need to register yourself to download it. You can directly use the code or you can modify them as per your requirements.

access data in multidimensional array in PHPConclusion

To do some processing on the data from database, you can quickly get it in an array and apply array functions or string functions available in PHP. Just remember that if the data volume is too high, then array processing can be slow. Hope you could understand the way I have presented it using this simple application.