How to write PHP script by using MySQL JOINS inside it to join two MySQL tables?

MySQL JOINs allow you to combine data from multiple tables based on related columns. In PHP, you can execute JOIN queries using database connection functions and process the results to display combined information from different tables.

Sample Database Tables

Let's work with two sample tables to demonstrate the JOIN operation ?

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)

PHP Script with MySQL JOIN

Here's a complete PHP script that joins the two tables to fetch author information along with their tutorial counts ?

<?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
";
    mysql_close($conn);
?>

Expected Output

Author:John Poul 
Count: 1 
Tutorial ID: 1 
--------------------------------
Author:Sanjay 
Count: 1 
Tutorial ID: 3 
--------------------------------
Fetched data successfully

Modern Approach with MySQLi

Since mysql_* functions are deprecated, here's the modern approach using MySQLi ?

<?php
    $dbhost = 'localhost';
    $dbuser = 'root';
    $dbpass = 'rootpassword';
    $database = 'TUTORIALS';
    
    $conn = new mysqli($dbhost, $dbuser, $dbpass, $database);
    
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
    $sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
            FROM tutorials_tbl a
            INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
    
    $result = $conn->query($sql);
    
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            echo "Author: " . $row["tutorial_author"] . "<br>";
            echo "Count: " . $row["tutorial_count"] . "<br>";
            echo "Tutorial ID: " . $row["tutorial_id"] . "<br>";
            echo "--------------------------------<br>";
        }
    } else {
        echo "0 results";
    }
    
    $conn->close();
?>

Conclusion

MySQL JOINs in PHP allow you to combine data from multiple tables efficiently. While the legacy example uses mysql_* functions, modern PHP applications should use MySQLi or PDO for better security and performance.