Populating an array in PHP from MySQL database is very simple. All you have to do is connect to the database, use a select query to get data from the database, fetch each row and insert it into an array. Let us see an example of how you can do it.
We will populate an array with the data stored in a database table and display them from the array.
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(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `languages` (`id`, `language`) VALUES
(1, 'Java'),
(2, 'C++'),
(3, 'Python'),
(4, 'C-Sharp'),
(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 connect to a MySQL database using the below PHP script:
dbconnect.php
<?php
$server="localhost";
$userid="root";
$pwd="";
$dbname="demo";
$conn = mysqli_connect($server, $userid, $pwd, $dbname);
//Check connection
if (!$conn)
die("Connection Error: " . mysqli_connect_error());
We are using mysqli_connect() function with four parameters as given below:
For successful connection it will return true. We will include this dbconnect.php in other php programs. You can read the topic How to Connect to MySQL Database in PHP using MySQLi and PDO.
This program will populate the programming language names from the database to an array and display the data from the database and the array after populating it. It will be easier to verify if the array is populated correctly.
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 rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<?php
// connect to database
include 'dbconnect.php';
?>
<div class="container" style="text-align: center;">
<h2>Populate an array from database and display data from array</h2>
<?php
$languages = array(); // declare array
$sql = "select * from languages order by id";
$rs = mysqli_query($conn,$sql);?>
<!-- Show data from database in a html table -->
<table class="table table-bordered table-stripped">
<tr>
<th>Id</th><th>Language Name</th>
</tr>
<?php if (mysqli_num_rows($rs) >0) {
$i = 0;
foreach ($rs as $value) {
$i++; ?>
<tr>
<td><?php echo $value['id']?></td>
<td><?php echo $value['language']?></td>
</tr>
<?php $languages[$i] = $value['language']; //populate array with language name
}
}
else {?>
<tr><td colspan="2"><?php echo "No data exist in database table";?></td></tr>
<?php } ?>
</table>
<h3>Array after populating it from database table</h3>
<div class="array">
<?php
// Display data from array
if (!empty($languages)) {
echo "<strong>Array: <br></strong>";
foreach ($languages as $value) {
echo "$value<br>";
}
}
?>
</div>
</body>
</html>
The array is defined as $languages. Look at line 34, data is inserted into the array. After fetching each row from the database table, data is displayed in an html table, at the same time we are inserting language names in the array. To verify the array is populated correctly, we are displaying all elements from the array.
We will use a folder 'db_array' under xampp/htdocs and save index.php in that folder.
If you want to test it, check that 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:
ConclusionWhen you want to do some processing on the data from the database, you can quickly get it into an array and apply array functions or string functions available in PHP. Remember that if the data volume is too high, array processing can be slow. I hope you could understand the way I have presented it using my simple application.
Post a Comment