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.
Sample MySQL database
Before connecting to database we need to create database.
An example I have given sample database structure below.
--
-- 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;
-- 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
--
-- 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');
-- 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.
$host='';
$username='';
$password='';
$databasename='';
$mysqli = new mysqli($host, $username, $password, $password);
$username='';
$password='';
$databasename='';
$mysqli = new mysqli($host, $username, $password, $password);
Now we need to write the select query to display
$selectquery = 'SELECT id,name,email,created_date from users';
$result = $mysqli->query($selectquery);
$result = $mysqli->query($selectquery);
View it in HTML 5 table format
<!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>
<!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>
<!--[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.
1 thought on “Display data in HTML 5 table from MySQL using PHP”