What is the best way to calculate the sum of values for each year in a SQL database using PHP?

To calculate the sum of values for each year in a SQL database using PHP, you can use a SQL query to group the values by year and then use PHP to fetch and display the results. You can achieve this by selecting the year from the date column, summing the values, and grouping by year in the SQL query. Then, fetch the results in PHP and display them in a table or any desired format. 

<?php
// Connect to the database
$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);
}

// SQL query to calculate sum of values for each year
$sql = "SELECT YEAR(date_column) AS year, SUM(value_column) AS total_value
        FROM your_table
        GROUP BY YEAR(date_column)
        ORDER BY YEAR(date_column)";

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

// Display results
if ($result->num_rows > 0) {
    echo "<table><tr><th>Year</th><th>Total Value</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["year"] . "</td><td>" . $row["total_value"] . "</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?>

Keywords

PHP SQL database sum calculation year-wise aggregation.

Related Questions