Friday 29 April 2016

Fetch Data from Two or more Table Join using PHP and MySql



In this video you can find how to merge two table data using inner join keyword and load that data on web page using php and mysql. If you want to load data from multiple table then at that time you can use inner join keyword and merge two table or more table with common column between two or more table. Here I have use two table brand and product. Brand table has two column brand_id and brand_name, brand_id is a primary key. And second is product table, in which there is three column product_id, product_name and brand_id. product_id is a primary key of this table and brand_id is foreign key of this table. Here brand_id is a common column in both table. So with help of inner join query it return common column data of both table.

Source Code

Database


 --  
 -- Table structure for table `brand`  
 --  
 CREATE TABLE IF NOT EXISTS `brand` (  
  `brand_id` int(11) NOT NULL AUTO_INCREMENT,  
  `brand_name` varchar(250) NOT NULL,  
  PRIMARY KEY (`brand_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;  
 --  
 -- Dumping data for table `brand`  
 --  
 INSERT INTO `brand` (`brand_id`, `brand_name`) VALUES  
 (1, 'Samsung'),  
 (2, 'Sony'),  
 (3, 'Motorola'),  
 (4, 'Xiaomi');  
 -- --------------------------------------------------------  
 --  
 -- Table structure for table `product`  
 --  
 CREATE TABLE IF NOT EXISTS `product` (  
  `product_id` int(11) NOT NULL AUTO_INCREMENT,  
  `product_name` varchar(250) NOT NULL,  
  `brand_id` int(11) NOT NULL,  
  PRIMARY KEY (`product_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;  
 --  
 -- Dumping data for table `product`  
 --  
 INSERT INTO `product` (`product_id`, `product_name`, `brand_id`) VALUES  
 (1, 'Samsung Galaxy A9', 1),  
 (2, 'Samsung Galaxy S7', 1),  
 (3, 'Samsung Galaxy S6 edge', 1),  
 (4, 'Xperia Z5 Premium', 2),  
 (5, 'Xperia M5 Dual', 2),  
 (6, 'Xperia C5 uplta', 2),  
 (7, 'Moto G Turbo', 3),  
 (8, 'Moto X Force', 3),  
 (9, 'Redmi 3 Pro', 4),  
 (10, 'Mi 5', 4);  

multiple_table.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "zzz");  
 $sql = "SELECT * FROM brand INNER JOIN product ON brand.brand_id = product.brand_id";  
 $result = mysqli_query($connect, $sql);  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | Fetch Data from Two or more Table Join using PHP and MySql</title>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
      </head>  
      <body>  
           <br />  
           <div class="container" style="width:500px;">  
                <h3 align="">Fetch Data from Two or more Table Join using PHP and MySql</h3><br />                 
                <div class="table-responsive">  
                     <table class="table table-striped">  
                          <tr>  
                               <th>Brand Name</th>  
                               <th>Product Name</th>  
                          </tr>  
                          <?php  
                          if(mysqli_num_rows($result) > 0)  
                          {  
                               while($row = mysqli_fetch_array($result))  
                               {  
                          ?>  
                          <tr>  
                               <td><?php echo $row["brand_name"];?></td>  
                               <td><?php echo $row["product_name"]; ?></td>  
                          </tr>  
                          <?php  
                               }  
                          }  
                          ?>  
                     </table>  
                </div>  
           </div>  
           <br />  
      </body>  
 </html>  

10 comments:

  1. Hi there i love your tuts mate for real the best ones online i ever seen. I wanna ask you lets say i have this DB connected to users table and tickets table, now i want to give access only to a user from the same DB but i got several users, how can i add the logged in user to see only the data from the tables that are connected to him?

    ReplyDelete
  2. Hi Webslesson
    I want to select only the last record from other table. In this case i only want to select the last or lastly added model of each brand from products table.
    how i can do that your help will be greatly appreciated.
    Thanks In advance

    ReplyDelete
  3. Simply awesome tutorial. Thank you so much.

    ReplyDelete
  4. Can we put condition(WHERE) , and if it can where should I put ?

    ReplyDelete
  5. Do you have any idea how much you have helped us? Do you have a patron contribution portal for us to support your work? Please drop it in my email.

    ReplyDelete