So far, we've learned to get data from a table. It is simple to need, but in most realistic use of MySQL, it is often necessary to put data from multiple tables in a single query.
You can use a single SQL query from multiple tables. The join behavior in MySQL means that two or more tables into a table can use joins to join the MySQL table in the Select,update and DELETE statements. We'll see an example of a left join with a simple MySQL connection.
to use joins at the command prompt:
Suppose the complete list of our two-table tutorials Tcount_tbl and Tutorials_tbl is as follows:
Example:
Try the following example:
root@host# mysql-u root-p password;
Enter password:******* mysql> Use tutorials;
Database changed mysql> SELECT * from TCOUNT_TBL; +-----------------+----------------+
| Tutorial_author |
Tutorial_count | +-----------------+----------------+
| Mahran | 20 | | Mahnaz | NULL | | Jen | NULL | | Gill | 20 | | John Poul | 1 | | Sanjay |
1 |
+-----------------+----------------+ 6 rows in Set (0.01 sec) mysql> SELECT * from TUTORIALS_TBL; +-------------+----------------+-----------------+-----------------+
| tutorial_id | Tutorial_title | Tutorial_author |
Submission_date | +-------------+----------------+-----------------+-----------------+
| 1 | Learn PHP | John Poul | 2007-05-24 | | 2 | Learn MySQL | Abdul S | 2007-05-24 | | 3 | JAVA Tutorial | Sanjay |
2007-05-06 |
+-------------+----------------+-----------------+-----------------+ 3 rows in Set (0.00 sec) mysql>
Now, by joining these two tables, we can write a SQL query. This query selects all the authors from the table TUTORIALS_TBL and will pick up the corresponding number of tutorials from TCOUNT_TBL.
Mysql> SELECT a.tutorial_id, A.tutorial_author, B.tutorial_count
-> from Tutorials_tbl A, tcount_tbl b
-& Gt WHERE a.tutorial_author = B.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | Tutorial_count
| +-------------+-----------------+----------------+
| 1 | John Poul | 1 |
| 3 | Sanjay | 1 |
+-------------+-----------------+----------------+
2 rows in Set (0.01 sec)
mysql>
To use joins in PHP scripts:
You can use any of the above SQL queries for PHP scripts. You only need to execute the SQL query through the PHP function mysql_query (), and then get the results in the normal way.
Example:
Try the following example:
<?php
$dbhost = ' localhost:3036 ';
$dbuser = ' root ';
$dbpass = ' Rootpassword ';
$conn = mysql_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
die (' Could not connect: '. Mysql_error ());
}
$sql = ' SELECT a.tutorial_id, A.tutorial_author, b.tutorial_count from
tutorials_tbl A, tcount_tbl b
WHERE A.tutorial_author = B.tutorial_author ';
mysql_select_db (' tutorials ');
$retval = mysql_query ($sql, $conn);
if (! $retval)
{
die (' could not get data: '. mysql_error ());
}
while ($row = Mysql_fetch_array ($retval, MYSQL_ASSOC))
{
echo author:{$row [' Tutorial_author ']} <br> ".
" Count: {$row [' Tutorial_count ']} <br> '.
' Tutorial ID: {$row [' tutorial_id ']} <br> '.
' --------------------------------<br> ";
}
echo "fetched data successfully\n";
Mysql_close ($conn);
? >
MySQL Left connection:
A simple connection and a MySQL left connection are different. A MySQL left join provides extra consideration to the table on the left-hand side.
If you do a LEFT join, all of the records that you get are matched in the same way, and you get an extra record of each mismatched record, a join in the left-hand table-thus ensuring that each author is associated (in this example):
Instance:
Try the following example to understand the left JOIN:
root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
mysql> SELECT a.tutorial_id, A.tutorial_author, B.tutorial_count-> from
tutorials_tbl A left JOIN tcount_tbl b
-> on a.tutorial_author = B.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | Tutorial_count
| +-------------+-----------------+----------------+
| 1 | John Poul | 1 |
| 2 | Abdul S | NULL |
| 3 | Sanjay | 1 |
+-------------+-----------------+----------------+
3 rows in Set (0.02 sec)
More practice is needed to become familiar with joins. This is a complex concept that will become clearer in the mysql/sql.