How to display data from database in php

Syed M Kashif August 15, 2021

  How to display data from database in php


 As we know that displaying data from database is a common operation while developing a website. We usually display the data to our users in different design formats like tables, cards, etc.  Every developer aims to display the data to users in an attractive way to grab more clients, sales, and hits. This article is mainly focused on fetching data from database in PHP. As we know, we have already inserted the data of books into our "book" table in our previous article. This article aims at displaying data from database in especially in tables. It is the second part of PHP CRUD where R stands for "read". Now let's start the article.

Create HTML mark up:

  • Create "viewbook.php" page to write your HTML markup.
  • Start PHP script and check if the SESSION variables are set. Redirect the user to the login page if SESSION variables are not set and the user is not logged in.
<?php
session_start();
if (!isset($_SESSION['id']) && !isset($_SESSION['email'])) {
  header("location: login.php");
}
?>
  • Include bootstrap 5 in your code to easily design your webpage.
  • Create navigation with a logout button and greeting message to the user.
<nav class="navbar navbar-expand-lg navbar-light">
  <div class="container-fluid">
    <a class="navbar-brand" href="#"><h3>My Library</h3></a>
    <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNavAltMarkup" aria-controls="navbarNavAltMarkup" aria-expanded="false" aria-label="Toggle navigation">
      <span class="navbar-toggler-icon"></span>
    </button>
    <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
      <div class="navbar-nav ms-auto mb-2 mb-lg-0 ">
        <a class="nav-link" href="#"><?php
			echo "Hello! " . $_SESSION['name']; ?></a>
        <a class="nav-link" href="includes/logout.inc.php">Log out</a>      
      </div>
    </div>
     </div>
</nav>
  • Create a bootstrap container where we display books from database.
  • Create a bootstrap grid with a single row and a single column.
  • Inside the column, data will be fetched from database in an HTML  table.
  • Create the table header using thead tag and write the names of all the columns such as title, author, description, and so on.
  • We will print the table body using PHP to print rows of data dynamically.
<div class="container-fluid">
       <div class="row justify-content-center my-5">
           <div class="col-10">
            <table class="table table-bordered">
              <thead class="thead">
                <tr>
                  <th scope="col">Title </th>
                  <th scope="col">Author</th>
                  <th scope="col">Description</th>
                  <th scope="col">Date</th>
                  <th scope="col">Download</th>
                  <th scope="col">Update</th>
                  <th scope="col">Delete</th>
                </tr>
              </thead>
            <tbody> 
            //php code here     
            </tbody>
        </table>
       </div>
    </div>
   </div>
  • Create a footer with copyright information.
  • Now add some style to give a decent gradient color to our navbar and table header.
