我的数据库中有3个表，用于向用户显示产品的可用性。

Table 1 has category,year,brand,desc,size,colour,mpn (this is the product table)
Table 2 has mpn,qty,eta (this is the on order table)
Table 3 has mpn,qty (this is the reserved table)

这是一个表单页面，人们可以在其中搜索可用的项目。
我加入了表1和表2，以显示当前的列表，以便无问题地获得可用性。我的问题是处理为特定人群保留的物品数量。
案例：我有一件商品，8月份有5件到货，9月份还有3件。我预订了6张票。
我希望显示包含两个装运日期的项目，但也能够显示下一个可用的eta是两个日期中较晚的一个。
我猜我得用php来比较每批货的总数量和总的预订量，然后再从那里开始，但目前脑子里还是乱七八糟的。

<div class="row align-items-start">
        <form class="form-horizontal" action="index.php" method="post">
            <div class="form-group">
                <label class="control-label" for="bikeyear">Bike Year</label>
                <input type="text" class="form-control" placeholder="Enter Model Year" name="bikeyear">

                <label class="control-label" for="bikebrand">Bike Brand</label>
                <input type="text" class="form-control" placeholder="Enter Brand" name="bikebrand">

                <label class="control-label" for="bikedesc">Bike Description</label>
                <input type="text" class="form-control" placeholder="Enter Model Name" name="bikedesc">

                <label class="control-label" for="bikesize">Bike Size</label>
                <input type="text" class="form-control" placeholder="Enter Size" name="bikesize"><br />

                <input type="submit" class="btn btn-primary" name="submit" value="Submit">
            </div>
        </form>
</div>
</div>
<?php
if(ISSET($_POST['submit'])){
    $connection = mysqli_connect('localhost', 'user', 'secret', 'db');    
    if($connection === false){
        die("ERROR: Could no connect to database!" . mysqli_connect_error());
    }

    $bikeyear = $_POST['bikeyear']; 
    $bikebrand = $_POST['bikebrand']; 
    $bikedesc = $_POST['bikedesc'];
    $bikesize = $_POST['bikesize'];

    $bikeyear = mysqli_real_escape_string($connection, $bikeyear);
    $bikebrand= mysqli_real_escape_string($connection, $bikebrand);
    $bikedesc = mysqli_real_escape_string($connection, $bikedesc);
    $bikesize = mysqli_real_escape_string($connection, $bikesize);

    $query = "SELECT ";
    $query.= "bikes.bikeyear, ";
    $query.= "bikes.bikebrand, ";
    $query.= "bikes.bikedesc, ";
    $query.= "bikes.bikecolour, ";
    $query.= "bikes.bikesize, ";
    $query.= "bikes.bikempn, ";
    $query.= "IFNULL(bobikes.bikeqty,0) AS bikeqtyn, ";
    $query.= "IFNULL(DATE_FORMAT(bobikes.bikeeta,'%M %Y'),'Unknown') AS bikeetaf ";
    //$query.= "IFNULL(resbikes.bikeqty,0) AS reserved ";
    $query.= "FROM bikes ";
    $query.= "LEFT JOIN bobikes ON bobikes.mpn = bikes.bikempn ";
    //$query.= "LEFT JOIN resbikes ON resbikes.mpn = bikes.bikempn ";
    $query.= "WHERE bikeyear LIKE '%". $bikeyear ."%' AND bikebrand LIKE '%". $bikebrand ."%' AND bikedesc LIKE '%". $bikedesc ."%' AND bikesize LIKE '%". $bikesize ."%' ";
    //$query.= "GROUP BY bobikes.bikeeta ";
    $query.= "ORDER BY bikes.bikempn, bobikes.bikeeta";

    print $query;
    if($result = mysqli_query($connection, $query)){
        if(mysqli_num_rows($result) > 0){
            echo "<table>";
                echo "<tr>";
                    echo "<th>Year</th>";
                    echo "<th>Brand</th>";
                    echo "<th>Model</th>";
                    echo "<th>Colour</th>";
                    echo "<th>Size</th>";
                    echo "<th>Part #</th>";
                    echo "<th>On Order</th>";
                    echo "<th>ETA</th>";
                    //echo "<th>Reserved</th>";
                    echo "</tr>";
            while($row = mysqli_fetch_assoc($result)){
                echo "<tr>";
                    echo "<td>" . $row['bikeyear'] . "</td>";
                    echo "<td>" . $row['bikebrand'] . "</td>";
                    echo "<td>" . $row['bikedesc'] . "</td>";
                    echo "<td>" . $row['bikecolour'] . "</td>";
                    echo "<td>" . $row['bikesize'] . "</td>";
                    echo "<td>" . $row['bikempn'] . "</td>";
                    echo "<td>" . $row['bikeqtyn'] . "</td>";
                    echo "<td>" . $row['bikeetaf'] . "</td>";
                    //echo "<td>" . $row['reserved'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
        } else{
            echo "No records matching your query were found.";
        }
    } else{
        echo "Error: Could not execute $query. " . mysqli_error($connection);
    }
}```