How To Fetch Data From Database In PHP And Display In HTML Table

Trang chủ » Echo Th Php » How To Fetch Data From Database In PHP And Display In HTML Table

Có thể bạn quan tâm

There are few methods using which you can use fetch data from database in PHP and display it into HTML table. In this tutorial, we have explained the following method to fetch data from database in PHP and display it into an HTML table.

  • Basic Program using Loop
  • Using Ajax
  • Using Data Table

1) Basic Program using Loop

<?php $host = "127.0.0.1"; //IP of your database $userName = "root"; //Username for database login $userPass = ""; //Password associated with the username $database = "example-database"; //Your database name $connectQuery = mysqli_connect($host,$userName,$userPass,$database); if(mysqli_connect_errno()){ echo mysqli_connect_error(); exit(); }else{ $selectQuery = "SELECT * FROM `tbl_users` ORDER BY `user_id` ASC"; $result = mysqli_query($connectQuery,$selectQuery); if(mysqli_num_rows($result) > 0){ }else{ $msg = "No Record found"; } } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta http-equiv="X-UA-Compatible" content="ie=edge"> <title>HTML and PHP code</title> </head> <body> <h1>Display user list using HTML and PHP</h1> <?=$msg;?> <table border="1px" style="width:600px; line-height:40px;"> <thead> <tr> <th>Name</th> <th>Email</th> <th>Status</th> <th>Registrating Date</th> </tr> </thead> <tbody> <?php while($row = mysqli_fetch_assoc($result)){?> <tr> <td><?php echo $row['user_firstName'].$row['user_lastName']; ?></td> <td><?php echo $row['user_email']; ?></td> <td><?php if($row['user_status'] == 1){ echo "Active"; }else{ echo "Deactive"; } ?></td> <td><?php echo $row['user_registrationDate']; ?></td> <tr> <?}?> </tbody> </table> </body> </html>

Output

Fetch Data From Database in PHP and Display in HTML Table

2) Using Ajax

HTML File Code

<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta http-equiv="X-UA-Compatible" content="ie=edge"> <title>HTML and PHP code</title> </head> <body> <h1>Display user list using HTML and PHP</h1> <?=$msg;?> <table border="1px" style="width:600px; line-height:40px;"> <thead> <tr> <th>Name</th> <th>Email</th> <th>Registrating Date</th> </tr> </thead> <tbody id="tableBody"> </tbody> </table> </body> </html> <script src="https://code.jquery.com/jquery-3.4.1.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script> <script> $( document ).ready(function() { $.ajax({ url: 'fetch.php', mothod: 'post', dataType: 'json', success:function(data){ let string = ''; $.each(data, function(key, value){ string += `<tr> <td>${value['user_firstName']} ${value['user_lastName']}</td> <td>${value['user_email']}</td> <td>${value['user_registrationDate']}</td> </tr>`; }); $('#tableBody').append(string); }, error:{ } }); }); </script>

PHP File Code

<?php $host = "127.0.0.1"; //IP of your database $userName = "root"; //Username for database login $userPass = ""; //Password associated with the username $database = "example-database"; //Your database name $connectQuery = mysqli_connect($host,$userName,$userPass,$database); if(mysqli_connect_errno()){ echo mysqli_connect_error(); exit(); }else{ $selectQuery = "SELECT * FROM `tbl_users` ORDER BY `user_id` ASC"; $result = mysqli_query($connectQuery,$selectQuery); if(mysqli_num_rows($result) > 0){ $result_array = array(); while($row = mysqli_fetch_assoc($result)){ array_push($result_array, $row); } } echo json_encode($result_array); } ?>

Output

Fetch Data From Database in PHP and Display in HTML Table

3) Using Data Table

HTML File Code

<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <meta http-equiv="X-UA-Compatible" content="ie=edge"> <title>HTML and PHP code</title> <style> table, td, th { border: 1px solid #ddd; text-align: left; } table { border-collapse: collapse; width: 100%; } th, td { padding: 15px; } </style> </head> <body> <h1>Display user list using HTML and PHP</h1> <?= $msg; ?> <table id="my-example"> <thead> <tr> <th>Name</th> <th>Email</th> <th>Registrating Date</th> </tr> </thead> <tbody> </tbody> </table> </body> </html> <script src="https://code.jquery.com/jquery-3.4.1.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script> <link rel="stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css"> <script type="text/javascript" charset="utf8" src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script> <script> $(document).ready(function() { $('#my-example').dataTable({ "bProcessing": true, "sAjaxSource": "fetch.php", "aoColumns": [{ mData: 'user_firstName' }, { mData: 'user_email' }, { mData: 'user_registrationDate' } ] }); }); </script>

PHP File Code

<?php $host = "127.0.0.1"; //IP of your database $userName = "root"; //Username for database login $userPass = ""; //Password associated with the username $database = "example-database"; //Your database name $connectQuery = mysqli_connect($host,$userName,$userPass,$database); if(mysqli_connect_errno()){ echo mysqli_connect_error(); exit(); }else{ $selectQuery = "SELECT * FROM `tbl_users` ORDER BY `user_id` ASC"; $result = mysqli_query($connectQuery,$selectQuery); if(mysqli_num_rows($result) > 0){ $result_array = array(); while($row = mysqli_fetch_assoc($result)){ array_push($result_array, $row); } } $results = ["sEcho" => 1, "iTotalRecords" => count($result_array), "iTotalDisplayRecords" => count($result_array), "aaData" => $result_array ]; echo json_encode($results); } ?>

Output

Fetch Data From Database in PHP and Display in HTML Table

Từ khóa » Echo Th Php