How to populate an array from MySQL database in PHP

Populating an array in PHP from MySQL database is very simple. All you have to do just 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 the table

Create a table named 'languages' in MySQL database. This table will have name of some programming languages. Table structure is given below:

populate array from database in php and mysql Table has 2 columns.
  1. id - Primary key and auto incremented
  2. language - programming language name

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;

Connect to MySQL database (dbconnect.php)

We will connect to MySQL database using 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:

  1. server - localhost
  2. userid - we are using root user
  3. password - no password for user root
  4. database name - demo in our case.

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.

PHP program to populate Array and display data (index.php)

This program will populate the programming language names from database to an array and also display all data from the database as well as from the array.

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 name in the array. Now, just to make sure 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.

Now, if you want to test it, just check that Apache and MySQL services are running in XAMPP control panel. Run localhost/db_array in the browser. You will see the below page displayed:

Array from Database PHP

populate array in php mysqlConclusion

When you want 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 my simple application.

Related Topics

Popular Topics