Latest web development tutorials

Using Mysql connection

In previous chapters, we have learned that if read data in a table, which is relatively simple, but often need to read data from multiple tables in a real application.

This chapter we'll show you how to use the MySQL JOIN query data in two or more tables.

You can use Mysql in SELECT, UPDATE, and DELETE statements to JOIN joint multi-table queries.

JOIN according to function divided into the following three categories:

  • (Internal connection, or the equivalent connection) INNER JOIN: Get record two fields in the table matches relations.
  • LEFT JOIN (left connection): Get all the records from the left table, right table, even if there is no corresponding matching records.
  • RIGHT JOIN (right connection): In contrast with the LEFT JOIN, get the right table for all records, even if the left table has no corresponding matching records.

Database structure and data used in this section Download: w3big.sql .


Use INNER JOIN command prompt

We have two tables tcount_tbl and w3big_tbl in w3big database. Two data tables data are as follows:

Examples

Try the following examples:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use w3big;
Database changed
mysql> SELECT * FROM tcount_tbl;
+-----------------+----------------+
| w3big_author | w3big_count |
+-----------------+----------------+
| mahran          |             20 |
| mahnaz          |           NULL |
| Jen             |           NULL |
| Gill            |             20 |
| John Poul       |              1 |
| Sanjay          |              1 |
+-----------------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT * from w3big_tbl;
+-------------+----------------+-----------------+-----------------+
| w3big_id | w3big_title | w3big_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>

Next we come to connect more than two tables in the table to read w3big_tbl all w3big_author field w3big_count field values corresponding to the table in tcount_tbl using MySQL INNER JOIN (use may be omitted INNER JOIN, the same effect):

mysql> SELECT a.w3big_id, a.w3big_author, b.w3big_count FROM w3big_tbl a INNER JOIN tcount_tbl b ON a.w3big_author = b.w3big_author;
+-----------+---------------+--------------+
| w3big_id | w3big_author | w3big_count |
+-----------+---------------+--------------+
|         1 | John Poul     |            1 |
|         3 | Sanjay        |            1 |
+-----------+---------------+--------------+
2 rows in set (0.00 sec)

Above SQL statement is equivalent to:

mysql> SELECT a.w3big_id, a.w3big_author, b.w3big_count FROM w3big_tbl a, tcount_tbl b WHERE a.w3big_author = b.w3big_author;
+-------------+-----------------+----------------+
| w3big_id | w3big_author | w3big_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>


MySQL LEFT JOIN

MySQL left join and join different. MySQL LEFT JOIN will read all the data of the data table on the left, the right side of the table even if no corresponding data.

Examples

Try the following examples, w3big_tbl left table, tcount_tbl the right table, to understand MySQL LEFT JOIN applications:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use w3big;
Database changed
mysql> SELECT a.w3big_id, a.w3big_author, b.w3big_count FROM w3big_tbl a LEFT JOIN tcount_tbl b ON a.w3big_author = b.w3big_author;
+-------------+-----------------+----------------+
| w3big_id | w3big_author | w3big_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           2 | Abdul S         |           NULL |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)

The above examples use the LEFT JOIN, the statement reads all the selected data field to the left of the data table w3big_tbl, even if there is no corresponding right-hand table tcount_tbl in w3big_author field value.


MySQL RIGHT JOIN

MySQL RIGHT JOIN reads all the data to the right of the data table on the left side of the table even if no corresponding data.

Examples

Try the following examples, tcount_tbl left table, w3big_tbl the right table, to understand MySQL RIGHT JOIN applications:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use w3big;
Database changed
mysql> SELECT b.w3big_id, b.w3big_author, a.w3big_count FROM tcount_tbl a RIGHT JOIN w3big_tbl b ON a.w3big_author = b.w3big_author;
+-------------+-----------------+----------------+
| w3big_id | w3big_author | w3big_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           2 | Abdul S         |           NULL |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)

The above examples use a RIGHT JOIN, the statement reads all the selected data field to the right of the data table w3big_tbl, even if there is no corresponding table in the left tcount_tbl in w3big_author field value.


JOIN use in PHP scripts

PHP use mysql_query () function to execute SQL statements, you can use more than the same SQL statement as mysql_query () function parameters.

Try the following examples:

<?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.w3big_id, a.w3big_author, b.w3big_count FROM w3big_tbl a INNER JOIN tcount_tbl b ON a.w3big_author = b.w3big_author';

mysql_select_db('w3big');
$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['w3big_author']}  <br> ".
         "Count: {$row['w3big_count']} <br> ".
         "Tutorial ID: {$row['w3big_id']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>