How can PHP be used to join and aggregate data from multiple database tables to generate a list of top goal scorers in a football league?

To generate a list of top goal scorers in a football league by joining and aggregating data from multiple database tables, we can use SQL queries to retrieve player information from the player table and goal information from the goals table. We can then join these tables on the player_id column and use aggregate functions like COUNT to calculate the total number of goals scored by each player. Finally, we can order the results by the total number of goals in descending order to get the list of top goal scorers. 

<?php
// Connect to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "football_db";

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

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

// SQL query to get the list of top goal scorers
$sql = "SELECT players.player_id, players.player_name, COUNT(goals.goal_id) AS total_goals
        FROM players
        LEFT JOIN goals ON players.player_id = goals.player_id
        GROUP BY players.player_id
        ORDER BY total_goals DESC";

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

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Player ID: " . $row["player_id"]. " - Name: " . $row["player_name"]. " - Total Goals: " . $row["total_goals"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Keywords

PHP database tables join aggregate goal scorers

Related Questions