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