Expert of PHP: Display Data From Database Into HTML Table Using PHP

Today i will share how to display data from MySQL database into HTML table using PHP. I will also show that how to store values from database table into HTML array, and how to handle that array in PHP. For this purpose first i will create a database with name allphptricks and then create a table in database with name sports and dump dummy data into it.

Steps to Display Data From Database Into HTML Table Using PHP

Create a Database
Create a Database Table
Dumping Data into Table
Create a Database Connection Page
Create a Main Index Page

1. Create a Database

Execute the following query in your MySQL query.

1

CREATE DATABASE allphptricks;

2. Create a Database Table

Run the following query in your above database.

1

2

3

4

5

CREATE TABLE IF NOT EXISTS `sports` (

  `sport_id` int(10) NOT NULL AUTO_INCREMENT,

  `sport_name` varchar(100) NOT NULL,

  PRIMARY KEY (`sport_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

3. Dumping Data into Table

Now i will insert sample data in our table, following query is dumping data into table.

1

2

3

4

5

6

7

8

9

10

INSERT INTO `sports` (`sport_id`, `sport_name`) VALUES

(1, 'Cricket'),

(2, 'Football'),

(3, 'Basketball'),

(4, 'Hockey'),

(5, 'Golf'),

(6, 'Badminton'),

(7, 'Boxing'),

(8, 'Cycling BMX'),

(9, 'Canoe Sprint');

For your ease i have also attached an sport.sql file in this tutorial download, you can import that file if you do not want to create table and dumping data.

4. Create a Database Connection Page

Create a database connection page with name db.php and copy the following code in it.

1

2

3

4

5

6

7

8

9

10

<?php

// Enter your Host, username, password, database below.

// I left password empty because i do not set password on localhost.

$con = mysqli_connect("localhost","root","","allphptricks");

// Check connection

if (mysqli_connect_errno())

  {

  echo "Failed to connect to MySQL: " . mysqli_connect_error();

  }

?>

5. Create a Main Index Page

Now create a main index.php page which contain the actual script of PHP which will show data from database into HTML table and also it will store all values in HTML array and on form submission, it will catch all selected values and print them on web page, you can also store it in your database.

Now copy the below code in your index.php page right after start of <body> tag

HTML Form & PHP Loop Script

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

<form name="form" method="post" action="">

<label><strong>Select Sports:</strong></label><br />

<table border="0" width="60%">

<tr>

<?php

$count = 0;

$query = mysqli_query($con,"SELECT * FROM sports");

foreach($query as $row){

 $count++;

?>

<td width="3%">

<input type="checkbox" name="sports[]" 

value="<?php echo $row["sport_id"]; ?>">

</td>

<td width="30%">

<?php echo $row["sport_name"]; ?>

</td>

<?php

if($count == 3) { // three items in a row

        echo '</tr><tr>';

        $count = 0;

    }

} ?>

</tr>

</table>

<input type="submit" name="submit" value="Submit">

</form>

<br />

<?php echo $status; ?>

In the input field name i used sports[] these square brackets will make it HTML array. The above code will get all sports name from database table and view them in the HTML table form, each row display three sports name. But this will also need database connection, so copy the below code before the start of <html> tag in the page header.

DB Connection & PHP Loop Script of Array

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

<?php

include('db.php');

$status = '';

if (!empty($_POST['sports'])){

if (is_array($_POST['sports'])) {

$status = "<strong>You selected the below sports:</strong><br />";

foreach($_POST['sports'] as $sport_id){

$query = mysqli_query

    (

    $con,

    "SELECT * FROM sports WHERE `sport_id`='$sport_id'"

    );

$row = mysqli_fetch_assoc($query);

$status .= $row['sport_name'] . "<br />";

   } 

  } 

} 

?>

Above code will not only include database connection file but it will also check if form is submitted so it will check array variable, if it is array then it will start foreach loop to catch all selected values by user and store it in $status variable which will display result below HTML table.

Little CSS of HTML Table

Add following CSS in your index.php before closing </head>

1

2

3

4

5

<style>

table td {

    border-bottom: 1px solid #f1f1f1;

}

</style>

If you found this tutorial helpful, share it with your friends and developers group.

Expert of PHP

Thursday, July 25, 2019

Display Data From Database Into HTML Table Using PHP