How to Display MySQL data in HTML-5 Table Using PHP

Continuing learning about PHP and MySQL, this time, we’ll discuss how to display MySQL data in HTML  Table With PHP.

When retrieving data from a MySQL table, generally we’ll display it in a table form, by displaying data in a table form, will make it more attractive and easier to read.

In this tutorial, we’ll learn how to do it. The final result will look like the following figure:

How to Display MySQL Data in HTML Table

The Design of the above table needs to be adjusted so that it suit to the theme of your website and the type of data to be displayed.

To learn more about designing HTML table, you can follow these tutorials:

I. PHP Functions Used to Retrieve and Display Data from MySQL Database

In the previous article, we have discussed how to retrieve the data from MySQL database tables, the point is, it is not recommended anymore to use mysql_xxx function, because it has been removed in PHP 7, use mysqli_xxx or PHP PDO instead.

Therefore, on this tutorial, we’ll use mysqli_xxx function for connection, querying, and displaying MySQL data. If you prefer to use OOP way, you can adjust is as necessary.

II. Sample Data

In this tutorial, we’ll use an example of sales data saved on a database table names “sales”. To create it, login to phpMyAdmin select a database, then click the “SQL” tab menu located on the top.

Next, copy and paste the following code and execute:

CREATE TABLE IF NOT EXISTS `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET latin1 NOT NULL,
  `item` varchar(50) CHARACTER SET latin1 NOT NULL,
  `date` date NOT NULL,
  `amount` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8;

INSERT INTO `sales` (`id`, `name`, `item`, `date`, `amount`) VALUES
(1, 'Andrew', 'Television', '2016-06-07', 2500),
(2, 'Bryan', 'Washing Machine', '2016-07-10', 1100),
(3, 'Cherly', 'Water Dispenser', '2016-08-11', 95),
(4, 'Dean', 'Refrigerator', '2016-09-15', 583),
(5, 'Esryl', 'Wall Fan', '2016-10-11', 45),
(6, 'Franky', 'Steam Iron', '2016-10-17', 0),
(7, 'Gerry', 'Air Conditioner', '2016-11-17', 325);

If the SQL run successfully we’ll see a sales table  exists within the database

III. Display MySQL Data In HTML Table

After the above preparation is completed, now we come to the main part of this tutorial, retrieve data from MySQL database and display it in an HTML table.

First of all, create a .php file and save it to the htdocs directory. In this example, I give a name displaying_data.php.

Note: htdocs is a folder used to place all .php file, if you don’t have one, you need to setup a PHP environment, you can read the guidance here.

Furthermore, copy-paste and run the following script (don’t forget to adjust the server name, username, password, and database name):

<?php
$db_host = 'localhost'; // Server Name
$db_user = 'root'; // Username
$db_pass = ''; // Password
$db_name = 'tutorial'; // Database Name

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
	die ('Failed to connect to MySQL: ' . mysqli_connect_error());	
}

$sql = 'SELECT * 
		FROM sales';
		
$query = mysqli_query($conn, $sql);

if (!$query) {
	die ('SQL Error: ' . mysqli_error($conn));
}
?>
<html>
<head>
	<title>Displaying MySQL Data in HTML Table</title>
	<style type="text/css">
		body {
			font-size: 15px;
			color: #343d44;
			font-family: "segoe-ui", "open-sans", tahoma, arial;
			padding: 0;
			margin: 0;
		}
		table {
			margin: auto;
			font-family: "Lucida Sans Unicode", "Lucida Grande", "Segoe Ui";
			font-size: 12px;
		}

		h1 {
			margin: 25px auto 0;
			text-align: center;
			text-transform: uppercase;
			font-size: 17px;
		}

		table td {
			transition: all .5s;
		}
		
		/* Table */
		.data-table {
			border-collapse: collapse;
			font-size: 14px;
			min-width: 537px;
		}

		.data-table th, 
		.data-table td {
			border: 1px solid #e1edff;
			padding: 7px 17px;
		}
		.data-table caption {
			margin: 7px;
		}

		/* Table Header */
		.data-table thead th {
			background-color: #508abb;
			color: #FFFFFF;
			border-color: #6ea1cc !important;
			text-transform: uppercase;
		}

		/* Table Body */
		.data-table tbody td {
			color: #353535;
		}
		.data-table tbody td:first-child,
		.data-table tbody td:nth-child(4),
		.data-table tbody td:last-child {
			text-align: right;
		}

		.data-table tbody tr:nth-child(odd) td {
			background-color: #f4fbff;
		}
		.data-table tbody tr:hover td {
			background-color: #ffffa2;
			border-color: #ffff0f;
		}

		/* Table Footer */
		.data-table tfoot th {
			background-color: #e5f5ff;
			text-align: right;
		}
		.data-table tfoot th:first-child {
			text-align: left;
		}
		.data-table tbody td:empty
		{
			background-color: #ffcccc;
		}
	</style>
</head>
<body>
	<h1>Table 1</h1>
	<table class="data-table">
		<caption class="title">Sales Data of Electronic Division</caption>
		<thead>
			<tr>
				<th>NO</th>
				<th>CUSTOMER</th>
				<th>ITEM</th>
				<th>DATE</th>
				<th>AMOUNT</th>
			</tr>
		</thead>
		<tbody>
		<?php
		$no 	= 1;
		$total 	= 0;
		while ($row = mysqli_fetch_array($query))
		{
			$amount  = $row['amount'] == 0 ? '' : number_format($row['amount']);
			echo '<tr>
					<td>'.$no.'</td>
					<td>'.$row['name'].'</td>
					<td>'.$row['item'].'</td>
					<td>'. date('F d, Y', strtotime($row['date'])) . '</td>
					<td>'.$amount.'</td>
				</tr>';
			$total += $row['amount'];
			$no++;
		}?>
		</tbody>
		<tfoot>
			<tr>
				<th colspan="4">TOTAL</th>
				<th><?=number_format($total)?></th>
			</tr>
		</tfoot>
	</table>
</body>
</html>

If successful, the browser will display a table like the above image.

DONE

Congrats, At this step, you have successfully displaying MySQL data in an HTML table.  If you need more explanation, read the rest of this article.

1 PHP Script

In the above example, we put PHP scripts on the top of the script. This is for separating PHP code from HTML code. (separate logic and presentation).

Furthermore, it common to separate .php file that contain logic and .php file that contain presentation.

Retrieve MySQL data

To retrieve MySQL data using PHP, we use mysqli_fetch_array() function, which will generate indexed array and associative array.

You can use two other functions to retrieve MySQL data, that are: mysqli_fetch_row() and mysqli_fetch_assoc()

mysql_fetch_row() will generate an indexed array (array with a sequence number as the key), while mysql_fetch_assoc() will generate an associative array (array with field name as the key)

The differences of the three functions above are discussed in-depth in the article: Retrieve Data From MySQL Database Using PHP

More about array: Understanding Array In PHP

Date format

In the database, the date format is yyyy-mm-dd (the most recommended format date for SQL). To convert it into a specific format, we use date() function.

In the above code we use date('d F, Y', strtotime($row['date'])) the d F, Y argument will give a specific output:

  • d for two digit date, 01 to 31
  • F for Fulltext month, January through December
  • Y for four digit year

PHP provide a lot of date format, more about date() function and date format can be read in the page: PHP: date – Manual.

In the second argument, we use strtotime() function to convert date (yyyy-mm-dd) to UNIX timestamp. We have to do this because the second argument of PHP date function must be a timestamp

More about strtotime() function can be followed here: PHP: strtotime – Manual

Statement / Other Functions

Statement / other PHP function that we use:

  • Ternary operator to define the variable $amount. if the value is 0 then we change the value to blank. This is for styling purpose, to give a style on the blank column using CSS.
  • Assignment operator += . We use it to sum the total. In the above example, we write $total += $row['amount'], this is same as $total = $total + $row['amount']
  • Function: number_format() that we use to format the number in thousands separator.
  • Increment operator $no++ used to make a sequence number.

2 HTML 5 Code

Related to the table elements, there are no new table elements in HTML 5, but there are some important attributes which are no longer supported, such as align, valign, and width.

If we want to use those attributes, we have to change it into inline-style CSS, eg: from <th width="80">Amount</th> into <th style="width:80px">Amount</th>

In the example above, we use <caption> element for displaying the title and group rows into three parts using the <thead>, <tbody>, and <tfoot> elements

To be a valid HTML 5 code that is meet the standards, we must pay attention to the placement of these elements:

  • <caption> element is always on top.
  • <th> element as the columns of the table, can be placed inside <thead>, <tbody>, or <tfoot> element.
  • <thead> element as the header of the table, should be at the top underneath the <caption> element and <col> or <colgroup> element (if any)
  • <tbody> element as the body of the table, should be positioned under the <thead> element.
  • <tfoot> element as the footer of the table, should be positioned under <thead> element but not always under <tbody> element.

More about above elements can be read here: How to Create Table Using HTML 5 and CSS 3

3 CSS Code

In the example above, we design the table using CSS, specifically CSS 3, so it looks more interesting and beautiful. Some styles that we apply to the table:

  • Make alternate color to the table row (zebra-row) by using the :nth-child(odd) and: nth-child(even) selector. As the name implies odd will match the odd rows (1, 3, 5, etc.) and even will match the even rows (2, 4, 6, etc.).
  • Creating right-aligned columns to the columns of number, date, and amount. For number column, we use tbody td:first-child selector, date column using tbody td:nth-child(4) selector, and amount column using tbody td:last-child selector.
  • If we display a large number of data, then for analysis purpose, we need to mark the blank columns/fields, so the blank column becomes more “eye-catching”. In the example above, we give it a red color. In CSS 3, to select an empty element we use :empty selector, in the above example we apply it to body td:empty selector.
  • When the mouse is over the row (:hover event), we change the color of the row into yellow (actually we change the color of the column), so we use tbody tr:hover td selector.

More about designing table using CSS 3 can be read in the article: Styling Table Using CSS 3

Final Words

After following the above guidance, it can conclude that retrieving data from MySQL tables and display on HTML table using PHP is not such a hard task.

Nevertheless, we need to pay an attention to the presentation of the data, both for the HTML element and the CSS code.

If we’ll display data in a table, then make sure that the HTML elements are meet the standard, we can test it here: The W3c Markup Validation Service

Furthermore, make sure that the CSS code meets the targeted browser because there is some selector that begin exists in CSS 3 which are not supported by old browsers.

Related Post

9 Responses

Leave a comment

Like Us

Social