<style type="text/css">
    .navbar{
        background: -webkit-linear-gradient(to right, #5bc0de, #fff);  
          background: linear-gradient(to right, #5bc0de, #fff);
    }

    .thead{
        background: -webkit-linear-gradient(to right, #5bc0de, #fff);  
          background: linear-gradient(to right, #5bc0de, #fff);
    }
    </style>

Fetch data from database:

  • Start PHP script inside tbody tag in our bootstrap row.
  • Write a Mysql select query to select all the books uploaded by the user in our "book" table.
  • Execute the query using PHP mysqli_query() function.
  • Then use PHP mysqli_num_rows() function to check if the number of rows returned by the query is greater than 0.
  • If the returned rows are equal to 0, print a "no record found" error message to the user.
  • Otherwise, use PHP mysqli_fetch_assoc() function to fetch the data in an associative array "$Row".
  • Use the above function in a while loop to fetch the data till the end of the table.
<?php
require 'includes/db.inc.php';
$user_id = $_SESSION['id'];
$query  = "SELECT * FROM book WHERE user_id= $user_id;";
$result = mysqli_query($conn, $query);
    if (mysqli_num_rows($result) > 0) {
         while ($Row = mysqli_fetch_assoc($result)) {
?>

Notice that the PHP script is closed after the opening bracket of while loop. Once we create a dynamic row using HTML We will start PHP script again to write the closing brackets of if statement and while loop. When writing PHP with HTML, we need to regularly start and close the PHP script according to the scenario.

Display fetched data in a table:

  • Create a single row using HTML tr tag to print the dynamic rows of data.
  • The while loop will continue to print the above row until the last fected record is displayed.
  • Use echo statement inside the td tag to display data from the $Row array.
  • Display the data of all the columns to the user.
  • Create a download button using HTML anchor tag for the pdf and docx files saved in the uploads folder.
  • Inside the href attribute of download button, write PHP script to set the path of the file.
  • The PHP script will concatenate the path "uploads/"and the file name.
  • Create delete and upload buttons using anchor tags.
  • We will work on update and delete buttons in our coming articles. 
<tr>
       <td><?php echo $Row['title']; ?></td>
       <td><?php echo $Row['author']; ?></td>
       <td><?php echo $Row['description']; ?></td>
       <td><?php echo $Row['date']; ?></td>  
       <td>
    <a class="btn btn-primary my-3" href="<?php echo "uploads/" . $Row['filename']; ?>">
        Download
    </a>
       </td>
        <td>
      <a  class="btn btn-warning my-3" href="#">
           Update
      </a>
        </td>
        <td>
      <a  class="btn btn-danger my-3" href="#">
            Delete
       </a>
         </td>
</tr>
//if and while loop closing brackets
<?php
  }
}
?>


Now see the result of the code in the below screenshot.
 
How to display data from database in php

Hurrah! We have successfully displayed data from database in PHP. Now see the complete code of this article at once. This will help you understand how data is fetched from database in PHP in sequence.

<?php
session_start();
if (!isset($_SESSION['id']) && !isset($_SESSION['email'])) {
  header("location: login.php");
}
?>

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">

    <title>View Books</title>
    <style type="text/css">
    .navbar{
        background: -webkit-linear-gradient(to right, #5bc0de, #fff);  
          background: linear-gradient(to right, #5bc0de, #fff);
    }

    .thead{
        background: -webkit-linear-gradient(to right, #5bc0de, #fff);  
          background: linear-gradient(to right, #5bc0de, #fff);
    }
    

    </style>
  </head>
  <body>
  <!--Starts Here NavBar-->
 <nav class="navbar navbar-expand-lg navbar-light">
  <div class="container-fluid">
    <a class="navbar-brand" href="#"><h3>My Library</h3></a>
    <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNavAltMarkup" aria-controls="navbarNavAltMarkup" aria-expanded="false" aria-label="Toggle navigation">
      <span class="navbar-toggler-icon"></span>
    </button>
    <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
      <div class="navbar-nav ms-auto mb-2 mb-lg-0 ">
        <a class="nav-link" href="#"><?php
			echo "Hello! " . $_SESSION['name']; ?></a>
        <a class="nav-link" href="includes/logout.inc.php">Log out</a>      
      </div>
    </div>
     </div>
</nav>

    <!--Addbook form container-->
 <div class="container-fluid">
       <div class="row justify-content-center my-5">
           <div class="col-10">
            <table class="table table-bordered">
              <thead class="thead">
                <tr>
                  <th scope="col">Title </th>
                  <th scope="col">Author</th>
                  <th scope="col">Description</th>
                  <th scope="col">Date</th>
                  <th scope="col">Download</th>
                  <th scope="col">Update</th>
                  <th scope="col">Delete</th>
                </tr>
              </thead>
        <tbody>      
	<?php
	require 'includes/db.inc.php';
	$user_id = $_SESSION['id'];
	$query  = "SELECT * FROM book WHERE user_id= $user_id;";
	$result = mysqli_query($conn, $query);
	if (mysqli_num_rows($result) > 0) {
	  while ($Row = mysqli_fetch_assoc($result)) {
	?>
           <tr>
              <td><?php  echo $Row['title']; ?></td>
              <td><?php echo $Row['author']; ?></td>
              <td><?php echo $Row['description'];?></td>
              <td><?php echo $Row['date'];?></td>  
              <td>
    <a class="btn btn-primary my-3" href="<?php echo "uploads/" . $Row['filename'] ?>">
        Download
    </a>
             </td>
              <td>
              <a  class="btn btn-warning my-3" href="#">
                  Update
              </a>
              </td>
              <td>
              <a  class="btn btn-danger my-3" href="#">
              Delete
              </a>
              </td>
            </tr>
    <?php
  }
}
?>
               </tbody>
            </table>
        </div>
    </div>
   </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>

  </body>
</html>

We have successfully learned how to display data from database in PHP with code examples. Now let's end the article here. In the next one, we will discuss displaying data from multiple database tables in bootstrap cards. We will use the concept of joins for that. Thanks a lot. Keep reading and supporting us.  
Tags

Post a Comment

0 Comments