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 the 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). This array will look 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. 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 with auto increment
  2. category_name - Name of category like Furniture, Electronics etc.

category.sql


CREATE TABLE `category` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'Furniture'),
(2, 'Electronics'),
(3, 'Dress Material'),
(4, 'Food');

ALTER TABLE `category`
  ADD PRIMARY KEY (`category_id`);

ALTER TABLE `category`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

Table: items

Making a Two-Dimensional Associative Array in php

Table has 3 columns.

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

items.sql


CREATE TABLE `items` (
  `item_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `item_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `items` (`item_id`, `category_id`, `item_name`) VALUES
(1, 1, 'Table'),
(2, 1, 'Chair'),
(3, 1, 'Shelf'),
(4, 1, 'Sofa'),
(5, 1, 'Bed'),
(6, 2, 'TV'),
(7, 2, 'Mobile'),
(8, 2, 'Laptop'),
(9, 2, 'Digital Clock'),
(10, 2, 'Microwave'),
(11, 3, 'Shirts'),
(12, 3, 'Tee'),
(13, 3, 'Trousers'),
(14, 3, 'Shorts'),
(15, 3, 'Jeans'),
(16, 4, 'Noodles'),
(17, 4, 'Chocolate'),
(18, 4, 'Ice Cream'),
(19, 4, 'Tea'),
(20, 4, 'Coffee');

ALTER TABLE `items`
  ADD PRIMARY KEY (`item_id`);

ALTER TABLE `items`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

Connect to MySQL database (dbconnect.php)

Connect to MySQL database using below PHP scripts. We used a separate php file for this, so that we can include it in other programs to connect to database.

dbconnect.php


<?php
  $server="localhost";
  $userid="root";
  $pwd="";
  $dbname="test";
  $conn = mysqli_connect($server, $userid, $pwd, $dbname);
//Check connection
if (!$conn) 
  	die("Connection Error: " . mysqli_connect_error());

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

  1. server - it is localhost
  2. userid - we are using root user
  3. password - no password for user root
  4. database name - we will use test database.

You can also 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.

index.php


<!DOCTYPE html>
<html lang="en">
<head>
  <title>Two Dimensional Array</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <div class="container">
    <h2>Populate a two dimensional array from database</h2>
   <?php
    include 'dbconnect.php'; // connect to database
    // we have two tables - category and items. For each category there are one or more items
    // we get all data from category and items in a two dimensional array
    $cat_item_arr = array();  // array for both category and items
    $sel_cat = "select * from category order by category_id";
    $rs_cat = mysqli_query($conn,$sel_cat);
    if (mysqli_num_rows($rs_cat) >0) {
      foreach ($rs_cat as $cat_value) {
        $category_id = $cat_value['category_id'];
        $sel_items = "select * from items where category_id ='$category_id' order by item_id";
        $rs_item = mysqli_query($conn,$sel_items);
        if (mysqli_num_rows($rs_item) >0) {
          $item_arr = array();   //item array for a category
          foreach ($rs_item as $item_value) {
            $item_arr[$item_value['item_id']] = $item_value['item_name'];  //populate item array
            }
        }
        $cat_item_arr[$cat_value['category_name']] = $item_arr; //populate the array 
      }
    }
    // Display all categories and items from the array
    ?>
    <h3>Display Two Dimensional Array:</h3>
    <ol>
    <?php
    foreach ($cat_item_arr as $cat_key => $value) {
      echo  "<li><b>".$cat_key."</b></li>";
      echo '<ul>';
      foreach ($value as $item_key => $item_value) {
        echo "<li>".$item_key."=".$item_value."</li>";
      }
      echo "</ul>";
    }
    ?>
  </ol>
  </div>
</body>
</html>

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 the array $item_arr, $cat_item_arr is populated. This way once all categories are fetched, our $cat_item_arr is populated with all categories and all items.

Now, display all elements from the array in an 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:


body{
	font-size: 20px;
}
h2, h3{
	text-align: center;
	padding: 20px;
}

ol{
margin: auto;
width: 50%;
}

Now, if you want to test it, just make sure in your XAMPP control panel Apache and MySQL services are running. Run localhost/array in the browser. 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. It should match.

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

Click on the download button to download the source code.

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.