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.
I have a database table called 'languages' with data for programming languages. We will populate an array with all language names stored in the database table. We have a database connection PHP script(
dbconnect.php) and an
index.php which will display the data from the database table and also display them from the array after it is populated.
Create the table
Let us create a table named 'languages' in MySQL database. This table will have name of some programming languages. I have a database called 'demo'. So 'languages' Table will be created in this database. If you have an existing database other than demo you can also use it. Just make sure same table name does not exist already. Table structure is given below:
Table has 2 columns.
- id - it is the primary key and auto incremented
- language - programming language name
Create table script for this table is given below, you can use this code to create the table. You can also download the scripts from download section later.
After you create the table and inserted data in it, please verify if data is inserted correctly, it should have below data in it:
Connect to MySQL database (dbconnect.php)
Use below script to connect to the database. Note that we have this database connection php program. This is written in a separate php program as it can be used in every program where database connection is needed. This will be easy for maintenance and also will enable reusability of the code.
I am using
mysqli_connect() function which needs 4 parameters.
- server - in our case it is localhost
- userid - we are using root user
- password - no password for user root
- database name - demo in our case.
If connection is successful then it will return true and false otherwise. This Boolean value is returned and stored in the $conn variable. We will include this
dbconnect.php in other php programs so that we do not need to write it again in the program. For detail database connection understanding please read topic How to Connect to MySQL database in PHP using mysqli_connect.
PHP program to populate Array and display data (index.php)
This is the program which 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.
The array is defined $languages. Look at line 34, data is inserted into the array. After fetching each row from the database table, data is displayed in a 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. In line 47, displaying each array element using a foreach loop.
I created a folder called 'db_array' under xampp/htdocs and saved index.php in that folder.
Now, if you want to test it, just make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/db_array. You will see the below page displayed:
Download Source Code
I have put all codes in a zip file. You can download it by clicking on the Download button below. You do not need to register yourself to download it. You can directly use the code or you can modify them as per your requirements.
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.