How can INNER JOIN be effectively used in PHP to link two tables based on a common ID for user-specific data retrieval?

To link two tables based on a common ID for user-specific data retrieval, you can use INNER JOIN in PHP. This allows you to combine rows from both tables where the ID matches, providing a single result set with information from both tables for a specific user. 

<?php
// Establish a connection to the database
$connection = new mysqli("localhost", "username", "password", "database");

// Define the user ID for which data will be retrieved
$user_id = 1;

// Query to retrieve user-specific data using INNER JOIN
$query = "SELECT users.*, user_data.*
          FROM users
          INNER JOIN user_data ON users.id = user_data.user_id
          WHERE users.id = $user_id";

$result = $connection->query($query);

// Process the result set
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        // Access data from both tables using $row['column_name']
        echo "User ID: " . $row['id'] . "<br>";
        echo "Username: " . $row['username'] . "<br>";
        echo "Email: " . $row['email'] . "<br>";
        echo "Additional Data: " . $row['additional_data'] . "<br>";
    }
} else {
    echo "No results found.";
}

// Close the database connection
$connection->close();
?>

Keywords

INNER JOIN PHP tables common ID user-specific data

Related Questions