Select Next Available Date if Limit has reached (PHP & MySQL)

Question

How to create a loop for my IF statement if the following day has reached its limit and it should stop once the date is available?

The scenario...

I am working on a Logistics application where a specific courier can only handle limited transactions per day. The application should give the customer the best possible date of delivery as per the product he ordered.

eg.

Product A = 13 Days to be delivered
Product B = 14 Days to be delivered

So, if the customer selected Product A, the best delivery date will be today's date + 13 days.

Let say the Best Delivery Date is 27/11/2019.

But since the courier has a per-day limit of deliveries, I should count first in the Database if the limit has reached or not. If the limit has reached on that day then I will show to the customer the next available date which is 28/11/2019.

My Code

$courierLimitPerDay = 25;

$date = date('Y-m-d', strtotime($today. ' + '.$productsDeliveyDay.' days'));

$sql = "SELECT COUNT(*) FROM `product_delivery` WHERE delivery_date = '$date' AND courier_id = '$courierId'";
$query = $connect->query($sql);
$result = $query->fetch_row();

if($result[0] == $courierLimitPerDay) {
  $date = date('Y-m-d', strtotime($date . ' + 1 days'));
  $sql = "SELECT COUNT(*) FROM `product_delivery` 
          WHERE delivery_date = '$date' AND courier_id = '$courierId'";
  $query = $connect->query($sql);
  $result = $query->fetch_row();
}

echo "Best Delivery Date is on ". $date;

also: `if($result[0] == $courierLimitPerDay)` should be `if($result[0] > $courierLimitPerDay)` — Syllz, Nov 14 '19 at 14:03
See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Nov 14 '19 at 14:19

score 3 · Accepted Answer · answered Nov 14 '19 at 14:04

You may be able to get away with doing this purely in SQL. This code selects the number of deliveries on each date for that particular courier and on or after the date you want.

The main thing is to GROUP BY the delivery date and say that the number of deliveries is less than the maximum (HAVING deliveries < 4 with the appropriate number rather than just 4).

It then orders it by the delivery date, so earliest first and only the first row.

SELECT delivery_date, COUNT(*) as deliveries
    FROM product_delivery 
    WHERE courier_id = '$courierId'
        and delivery_date >= '$date'
    group by delivery_date
    having deliveries < 4
    order by delivery_date
    limit 1

Note that if it doesn't return a row, this means there aren't any slots in the current days with deliveries, so just use the date after the last

SELECT max(delivery_date)
    FROM product_delivery 
    WHERE courier_id = '$courierId'
    limit 1

Last thing is to learn to use prepared statements as this can save a lot of hassle in the future.

You are an angel from up above @Nigel Ren. This one works perfectly! Thank you very much! — Borgy ES, Nov 17 '19 at 04:16
Regarding the Prepared Statements, do I really have to do it even if I'm using the mysqli_real_escape_string already? — Borgy ES, Nov 17 '19 at 05:13
Hi @Nigel Ren, I have a small issue. How not to skip the date with 0 records? — Borgy ES, Nov 17 '19 at 13:01

score 0 · Answer 2 · answered Nov 14 '19 at 13:56

The best solution will be to build for that recursive function. Besed on What in layman's terms is a Recursive Function using PHP

function get_delivery_date( $date, $courierId, $courierLimitPerDay = 25) {
    $sql = "SELECT COUNT(*) FROM `product_delivery` WHERE delivery_date = '$date' AND courier_id = '$courierId'";
    $query = $connect->query($sql);
    $result = $query->fetch_row();
    if( $result[0] == $courierLimitPerDay) {
        $date = date('Y-m-d', strtotime($date . ' + 1 days'));
        return get_delivery_date( $date, $courierId, $courierLimitPerDay);
    } else {
        return $date;
    }
}

This doesn't work. But thank you anyway, I'm gonna study this Recursive Function in PHP. — Borgy ES, Nov 17 '19 at 04:17

Select Next Available Date if Limit has reached (PHP & MySQL)

2 Answers2