How I fetch country name from another table using query in PHP

fetch data from another table using query in php

In this tutorial, you will learn how to fetch data from a database particular table rows like county names from another table using a query in PHP.

Here, in the below code we are exploring the JQuery Ajax example with PHP MySQL for sending an Ajax get request and fetching data from MySQL database server using PHP and PDO.

And know I created three empty files and named as:

dbcon.php
fetch.html
get.php

And so, create a SQL database ,I already have database name "phptut" and table name "customer".

Database Table:


CREATE TABLE customer
(
cust_id int AUTO_INCREMENT PRIMARY KEY,
cust_name varchar(50) NOT null ,
address varchar(255) NOT null,
country varchar(50) NOT null,
city varchar(50) NOT null
);

Insert values into table:


INSERT INTO `customer`(`cust_name`, `address`, `country`, `city`) 
VALUES (‘chandra’,’paris street’,’india’,’chennai’);
INSERT INTO `customer`(`cust_name`, `address`, `country`, `city`) 
VALUES (‘anbu’,’canada street’,’india’,’chennai’);
INSERT INTO `customer`(`cust_name`, `address`, `country`, `city`) 
VALUES (‘aravidh’,’french street’,’india’,’vellore’);
INSERT INTO `customer`(`cust_name`, `address`, `country`, `city`) 
VALUES (‘manikandan’,’china street’,’india’,’banglore’);
INSERT INTO `customer`(`cust_name`, `address`, `country`, `city`) 
VALUES (‘priya’,’paris street’,’india’,’chennai’);
INSERT INTO `customer`(`cust_name`, `address`, `country`, `city`) 
VALUES (‘vidhya’,’french street’,’india’,’chennai’);

HTML (fetch.html)


<div class="container py-5">
   <input type="button" id="display" value="Get records">
   <br/>
   <div class="records"></div>
</div>

First , we need to connect the database and here, I named dbcon.php

connect to database:


<?php
$servername = "localhost";
$username = "root";
$password = "";

try {
  $conn = new PDO("mysql:host=$servername;dbname=phptut", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  //echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

JQuery


$(document).ready(function() {
     $('#display').click(function(){
        $.ajax({
            type: "GET",
            url: "get.php",
            dataType:"html",
            success: function(data) {
                $('.records').html(data);
            }
        });

    });
});

In the above, script user click on the button id "display" Ajax request get the record from the get.php and return the data by the HTML method in JQuery.

get.php


<?php
      $sql = "SELECT * FROM customer ";
      $result = $conn->prepare($sql);
	  $result->execute();
	  
      echo '<table class="table table-bordered">';
      echo '<th>customer_id</th>';
      echo '<th>customer_name</th>';
	  echo '<th>country_name</th>';
      while($row= $result ->fetch()){
	   $customer_id = $row['cust_id'];
	   $customer_name= $row['cust_name'];
	   $county = $row['country'];	
       echo '<tr>';
	   echo '<td>'.$customer_id.'</td>';
	   echo '<td>'.$customer_name.'</td>';
       echo '<td>'.$county.'</td>';
       echo '</tr>';
       
      } 
      echo '</table>';
   ?>

Fetch data from another table using JQuery and PHP

In the above script normal the fetch data from the MySQL database and single table and now, we are going to fetch the data from another table using a query in PHP

Here, I have two tables for example the tables are a student table and a customer, and this table not meaning to fetch just for example. we are fetching the data using inner join to connect two tables in PHP and fetch like country name and student name combine into one table.

Get.php

Just need to edit the get.php files and get the output.


<?php
      $sql = "SELECT * FROM customer inner join student_details on 
             customer.cust_id = student_details.student_id";
      $result = $conn->prepare($sql);
	  $result->execute();
	  
      echo '<table class="table table-bordered">';
      echo '<th>customer_id</th>';
      echo '<th>customer_name</th>';
	  echo '<th>country_name</th>';
	  echo '<th>student_name</th>';
      while($row= $result ->fetch()){
	   $customer_id = $row['cust_id'];
	   $customer_name= $row['cust_name'];
	   $county = $row['country'];
       $stud_name = $row['student_name'];	   
       echo '<tr>';
	   echo '<td>'.$customer_id.'</td>';
	   echo '<td>'.$customer_name.'</td>';
       echo '<td>'.$county.'</td>';
	   echo '<td>'.$stud_name.'</td>';
       echo '</tr>';
       
      } 
      echo '</table>';
   ?>