How can INNER JOIN be effectively used in PHP to retrieve data from multiple tables with a many-to-many relationship?

When dealing with a many-to-many relationship between tables in a database, INNER JOIN can be effectively used in PHP to retrieve data from multiple tables by joining them on a common field. This allows for the retrieval of related data from both tables based on their relationship. By using INNER JOIN, you can ensure that only records with matching values in the specified columns are returned, making it a powerful tool for querying data from multiple tables in a many-to-many relationship. 

<?php
// Establish a database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Query to retrieve data from multiple tables using INNER JOIN
$sql = "SELECT table1.column, table2.column
        FROM table1
        INNER JOIN table1_table2 ON table1.id = table1_table2.table1_id
        INNER JOIN table2 ON table1_table2.table2_id = table2.id";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Table 1 Column: " . $row["column"] . " - Table 2 Column: " . $row["column"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Keywords

INNER JOIN PHP many-to-many relationship data retrieval multiple tables

Related Questions