How to Create Table in PHP

Subodh Poudel Feb 02, 2024
How to Create Table in PHP

This tutorial will introduce a way to create a table in PHP. We will read the data from the database table and populate the data in an HTML table using PHP.

Create HTML Table and Display the Data from Database in PHP

We can use the HTML tags in PHP files anywhere in the script we like. So, creating tables in a PHP file is not an issue.

We can create table using the <table> tags and its child elements like <td>, <tr>, <th>, etc. The challenging part is to read the data from the database tables and show them in the HTML table.

This tutorial will guide you through creating a database connection, running the query and displaying the data in the HTML table.

Create Database and Table in MySQL

The first step is to create a database and create a table inside the database. Here, we will use the MySQL database.

Create a database users in the MySQL terminal using the following command.

create database users;

Next, we need to create a table. Before that, we must use the following command to choose the database.

use users;

To create a table, use the following code.

CREATE TABLE Customers (PersonID int, LastName varchar(255), FirstName varchar(255), City varchar(255));

Here, we have created a table, Customers with some columns.

Insert Data into the Table in MySQL

The second step is to populate the table with some rows. Run the following queries to insert data into the table.

insert into Customers values(1,"Doe","John","Brussels"), (2,"Sparrow","Jack","Delhi");

The following query will insert two rows in the table Customers. The table after the insertion operation looks like this.

+----------+----------+-----------+---------+
| PersonID | LastName | FirstName |  City   |
+----------+----------+-----------+---------+
|        1 | Doe      | John      | Brussels|
|        2 | Sparrow  | Jack      | Delhi   |
+----------+----------+-----------+---------+

Establish a Database Connection in PHP

The third step is to write PHP code to establish a database connection. We will be using procedural PHP for this purpose.

In procedural PHP, the mysqli_connect() function creates a database connection. It accepts many parameters like the server, username, password and database.

Example Code:

$connect = mysqli_connect("localhost", "root", "password321", "users");

In the example above, localhost is the server, root is the username, password321 is the database password and users is the database name.

If the credentials are correct, then a database connection will be established.

Write and Run the Query in PHP

The next step is to run the query. Create a variable $query and write a SQL query to select everything from the Customers table.

After that, run the query using the mysqli_query() function, where the parameters are the $connect and $query variables.

Example Code:

$query = "SELECT * FROM Customers";
$result = mysqli_query($connect, $query);

The above code runs and executes a MySQL query that selects all the data from the Customers table.

Fetch the Data and Tabulate them in HTML Table

The final step is to fetch the data from the table. We can check the number of rows affected by the query using the mysqli_num_rows() function.

The function takes the response of the query as the parameter. So, we can use the condition where the number of rows is greater than zero to fetch the data from the database.

To fetch the data, we can use the mysqli_fetch_array() function. It also takes the response of the query as its parameter.

The function returns each row in the form of an array.

For example, use the mysqli_num_rows() function with the $result variable as its parameter in an if condition. Inside the body of the if block, create a table using the <table> tag.

Assign it to a variable $table. Create the table headers using the <th> tags inside the <tr> tag.

Next, fetch the data using the mysqli_fetch_array() function with $result as the parameter.

Assign the function to a $row variable. Use the function inside the while loop for iteration.

The loop extracts each column from the $row array. Use the column name of the database as the index.

Wrap each value inside a <td> tag. Concatenate the result in the $table variable.

Finally, outside the loop, print the $table variable.

Example Code:

if(mysqli_num_rows($result) > 0)
 {
  $table = '
   <table border=1>
                    <tr>
                         <th> Person ID </th>
                         <th>First Name</th>
                         <th>Last Name</th>
                         <th>City</th>

                    </tr>
  ';
  while($row = mysqli_fetch_array($result))
  {
   $table .= '
    <tr>
                        <td>'.$row["PersonID"].'</td>
                         <td>'.$row["FirstName"].'</td>
                         <td>'.$row["LastName"].'</td>
                         <td>'.$row["City"].'</td>


                    </tr>
   ';
  }
  $table .= '</table>';
  echo $table;
 }

Output:

Fetch the Data and Tabulate them in HTML Table

As a result, a table with the data in the database is created, as shown in the output section above.

Subodh Poudel avatar Subodh Poudel avatar

Subodh is a proactive software engineer, specialized in fintech industry and a writer who loves to express his software development learnings and set of skills through blogs and articles.

LinkedIn

Related Article - PHP Table