How can JOIN be effectively used to connect tables in a database query?
When using JOIN in a database query, you can effectively connect tables by specifying the columns from each table that should be used to establish the relationship between them. By using JOIN, you can retrieve data from multiple tables based on a related column, allowing you to combine information from different sources in a single query.
<?php
// Connect to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);
// Select data from two tables using JOIN
$sql = "SELECT users.username, orders.order_id
FROM users
JOIN orders ON users.user_id = orders.user_id";
$result = $conn->query($sql);
// Output the results
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "Username: " . $row["username"]. " - Order ID: " . $row["order_id"]. "<br>";
}
} else {
echo "0 results";
}
// Close the connection
$conn->close();
?>
Related questions
-
CSV file MySQL table subqueries
-
syntax highlighting color schemes PHP applications
-
SQL injection PDO prepared statements