What are some best practices for aggregating data in MySQL using PHP?

When aggregating data in MySQL using PHP, it is important to use proper SQL queries to fetch and manipulate the data efficiently. One common approach is to use aggregate functions like SUM(), COUNT(), AVG(), etc., along with GROUP BY to group the data as needed. Additionally, it is recommended to sanitize user inputs to prevent SQL injection attacks. 

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

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

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

// Query to aggregate data (example: sum of sales by month)
$sql = "SELECT MONTH(sale_date) as month, SUM(sale_amount) as total_sales FROM sales_table GROUP BY MONTH(sale_date)";

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

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Month: " . $row["month"]. " - Total Sales: $" . $row["total_sales"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Keywords

MySQL PHP data aggregation SQL queries performance optimization

Related Questions