PHP MySQL多表关联：企业车辆油耗统计概览开发需求

Hey there! Let's walk through how to build that full vehicle overview page where you calculate fuel consumption using mileage and refuel data from your MySQL tables. I'll break this down into actionable steps with code examples that fit your setup.

Step 1: Clarify Assumed Table Structures #

Since you didn't share exact schemas, I'll use logical, common structures for the two tables:

cars Table #

• id (INT, PRIMARY KEY): Unique vehicle identifier
• brand (VARCHAR): Vehicle brand (e.g., Ford)
• model (VARCHAR): Vehicle model (e.g., F-150)
• license_plate (VARCHAR): License plate number
• year (INT): Manufacturing year (optional extra detail)

fuel Table #

• id (INT, PRIMARY KEY): Unique refuel record ID
• car_id (INT, FOREIGN KEY): Links to cars.id
• date (DATE): Refuel date
• km (INT): Odometer reading at refuel
• liter (DECIMAL): Amount of fuel added in liters

Step 2: Fetch & Organize Data from MySQL #

First, we'll pull all vehicles and their associated refuel records, sorted by odometer reading (critical for accurate consumption calculations). Here's a PHP example using PDO for secure database access:

<?php
// Database connection (update credentials to match your setup)
$pdo = new PDO('mysql:host=localhost;dbname=your_database;charset=utf8', 'your_username', 'your_password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Query to get cars with their fuel records, sorted by vehicle and mileage
$stmt = $pdo->query("
    SELECT c.*, f.date, f.km, f.liter
    FROM cars c
    LEFT JOIN fuel f ON c.id = f.car_id
    ORDER BY c.id, f.km ASC
");

// Organize data into a nested array: key = car ID, value = vehicle details + fuel records
$companyVehicles = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $carId = $row['id'];
    // Initialize vehicle entry if it doesn't exist
    if (!isset($companyVehicles[$carId])) {
        $companyVehicles[$carId] = [
            'brand' => $row['brand'],
            'model' => $row['model'],
            'license_plate' => $row['license_plate'],
            'fuel_log' => []
        ];
    }
    // Add fuel record only if it's not empty (skip vehicles with no refuels)
    if (!empty($row['km'])) {
        $companyVehicles[$carId]['fuel_log'][] = [
            'date' => date('d.m.', strtotime($row['date'])), // Match your example's date format
            'km' => (int)$row['km'],
            'liter' => (float)$row['liter']
        ];
    }
}
?>

Step 3: Integrate Your Existing Fuel Calculation Function #

You mentioned you already have a PHP function for calculating consumption. Let's assume it takes an array like your $array example and returns consumption values (or adds a consumption key to each record). Here's how to plug it in:

<?php
// Your existing fuel calculation function (example placeholder matching your data format)
function calculateFuelConsumption($fuelLog) {
    $consumptionData = [];
    // Can't calculate consumption with fewer than 2 records
    if (count($fuelLog) < 2) {
        return $consumptionData;
    }
    // Loop through records to calculate consumption between each refuel
    for ($i = 1; $i < count($fuelLog); $i++) {
        $prevEntry = $fuelLog[$i-1];
        $currentEntry = $fuelLog[$i];
        $kmTraveled = $currentEntry['km'] - $prevEntry['km'];
        
        // Skip invalid mileage (e.g., odometer rolled back)
        if ($kmTraveled <= 0) {
            $consumptionData[] = null;
            continue;
        }
        // Calculate L/100km: (liters used / km traveled) * 100
        $consumption = ($currentEntry['liter'] / $kmTraveled) * 100;
        $consumptionData[] = round($consumption, 2); // Round to 2 decimal places
    }
    // Add a null for the first entry (no prior data to calculate from)
    array_unshift($consumptionData, null);
    return $consumptionData;
}

// Apply calculation to each vehicle's fuel log
foreach ($companyVehicles as &$vehicle) {
    $consumptions = calculateFuelConsumption($vehicle['fuel_log']);
    // Merge consumption values into the fuel log entries
    foreach ($vehicle['fuel_log'] as $index => &$logEntry) {
        $logEntry['consumption'] = $consumptions[$index];
    }
}
?>

Step 4: Render the Overview Page #

Now let's build the HTML to display all vehicles with their fuel data and calculated consumption in a clean, readable format:

<!DOCTYPE html>
<html>
<head>
    <title>Company Vehicle Fleet Overview</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        .vehicle-section { margin-bottom: 30px; border-bottom: 1px solid #eee; padding-bottom: 20px; }
        table { border-collapse: collapse; width: 100%; margin-top: 10px; }
        th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
        th { background-color: #f5f5f5; }
        .no-data { color: #666; font-style: italic; }
    </style>
</head>
<body>
    <h1>Company Vehicle Fleet Overview</h1>
    <?php foreach ($companyVehicles as $carId => $vehicle): ?>
        <div class="vehicle-section">
            <h2><?= htmlspecialchars($vehicle['brand']) ?> <?= htmlspecialchars($vehicle['model']) ?> (<?= htmlspecialchars($vehicle['license_plate']) ?>)</h2>
            <?php if (empty($vehicle['fuel_log'])): ?>
                <p class="no-data">No refuel records available for this vehicle.</p>
            <?php else: ?>
                <table>
                    <tr>
                        <th>Refuel Date</th>
                        <th>Odometer (km)</th>
                        <th>Fuel Added (L)</th>
                        <th>Fuel Consumption (L/100km)</th>
                    </tr>
                    <?php foreach ($vehicle['fuel_log'] as $logEntry): ?>
                        <tr>
                            <td><?= htmlspecialchars($logEntry['date']) ?></td>
                            <td><?= $logEntry['km'] ?></td>
                            <td><?= number_format($logEntry['liter'], 2) ?></td>
                            <td>
                                <?php if ($logEntry['consumption'] !== null): ?>
                                    <?= $logEntry['consumption'] ?>
                                <?php else: ?>
                                    <span class="no-data">N/A</span>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                </table>
            <?php endif; ?>
        </div>
    <?php endforeach; ?>
</body>
</html>

Quick Tips for Improvement #

• Security: Always sanitize outputs (we used htmlspecialchars to prevent XSS attacks) and use prepared statements if you add user-controlled filters.
• Edge Cases: Handle vehicles with no refuel records, invalid mileage drops, or partial refuels (if applicable to your use case).
• Performance: Add database indexes on fuel.car_id and fuel.km if you have a large number of records to speed up queries.

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