Fetch Data from Multiple Tables with PHP and MySQL

Hi friends, In this tutorial we will see how to join more than one tables in PHP & MySQL using SQL join statement and without using join statement, though this is very common requirement when it comes to database and we need sometimes to fetch data from more than one table as per tables relation so in that case we can write up join query to get data and by joining them it can be showed in proper format. so here in this tutorial i have taken two tables one is category and another is product so that you can understand easily. before proceeding you can check out the demo so that you can get idea about joining tables, so let’s get started.

Fetch Data from Multiple Tables with PHP and MySQL

Consider Following Two tables.

1 : tbl_categories


CREATE TABLE IF NOT EXISTS `tbl_categories` (
  `cat_id` int(5) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(20) NOT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

Records for this table :


INSERT INTO `tbl_categories` (`cat_id`, `cat_name`) VALUES
(1, 'samsung'),
(2, 'nokia'),
(3, 'htc');

2 : tbl_products


CREATE TABLE IF NOT EXISTS `tbl_products` (
  `product_id` int(5) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(30) NOT NULL,
  `cat_id` int(5) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

Records for this table :


INSERT INTO `tbl_products` (`product_id`, `product_name`, `cat_id`) VALUES
(1, 'galaxy note', 1),
(3, 'lumia 530', 2),
(5, 'htc grid', 3);

– In above two tables there exist one common column(field) named ‘cat_id’ based on which you can establish relatinship between these two tables.
– Now suppose you want to retrieve details from products table along with product category.
– You can do so using the following two methods.
1. Referencing two tables in single SQL statement.
2. Using JOIN statements.

1. Single SQL statement

In order to retrieve information from from two related tables you need to reference two tables in your SQL query.
Without Join general syntax :


SELECT tbl_a.column1 , tbl_a.column2 
       tbl_b.column1 , tbl_b.column2
FROM   tbl_a , tbl_b
WHERE  tbl_a.commonfield=tbl_b.commonfield

– in the above syntax WHERE condition establish relation between tables based on the common field.

Example.

index.php


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Using single SQL</title>
<style>
table,td,th
{
 padding:10px;
 border-collapse:collapse;
 font-family:Georgia, "Times New Roman", Times, serif;
 border:solid #ddd 2px;
}
</style>
</head>
<body>
<table align="center" border="1" width="100%">
<tr>
<th>product id</th>
<th>product name</th>
<th>category name</th>
</tr>
<?php
mysql_connect("localhost","root");
mysql_select_db("dbtuts");
$res=mysql_query("SELECT c.* , p.* FROM tbl_categories c,tbl_products p WHERE c.cat_id=p.cat_id");
while($row=mysql_fetch_array($res))
{
 ?>
    <tr>
    <td><p><?php echo $row['product_id']; ?></p></td>
    <td><p><?php echo $row['product_name']; ?></p></td>
    <td><p><?php echo $row['cat_name']; ?></p></td>
    </tr>
    <?php
}
?>
</table>
</body>
</html>

In above example Query used as follow :


SELECT c . * , p . *
FROM tbl_categories c, tbl_products p
WHERE c.cat_id = p.cat_id

Query explained :
c = tbl_categories
p = tbl_products
* = fetch all records from both tables.
1. c and p is the alias name of these two tables.
2. Both table should have common column, first is primary and second is foreign key.
3. ‘cat_id’ is primary key in categories table.
4. ‘cat_id’ is foreign key in products table.

Output : 

Select Data From Multiple MySql Table

2. using JOIN

-In MySql JOINs allows the retrieval of data records from one or more tables having same relation between them, you can also use logical operator to get the desired output from MySql join queries.
1. Inner JOIN
– it is a default join type.
– when two tables are joined using INNER JOIN option it returns only those records from both tables or which there exist an entry for common field.
example :


SELECT tbl_a.column1 , tbl_a.column2
       tbl_b.column1 , tbl_b.column2
FROM   tbl_a INNER JOIN tbl_b
ON     tbl_a.commonfield=tbl_b.commonfield

2. Outer JOIN
Syntax :


SELECT tbl_a.column1 , tbl_a.column2
       tbl_b.column1 , tbl_b.column2
FROM   tbl_a LEFT OUTER JOIN tbl_b
ON     tbl_a.commonfield=tbl_b.commonfield

that’s it…
I hope this post is helpful to you…
Download this Script from the given link.