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:
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:
- How to Create Table Using HTML 5 and CSS 3
- Styling Table Using CSS 3
- 10 Clean HTML Table Design Using CSS 3
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.
1PHP 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:
dfor two digit date, 01 to 31Ffor Fulltext month, January through DecemberYfor 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 is0then 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.
2HTML 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
3CSS 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 impliesoddwill match the odd rows (1, 3, 5, etc.) andevenwill 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-childselector, date column usingtbody td:nth-child(4)selector, and amount column usingtbody td:last-childselector. - 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
:emptyselector, in the above example we apply it tobody td:emptyselector. - When the mouse is over the row (
:hoverevent), we change the color of the row into yellow (actually we change the color of the column), so we usetbody tr:hover tdselector.
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.
http://webdevzoom.com/display-mysql-data-html-5-table-using-php/
'[ 기타 활동 ] > SQL' 카테고리의 다른 글
| PHP7 에서 MySql 접속하기 (0) | 2018.07.21 |
|---|---|
| CSV 화일 MySql에 임포트하기 (0) | 2018.07.15 |
| 특정 문자가 포함된 데이터 검색 (0) | 2018.07.15 |
| mySQL AUTO INCREMENT 값 초기화 (0) | 2018.07.15 |
| MySQL CSV 파일 임포트 익스포트 하기 (0) | 2018.07.14 |