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

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

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.

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

Table: items

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

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

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.

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 and PDO.

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.

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:

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