In this tutorial, you will learn how to populate an array from a MySQL database using PHP step by step. We will start by connecting PHP to the database, executing a simple SQL query, and then fetching the results into an array. This guide will help you understand how data is retrieved and stored dynamically in arrays.
We will populate an array from the database and then display them from the array for verification.
Create a table named 'languages' in MySQL database. This table will have the names of some programming languages. The table structure is given below:
Table has 2 columns.
languages.sql
CREATE TABLE `languages` (
`id` smallint(11) NOT NULL,
`language` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `languages` (`id`, `language`) VALUES
(1, 'Java'),
(2, 'C++'),
(3, 'Python'),
(4, 'C#'),
(5, 'C'),
(6, 'PHP');
ALTER TABLE `languages`
ADD PRIMARY KEY (`id`);
ALTER TABLE `languages`
MODIFY `id` smallint(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
We will create "index.php" for this. We will follow the steps below for this tutorial:
We will connect to a MySQL database using the PHP script below:
<?php
// connect to the database
$conn = new mysqli("localhost", "root", "", "test");
if ($conn->connect_error)
die("DB Connection Error: " . $conn->connect_error);
?>
We create a mysqli object with four parameters as given below:
You can read this tutorial How to Connect to MySQL Database in PHP using MySQLi and PDO to learn more on database connection in PHP .
Using a SELECT statement fetch the data from the database and display them in an HTML table. At the same time store the data in an array. See the below code:
<?php
$languages = array(); // declare array
$sql = "select * from languages order by language";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->get_result(); ?>
<!-- Show data from database in a html table -->
<div class="table-responsive">
<table class="table table-bordered table-striped" style="width:60%;margin:auto;">
<thead>
<tr>
<th>Id</th>
<th>Language Name</th>
</tr>
</thead>
<tbody>
<?php if ($result->num_rows > 0) {
$i = 0;
foreach ($result as $row) {
$i++; ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['language'] ?></td>
</tr>
<?php $languages[$i] = $row['language']; //store into an array
}
} else { ?>
<tr>
<td colspan="2">No data Found</td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
$languages is our array. Data is displayed in an html table using a for-loop. At the same time we are storing language names in the array in line 26 above.
To verify if the array is populated correctly, we will display all elements from the array. See the code below for this:
<h2>Display Array</h2>
<?php
// Display data from the array for verification
if (!empty($languages)) {
foreach ($languages as $value) {
echo nl2br($value."\n");
}
}
?>
We will save index.php in the folder 'db_array' under xampp/htdocs. See the below code for index.php.
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>Array Example</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.8/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<?php
// connect to database
$conn = new mysqli("localhost", "root", "", "test");
if ($conn->connect_error)
die("DB Connection Error: " . $conn->connect_error);
?>
<div class="container" style="text-align: center;">
<h1>Populate an Array from the Database</h1>
<?php
$languages = array(); // declare array
$sql = "select * from languages order by language";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->get_result(); ?>
<!-- Show data from database in a html table -->
<div class="table-responsive">
<table class="table table-bordered table-striped" style="width:60%;margin:auto;">
<thead>
<tr>
<th>Id</th>
<th>Language Name</th>
</tr>
</thead>
<tbody>
<?php if ($result->num_rows > 0) {
$i = 0;
foreach ($result as $row) {
$i++; ?>
<tr>
<td>
<?= $row['id'] ?>
</td>
<td>
<?= $row['language'] ?>
</td>
</tr>
<?php $languages[$i] = $row['language']; //store into an array
}
} else { ?>
<tr>
<td colspan="2">No data Found</td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<h2>Display Array</h2>
<?php
// Display data from the array for verification
if (!empty($languages)) {
foreach ($languages as $value) {
echo nl2br($value."\n");
}
}
?>
</body>
</html>
If you want to test it, make sure Apache and MySQL services are running in the XAMPP control panel. Run localhost/db_array in the browser. You will see the below page displayed:
Conclusion
In this tutorial, you've learned how to connect PHP with MySQL and fetch data into an array. Populating arrays from a database is a fundamental skill that helps you display and manipulate data dynamically in your web applications. Remember that if the data volume is too high, array processing can be slow.
Please write your comments/questions in the Comments section below. Your questions, doubts and suggestions are always welcome.
Post a Comment