Fetch Data from Multiple Tables with PHP and MySQL | Coding Cage

Fetch Data from Multiple Tables with PHP and MySQL

By
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.




27 comments:

  1. Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\fetch-data-from-multiple-tables\index.php on line 26

    what this erro bro . how to slove it

    ReplyDelete
    Replies
    1. Hi wajid :)
      if you modified this script then check in "index.php" table names which in your database, hence this is working script....

      Delete
  2. thanx bro .. i hv sloved my issue

    ReplyDelete
  3. Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\fetch-data-from-multiple-tables\index.php on line 26 pls help me out, i downloaded this codes and try running it on my system after creating tables as explained using xampp but the above message keep showing up. pls help me out.

    ReplyDelete
    Replies
    1. check in "index.php" table names which in your database, and other fields ...

      Delete
  4. I want to query on more than 4 tables. is it possible? i tried the code, but it doesnt work. please give me example on query for 4 tables.

    ReplyDelete
    Replies
    1. Hello Ronilo,
      if i got time i will post tutorial about your query.

      Delete
  5. Link to download broken

    ReplyDelete
  6. Replies
    1. you're welcome, yusuf, keep visiting :)

      Delete
    2. link to download is broken please help

      Delete
  7. Hi sir, since it's using mysql, how does query go with mysqli?

    ReplyDelete
  8. Hi sir, how it goes using mysqli?

    ReplyDelete
  9. Thanks to you such a wonderful tutorial, you save me time trying to figure out of my own but Links to download is broken,pls provide a new link for download. Thank you

    ReplyDelete
  10. Nice job.It's very cool but the link broken

    ReplyDelete
  11. how to fetch deatils of particular single product's price, title and other detail fetch using in php

    ReplyDelete
  12. Sorry, the link is broken?

    ReplyDelete
  13. Thanks for providing this informative information you may also refer.
    http://www.s4techno.com/blog/2015/12/23/difference-mysql-ce-mysql-enterprise-edition/

    ReplyDelete
  14. Thanks for providing this informative information. it is very useful you may also refer- http://www.s4techno.com/blog/2016/08/07/interview-questions-of-mysql/

    ReplyDelete