skip to Main Content

I want to show shop availability status with every half an hour using Php and mysql,

For that i tried with following code which creating time slots for me

$duration="30";
    $start="10:00AM";
    $end="07:00PM";

    $start = new DateTime($start);
        $end = new DateTime($end);
        $start_time = $start->format('H:i');
        $end_time = $end->format('H:i');
        $i=0;
        while(strtotime($start_time) <= strtotime($end_time)){
            $start = $start_time;
            $end = date('H:i',strtotime('+'.$duration.' minutes',strtotime($start_time)));
            $start_time = date('H:i',strtotime('+'.$duration.' minutes',strtotime($start_time)));
            $i++;
            if(strtotime($start_time) <= strtotime($end_time)){
                $time[$i]['start'] = $start;
                $time[$i]['end'] = $end;
            }
        }
        print_R($time);

Above code showing following result (creating time slots properly)

Array
(
    [1] => Array
        (
            [start] => 10:00
            [end] => 10:30
        )

    [2] => Array
        (
            [start] => 10:30
            [end] => 11:00
        )
    ...// and so on

I want result something like this

Array
(
    [1] => Array
        (
            [start] => 10:00
            [end] => 10:30
            [status] => availiable
        )

    [2] => Array
        (
            [start] => 10:30
            [end] => 11:00
            [status] => booked
        )

Here is my table “booking” in phpmyadmin

id       shop_id     date        booking_start_time    booking_close_time
1        3           4-7-2019        10:00                 11:00

How can i do this ? Thanks in advance

2

Answers


  1. You need to compare time slot from database as below:

    while(strtotime($start_time) <= strtotime($end_time)){
        $start = $start_time;
        $end = date('H:i',strtotime('+'.$duration.' minutes',strtotime($start_time)));
        $start_time = date('H:i',strtotime('+'.$duration.' minutes',strtotime($start_time)));
        $i++;
        if(strtotime($start_time) <= strtotime($end_time)){
            $time[$i]['start'] = $start;
            $time[$i]['end'] = $end;
        }
        //Here you need to write query and fetch data.
        $todayDate = date('d-m-Y'); //Please check date format. It should be similar as your database date field format.
        //please use data binding instead of contacting variable.
        $selectQuery = "select `id` from `booking` where date = "'.$todayDate.'" and 
            (( `booking_start_time` >= "'.$start.'" AND `booking_start_time` <= "'.$start.'" ) || 
            (`booking_close_time` >= "'.$end.'" AND `booking_close_time` <= "'.$end.'")) ";
    
        // After, you need to exeucte this query and need to check query output. if it has records, then you need to show booked else available. as below
        $result = mysqli_query($con, $selectQuery);
        if ($result->num_rows) {
            $time[$i]['status'] = 'booked';
        } else {
            $time[$i]['status'] = 'availiable';
        }
    }
    print_R($time);
    

    Hope it helps you.

    Login or Signup to reply.
  2. This is the best solution for your query.

    select count(*) as aggregate from `bookings` where (`booking_date` = '$booking_date') and (( start_time >= '$start_time' AND end_time <= '$end_time' ) || (end_time >= '$start_time' AND start_time <= '$end_time'))
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search