Two-Dimensional Array from Database in PHP and MySQL
Sometimes it is not so easy to work on some related data stored in multiple database tables. 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.
This topic will show how to use PHP code to populate a two-dimensional array with data from two 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 that will have category name and within the 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 from the array.
Create the tables
We will create two tables named 'category' and 'items' in the MySQL database. Table structures and create table scripts are given below:
Table: category
Table has 2 columns.
- category_id - it is the primary key with auto increment
- category_name - Name of the category like Furniture, Electronics etc.
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
Table has 3 columns.
- item_id - primary key and it is auto incremented
- category_id - category id from the category table
- item_name - Name of the item
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 the below PHP scripts. We used a separate PHP file for this so that we can include it in other programs to connect to the database.
<?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.
- server - it is localhost
- userid - we are using root user
- password - no password for user root
- 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 that will populate a 2D array from the database and also will display data from the array.
<!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 for-each loops to get data from the database. The outer loop is for all rows from the category table and the inner loop is for all items from the 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 ordered and unordered lists. Categories are in an ordered list (outer loop - line 39) and items are in an unordered list(inner loop - line 42). This way, a 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 the below styles:
body{
font-size: 20px;
}
h2, h3{
text-align: center;
padding: 20px;
}
ol{
margin: auto;
width: 50%;
}
If you want to test it, check that in your XAMPP control panel Apache and MySQL services are running. Run localhost/array in the browser. You will see the below page displayed:
You can verify the above data with the database. It should match.
Download Source Code
Click on the download button to download the source code.
Conclusion
To do some data processing from the database, you can quickly get the data in an array and apply array functions or string functions to them. Remember that if the data volume is high, array processing can be slower.
Post a Comment