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.
Records for this table :
2 : tbl_products
Records for this table :
- 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.
Without Join general syntax :
In above example Query used as follow :
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 :
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 :
2. Outer JOIN
Syntax :
that's it...
I hope this post is helpful to you...
Download this Script from the given link.
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.commonfieldExample.
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_idQuery 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 :
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.commonfield2. 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.commonfieldthat's it...
I hope this post is helpful to you...
Download this Script from the given link.
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
ReplyDeletewhat this erro bro . how to slove it
Hi wajid :)
Deleteif you modified this script then check in "index.php" table names which in your database, hence this is working script....
thanx bro .. i hv sloved my issue
ReplyDeleteGlad You Solve It :)
DeleteWarning: 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.
ReplyDeletecheck in "index.php" table names which in your database, and other fields ...
DeleteThanks, bro, it's working.
ReplyDeleteyou're welcome, rakib :)
DeleteI 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.
ReplyDeleteHello Ronilo,
Deleteif i got time i will post tutorial about your query.
Link to download broken
ReplyDeletereally help, thanks brother..
ReplyDeleteyou're welcome, yusuf, keep visiting :)
Deletelink to download is broken please help
DeleteHi sir, since it's using mysql, how does query go with mysqli?
ReplyDeleteHi sir, how it goes using mysqli?
ReplyDeleteThanks 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
ReplyDeleteGreat help, many thanks
ReplyDeleteNice job.It's very cool but the link broken
ReplyDeletehi i need help in sql
ReplyDeleteany one is here?
ReplyDeletei need help in my sql
ReplyDeletehow to fetch deatils of particular single product's price, title and other detail fetch using in php
ReplyDeleteSorry, the link is broken?
ReplyDeletethank u brother
ReplyDeletehow can i do this by MySQLi Object-oriented)
ReplyDelete