当前位置：首页 > PHP

php实现销售统计

2026-02-16 22:15:58PHP

PHP实现销售统计的方法

销售统计通常涉及数据收集、处理和展示。以下是几种常见的方法：

数据库设计与数据收集

创建销售数据表，包含字段如订单ID、产品ID、销售数量、销售金额、销售日期等。使用MySQL或其他数据库存储数据。

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    quantity INT,
    amount DECIMAL(10,2),
    sale_date DATE
);

数据插入示例

通过PHP表单或API接收销售数据并插入数据库。

$product_id = $_POST['product_id'];
$quantity = $_POST['quantity'];
$amount = $_POST['amount'];
$sale_date = $_POST['sale_date'];

$sql = "INSERT INTO sales (product_id, quantity, amount, sale_date) 
        VALUES ('$product_id', '$quantity', '$amount', '$sale_date')";
$conn->query($sql);

统计查询

按时间范围、产品类别等维度统计销售数据。

// 按日期统计
$sql = "SELECT sale_date, SUM(amount) as total_amount FROM sales GROUP BY sale_date";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
    echo $row['sale_date'] . ": " . $row['total_amount'] . "<br>";
}

// 按产品统计
$sql = "SELECT product_id, SUM(quantity) as total_quantity FROM sales GROUP BY product_id";

数据可视化

使用图表库如Chart.js展示统计结果。

// 获取数据
$sql = "SELECT sale_date, SUM(amount) as total FROM sales GROUP BY sale_date";
$result = $conn->query($sql);
$labels = [];
$data = [];
while($row = $result->fetch_assoc()) {
    $labels[] = $row['sale_date'];
    $data[] = $row['total'];
}

<canvas id="salesChart"></canvas>
<script>
var ctx = document.getElementById('salesChart').getContext('2d');
var chart = new Chart(ctx, {
    type: 'line',
    data: {
        labels: <?php echo json_encode($labels); ?>,
        datasets: [{
            label: '销售额',
            data: <?php echo json_encode($data); ?>,
            backgroundColor: 'rgba(75, 192, 192, 0.2)',
            borderColor: 'rgba(75, 192, 192, 1)'
        }]
    }
});
</script>

高级分析功能

实现同比环比分析、趋势预测等。

// 同比分析
$currentYear = date('Y');
$lastYear = $currentYear - 1;

$sql = "SELECT MONTH(sale_date) as month, SUM(amount) as total 
        FROM sales 
        WHERE YEAR(sale_date) = $currentYear 
        GROUP BY MONTH(sale_date)";
$currentResult = $conn->query($sql);

$sql = "SELECT MONTH(sale_date) as month, SUM(amount) as total 
        FROM sales 
        WHERE YEAR(sale_date) = $lastYear 
        GROUP BY MONTH(sale_date)";
$lastResult = $conn->query($sql);

报表导出

支持将统计结果导出为Excel或PDF格式。

// 使用PHPExcel库导出Excel
require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();

$sql = "SELECT * FROM sales";
$result = $conn->query($sql);
$row = 1;
while($data = $result->fetch_assoc()) {
    $sheet->setCellValue('A'.$row, $data['product_id']);
    $sheet->setCellValue('B'.$row, $data['quantity']);
    $sheet->setCellValue('C'.$row, $data['amount']);
    $row++;
}

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="sales_report.xls"');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

实现注意事项

确保数据库连接安全，使用预处理语句防止SQL注入。

$stmt = $conn->prepare("INSERT INTO sales (product_id, quantity, amount, sale_date) VALUES (?, ?, ?, ?)");
$stmt->bind_param("iids", $product_id, $quantity, $amount, $sale_date);
$stmt->execute();

对于大数据量统计，考虑使用缓存或定时任务预计算统计结果。

定期备份销售数据，确保数据安全。

php实现销售统计