Learn how to display data from databases and tables in PHP

One of  the common needs of web users  is to fetch the data from the databases on the server and show this information in the form of a table. There are different ways to display databases and tables in PHP. You can use a function to show the fetched information from the database in a table.

Ways to display databases and tables in PHP:

One of the most important requirements of web users is to  display databases and tables in PHP. Depending on the needs of users and  the type of use of the web page, it may be necessary to display the information of all database tables or the information of some tables.

The user may want to display all or part of the table information. The information of all fields or specific fields can be displayed.

Database information can be displayed in the desired format on the web page after loading from the server. This information can be expressed as plain text or as a table.

Reading the names of database tables:

There may be several tables in a database. There are different ways to display databases and tables in PHP. Using SQL and PHP commands, you can read and show the names of  database tables connected to a web page.

The code snippet below shows the names of the students' database tables.

There are two tables named properties and lessons in the student's database. The output of the above code is like the picture below:

The dbconfig.php file is use to create communication with the database and contains the following code:

Exposing desired rows from database tables:

Each table of a database consists of some rows and columns. The information in each column or field is a special feature of an entity. For example, you can put fields such as product name and price in the sales table. When displaying information in a database, we may want to fetch and display certain records.

For example, we want to find students in the students' database, Tehran's address.

This database can be seen in the following image:

To expose the information of entities whose address is Tehran, we use the following codes:

At the beginning of the code, the dbconfig.php file is added to the code using the include command, and the necessary connection to the student's database is established through this file.  The $sql variable contains the SQLcommands  needed to select the name, family, and address fields of each record from the properties table that have a Tehran address. Then, the desired records are called from the database and placed in the rows variable using the following command.

Then, using the table, tr, and td tags, the table required to display the selected records is created. In the while loop, using the  mysqli_fetch_array function, the information in the rows variable is read and shown in the table.

You can see the output of the above code in the image below:

If you wish, you can use the mysqli_error function to manage possible errors after executing the code.

Expose database information in order:

You can sort the loaded information from the database based on the fields in ascending or descending order and then display it. To do this, just use the phrase ORDER BY in the SELECT command. Note the general form below:

The default sorting method here is ascending. Pay attention to the below codes.

In this example, all the information in the lessons table from the "students" database that we have already created is sorted by the lesson's name after the fetch and is displayed in the table format. You can see its output in the figure below.

Using the function to expose the loaded information in the table:

It is better to put the codes needed to  show databases in PHP  in a function. We put the code needed to display database records in a table in a file called create_html_table.php. Then we have used this file in another place using the include command.

In the create_html_table.php file, we have defined a function called CreateHtmlTable that displays information in a table.

Note the code in this file:

In another file called html_table.php, after connecting to the students' database and fetching the records of the properties table, we call the CreateHtmlTable function to show the fetched records in the table.

Note how to use this function in the following code:

You can use the CreateHtmlTable function to display the retrieved information from any database in the form of a table.