如何用PHP+MySQL动态计算车辆MPG？解决历史里程获取难题

核心思路 #

要解决乱序录入、里程修改导致MPG数据不准确的问题，需放弃依赖数据库存储的MPG字段，改为在报表生成时动态计算：先按日期（及主键）排序获取指定车辆的燃油记录，在PHP遍历过程中记录上一条记录的里程数，用「当前里程 - 上一里程」的差值除以当前加油量得到实时MPG，同时修复原代码的SQL注入风险。

关键修改点 #

• 用预处理语句替代SQL字符串拼接，彻底避免SQL注入
• 调整排序逻辑：按Date升序排列，同一日期的记录额外按Fuel_ID排序，保证顺序稳定
• 在PHP循环中维护上一条记录的里程值，动态计算MPG，处理第一条记录无前置数据的情况
• 修复查询条件：车辆外键为数值类型，用=替代LIKE更准确

修改后的完整代码 #

<?php
include 'includes/conn.php';

$vehicle = '';
$date1 = '';
$date2 = '';
$v_name = '';

if (isset($_POST["submit"])) {
    $vehicle = $_POST["vehicle"];
    $date1 = $_POST["date1"];
    $date2 = $_POST["date2"];

    // 获取车辆编号
    $sqlc = "SELECT Bus_no FROM Vehicles WHERE V_ID = ?";
    $stmt4 = mysqli_prepare($conn, $sqlc);
    mysqli_stmt_bind_param($stmt4, "i", $vehicle);
    mysqli_stmt_execute($stmt4);
    $result4 = mysqli_stmt_get_result($stmt4);
    $row3 = mysqli_fetch_array($result4);
    $v_name = $row3['Bus_no'];
}
?>

<h2 class='display-6'> From <?php echo $date1; ?> To <?php echo $date2; ?> </h2><br>
<br><h2 class='display-5'>Fuel Report For <?php echo $v_name; ?></h2><br>

<?php
if (isset($_POST["submit"])) {
    // 主查询：获取燃油记录关联员工、车辆信息
    $sql = "SELECT Fuel.*, Employees.FirstName, Employees.LastName, Vehicles.Bus_no 
            FROM Fuel 
            INNER JOIN Employees ON Fuel.Employee = Employees.Emp_ID
            INNER JOIN Vehicles ON Fuel.Vehicle = Vehicles.V_ID 
            WHERE Fuel.Vehicle = ? 
            AND Fuel.Date BETWEEN ? AND ? 
            ORDER BY Fuel.Date ASC, Fuel.Fuel_ID ASC";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_bind_param($stmt, "iss", $vehicle, $date1, $date2);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);

    // 统计总加油量
    $sqla = "SELECT SUM(Gallons) as gallons FROM Fuel WHERE Vehicle = ? AND Date BETWEEN ? AND ?";
    $stmt2 = mysqli_prepare($conn, $sqla);
    mysqli_stmt_bind_param($stmt2, "iss", $vehicle, $date1, $date2);
    mysqli_stmt_execute($stmt2);
    $result2 = mysqli_stmt_get_result($stmt2);
    $row = mysqli_fetch_array($result2);
    $gals = $row['gallons'] ?? 0;

    // 统计总费用
    $sqlb = "SELECT SUM(Cost) as cost FROM Fuel WHERE Vehicle = ? AND Date BETWEEN ? AND ?";
    $stmt3 = mysqli_prepare($conn, $sqlb);
    mysqli_stmt_bind_param($stmt3, "iss", $vehicle, $date1, $date2);
    mysqli_stmt_execute($stmt3);
    $result3 = mysqli_stmt_get_result($stmt3);
    $row2 = mysqli_fetch_array($result3);
    $price = $row2['cost'] ?? 0;

    echo "<table>";
    echo "<tr>";
    echo  "<th>Date</th>";
    echo  "<th>Vehicle</th>";
    echo  "<th>Miles</th>";
    echo  "<th>Gallons</th>";
    echo  "<th>MPG</th>";
    echo  "<th>Price</th>";
    echo  "<th>Driver</th>";
    echo  "</tr>";

    if ($result->num_rows > 0) {
        $previous_miles = null; // 存储上一条记录的里程
        while ($row1 = $result->fetch_assoc()) {
            // 动态计算MPG
            if ($previous_miles === null) {
                $calculated_mpg = "N/A"; // 第一条记录无前置里程
            } else {
                if ($row1['Gallons'] > 0) {
                    $calculated_mpg = number_format(($row1['Miles'] - $previous_miles) / $row1['Gallons'], 2);
                } else {
                    $calculated_mpg = "Invalid"; // 避免除以0错误
                }
            }

            // 输出表格行
            echo "<tr><td>" . $row1['Date'] . "</td><td>" . $row1['Bus_no'] . "</td><td>" . $row1['Miles'] . "</td><td>" . $row1['Gallons'] .  "</td><td>" . $calculated_mpg .  "</td><td>$" . $row1['Cost'] . "</td><td>" . $row1['FirstName'] . " " . $row1['LastName'] ."</td></tr>";
            
            // 更新上一条里程为当前记录的里程
            $previous_miles = $row1['Miles'];
        }

        // 输出总计行
        echo "<tr><td></td><td></td><td><b>Totals</b></td><td><b>".$gals."</b></td><td></td><td><b>$".$price."</b></td><td></td></tr>";
        echo "</table>";
    } else {
        echo "<p>No result. The Database must be empty.</p>";
    }
}
?>

代码说明 #

1. 预处理语句：所有SQL查询都使用mysqli_prepare和mysqli_stmt_bind_param，避免直接拼接用户输入，防止SQL注入
2. MPG计算逻辑：
   • 初始化$previous_miles为null，标记第一条记录
   • 从第二条记录开始，用「当前里程 - 上一里程」的差值除以当前加油量，保留2位小数
   • 处理加油量为0的异常情况，避免除以0报错
3. 排序稳定性：新增ORDER BY Fuel.Date ASC, Fuel.Fuel_ID ASC，确保同一日期的记录按主键顺序排列，避免乱序

内容的提问来源于stack exchange，提问作者lomfs24