How to fetch data from two tables in mysql using php

Syed M Kashif August 25, 2021

How to fetch data from two tables in mysql using php

When developing a website, sometimes we need to fetch data from two or more tables at once using PHP. In such cases, we use joins to display data to our users.  In our scenario, we want to display all the books uploaded on our system along with the user information. For that, we will display data of books table and user table to our users. The best approach is to write a complex SQL query and take the join of the two tables.  In this article, we will cover joins with a practical example. So, let's start the article.

What is inner join:

Based on the logical relationship between two or more tables, the inner join clause is used to create a new virtual table by combining the rows with matching values in the two or more tables. Inner join is used to fetch data from multiple tables based on a condition. The SQL syntax of inner join is given below.

SELECT Col_List
FROM FirstTable
INNER JOIN SecondTable
ON FirstTable.ColName = SecondTable.ColName

now let's write a practical query and execute it to fetch records from two tables using MySQL and PHP.  

PHP code to fetch data from two tables:

  • First of all, create a bootstrap container and a single row.
  • Start the PHP script to write your PHP code inside the row.
  • Include the database connectivity file in your code.
  • Write a complex SQL query to fetch data of two or more tables.
  • The query will consist of an inner join of the two tables. We can take join of more than two tables as well.
  • Execute the query using mysqli_query() function and fetch the result of the query in $result.
  • Use PHP OR operator to display error message using mysqli_error() function in case of the failure of query.
  • Use the if statement to check if the records returned by the query are greater than 0.
  • Then use the mysqli_fetch_assoc() function to fetch a row from both the database tables inside the $Row associative array.
  • Use the above function inside a while loop to fetch the records till the end of the table.
See the code of all the above-mentioned steps below.

<div class="container-fluid">
    <div class="row my-5">
  <?php
  require 'includes/db.inc.php';
  //$user_id = $_SESSION['id'];
  $query  = "
  SELECT book.title, author, description, date, filename, user.name, email
  FROM book INNER JOIN user ON book.user_id = user.id";
  
  $result = mysqli_query($conn, $query) or die("error".mysqli_error($conn));
  if (mysqli_num_rows($result) > 0) {
    while ($Row = mysqli_fetch_assoc($result)) {
      
   ?>       
    </div>
 </div>

Now let's create bootstrap 5 cards and display all the data fetched using the above PHP code.

Bootstrap 5 cards to display data from two tables:

  • Make sure you have included bootstrap 5 cdn in your code.
  • Create a col-4 bootstrap column.
  • Inside the column create a bootstrap 5 card. The bootstrap card has two parts; card header and card body.
  •  The card header will display information like publisher name and date.
  • Embed the PHP script inside card header to display the publisher's name and the publishing date using echo statement. 
  • Now inside card body, display the title, author, email, and description of the book using PHP code.
  • Create a download button using HTML anchor tag and bootstrap btn classes.
  •  Embed PHP script inside the href attribute and set the path dynamically.
  • Concatenate the folder name "uploads/" with the filename to create a valid path for the file download.
  • At the end open a PHP script to write the closing brackets of the if statement and the while loop. 
  • Apply a gradient color to the card header to make it look better.
Now see the complete code to seek assistance on fetching data from two tables in MySQL using 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">
 
    .card-header{
        background: -webkit-linear-gradient(to right, #5bc0de, #fff);  
        background: linear-gradient(to right, #5bc0de, #fff);
    }

    </style>
  </head>
  <body>
  

    <!--Addbook form container-->
 <div class="container-fluid">
    <div class="row my-5">
<?php
  require 'includes/db.inc.php';
  $query  = "
  SELECT book.title, author, description, date, filename, user.name, email
  FROM book INNER JOIN user ON book.user_id = user.id";
  
  $result = mysqli_query($conn, $query) or die("error".mysqli_error($conn));
  if (mysqli_num_rows($result) > 0) {
    while ($Row = mysqli_fetch_assoc($result)) {
      
?>     
    <div class="col-4">
      <div class="card my-3">
      <div class="card-header">
        <?php echo "Published by ".$Row['name']." on ".$Row['date'] ?>
      </div>
      <div class="card-body text-center ">
        <h4 class="card-title"><?php  echo $Row['title'] ?></h4>
        <p class="card-text"><?php  echo "Written by ".$Row['author'] ?></p>
        <p class="card-text"><?php  echo $Row['description'] ?></p>
        <a href="<?php echo "uploads/".$Row['filename']; ?>" class="btn btn-primary">Download</a>
      </div>
    </div>
    </div>
<?php

    }
  }        
?>       
    </div>
 </div>
  </body>
</html>

The result of the above code is given below.

How to fetch data from two tables in mysql using php


Here is the end of this article. We will meet again with another informative article soon. Keep reading our content and thanks for supporting us.

Tags

Post a Comment

0 Comments