Blog

MySQLi – Using Joins

This is good enough for simple takes, but in most of the real world MySQL usages, you will often need to get data from multiple tables in a single query.

You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table.

You can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables. We will see an example of the LEFT JOIN also which is different from the simple MySQL JOIN.

Using Joins at the Command Prompt

Assume we have two tables tcount_tbl and Adglob_tbl, . Now take a look at the examples given below −

Example

The following examples −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use ADGLOB;
Database changed
mysql> SELECT * FROM tcount_tbl;
+-----------------+----------------+
| Adglob_author   | Adglob_count |
+-----------------+----------------+
|      mahi       |       20       |     
|      Naz        |      NULL      |        
|       Zen       |      NULL      |          
|      Yash       |       20       |          
|    Zafrul       |        1       |      
|     jay         |        1       |        
+-----------------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT * from Adglob_tbl;
+-------------+----------------+-----------------+-----------------+
| Adglob_id   | Adglob_title   | Adglob_author   | submission_date |
+-------------+----------------+-----------------+-----------------+
|      1      |  Learn PHP     |     Zafrul khan |    2007-05-24   |   
|      2      |  Learn MySQL   |      Subrat S   |    2007-05-24   |   
|      3      | Lear   Java    |      Jay        |    2007-05-06   |   
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>

Now we can write an SQL query to join these two tables. This query will select all the authors from table Adglob_tbl and will pick up the corresponding number of tutorials from the tcount_tbl.

mysql> SELECT a.Adglob_id, a.Adglob_author, b.Adglob_count
   → FROM Adglob_tbl a, tcount_tbl b
   → WHERE a.Adglob_author = b.Adglob_author;
+-------------+-----------------+----------------+
| Adglob_id   | Adglob_author   | Adglob_count   |
+-------------+-----------------+----------------+
|      1      |    Zafrul khan  |        1       |
|      3      |     Jay         |        1       |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>

Using Joins in a PHP Script

PHP uses mysqli query() or mysql_query() function to get records from a MySQL tables using Joins. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli→query($sql,$resultmode)
Sr.No.Parameter & Description
1$sqlRequired – SQL query to get records from multiple tables using Join.
2$resultmodeOptional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

First create a table in MySQL using following script and insert two records.

create table tcount_tbl(
   Adglob_author VARCHAR(40) NOT NULL,
   Adglob_count int
);
insert into tcount_tbl values('Ajit', 3);
insert into tcount_tbl values('Subu', 1);

Example

Try the following example to get records from a two tables using Join. −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Using joins on MySQL Tables</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'ADGLOB';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = 'SELECT a.Adglob_id, a.Adglob_author, b.Adglob_count
				FROM Adglob_tbl a, tcount_tbl b
				WHERE a.Adglob_author = b.Adglob_author';
		 
         $result = $mysqli→query($sql);
           
         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Author: %s, Count: %d <br />", 
                  $row["Adglob_id"], 
                  $row["Adglob_author"], 
                  $row["Adglob_count"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Id: 1, Author: Ajit, Count: 3
Id: 2, Author: Ajit, Count: 3
Id: 3, Author: Ajit, Count: 3
Id: 5, Author: Subu, Count: 1

MySQL LEFT JOIN

A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives some extra consideration to the table that is on the left.

If I do a LEFT JOIN, I get all the records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join: thus ensuring (in my example) that every AUTHOR gets a mention.

Example

Try the following example to understand the LEFT JOIN.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use ADGLOB;
Database changed
mysql> SELECT a.Adglob_id, a.Adglob_author, b.Adglob_count
   → FROM Adglob_tbl a LEFT JOIN tcount_tbl b
   → ON a.Adglob_author = b.Adglob_author;
+-------------+-----------------+----------------+
| Adglob_id   | Adglob_author   | Adglob_count   |
+-------------+-----------------+----------------+
|      1      |    Zafrul khan  |       1        |
|      2      |     Subrat S    |      NULL      |
|      3      |     Jay         |       1        |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)

You would need to do more practice to become familiar with JOINS. This is slightly a bit complex concept in MySQL/SQL and will become more clear while doing real examples.

Leave a Reply