In this post, we would learn how to display the data in HTML 5 table format from the database MySQL using PHP. Earlier we have learned how to install MySQL and how to use MySQL into our PHP application.

Display data in HTML 5 table from MySQL using PHP by Anil Kumar Panigrahi

Display data in HTML 5 table from MySQL using PHP by Anil Kumar Panigrahi

Sample MySQL database

Before connecting to database we need to create database.
An example I have given sample database structure below.

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
31
--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `contents`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Data to import into users table

1
2
3
4
5
6
7
8
--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `email`, `password`, `created_date`) VALUES
(1, 'Sophia Martin', '[email protected]', 'e10adc3949ba59abbe56e057f20f883e', '2017-12-01 17:42:45'),
(2, 'Emma Martin', '[email protected]', 'e10adc3949ba59abbe56e057f20f883e', '2017-12-01 17:42:45'),
(3, 'Olivia Martin', '[email protected]', 'e10adc3949ba59abbe56e057f20f883e', '2017-12-01 17:43:18');

Connecting to MySQL database

Earlier we have seen about how to connect database. By below code we can connect to database.

1
2
3
4
5
$host='';
$username='';
$password='';
$databasename='';
$mysqli = new mysqli($host, $username, $password, $password);

Now we need to write the select query to display

1
2
$selectquery = 'SELECT id,name,email,created_date from users';
$result = $mysqli->query($selectquery);

View it in HTML 5 table format

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
31
32
33
34
35
36
37
38
39
40
<!DOCTYPE html>
<!--[if lt IE 7]>      <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]>         <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]>         <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <title>Display data in HTML 5 table from MySQL using PHP</title>
        <meta name="description" content="">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <style>body{font-size:12px;}table {border-collapse: collapse;width:500px;}table, th, td {text-align: left;border: 1px solid #ccc;}td{width:25%;}th, td {padding: 15px;text-align: left;}tr:hover {background-color: #f5f5f5;}</style>
   </head>
    <body>
        <!--[if lt IE 7]>
            <p class="browsehappy">You are using an <strong>outdated</strong> browser. Please <a href="#">upgrade your browser</a> to improve your experience.</p>
        <![endif]-->
        <table>
            <tr>
                <th>Name</th>
                <th>Email</th>
                <th>Created Date</th>
                <th>Actions</th>
            </tr>
            <?PHP
                if ($result = $mysqli->query($selectquery)) {
                    while ($row = $result->fetch_row()) {
                        echo "<tr>";
                        echo "<td>".$row[1]."</td>";
                        echo "<td>".$row[2]."</td>";
                        echo "<td>".$row[3]."</td>";
                        echo "<td><a href='edit.php?id=".$row[0].">Edit</a><a href='delete.php?id=".$row[0].">Edit</a></td>";
                        echo "</tr>";
                    }
                }
            ?>    
        </table>
       
    </body>
</html>

Like this we can display the data into table format from MySQL using PHP.


2 Comments

Arsalaan Perwez · March 23, 2020 at 3:36 pm

Nice post!

How to make revisions of your content using PHP - Anil Labs · January 23, 2018 at 6:23 am

[…] Now we switch to PHP code: […]

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *