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
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.
Pingback : How to make revisions of your content using PHP - Anil Labs
Nice post!