SELECT using BETWEEN in MYSQL and PHP Variable

Question

I have this code below.

<?php

require_once 'con.php';

$start_time_input = strtotime('08:00:00');
$finish_time_input = strtotime('17:00:00');

$total_time = 0;

$query = mysqli_query($con, "SELECT `start_break`, `finish_break` FROM `break_time` WHERE `start_break` AND `finish_break` BETWEEN '".$start_time_input."' AND '".$finish_time_input."' " );

while($tampil = mysqli_fetch_array($query)){

    $start_time_db = strtotime($tampil['start_break']);
    $finish_time_db = strtotime($tampil['finish_break']);

    if ($start_time_input <= $start_time_db AND $finish_time_input >= $finish_time_db) {

        $total_time = (($finish_time_input - $start_time_input) - ($finish_time_db - $start_time_db)) / 3600;

    } else {
        $total_time = ($finish_time_input - $start_time_input) / 3600;
    }

    echo $total_time;

}

?>

I try to execute the php file, the page shows me nothing, mysql query seems incorrect. But when I try to run this query in phpmyadmin SELECT start_break, finish_break FROM break_time WHERE start_break AND finish_break BETWEEN '08:00:00' AND '17:00:00' the query gave me the result as expected.

Anyone can help me with this? I've tried to do some research, but as far as I get that I just need to add strtotime to my variable, I did that already and nothing happens.

Any help will be much appreciated.

Answer 1

Here is your current query:

SELECT start_break,
       finish_break
FROM break_time
WHERE start_break AND finish_break BETWEEN '08:00:00' AND '17:00:00'

The WHERE clause is saying where start_break evaluates to true, and finish_break is between 8am and 5pm. This probably isn't what you want, because start_break will always evaluate to true. From the MySQL documentation:

MySQL evaluates any nonzero, non-NULL value to TRUE

Besides the logical problem in the WHERE clause, you were also trying to compare the start and finish columns directly against a time-only string. This won't work unless these columns are also time, which I doubt.

Here is the query which you probably logically intended:

SELECT start_break,
       finish_break
FROM break_time
WHERE DATE_FORMAT(start_break, '%H:%i:%s') BETWEEN '08:00:00' AND '17:00:00' AND   -- both the start break and
      DATE_FORMAT(finish_break, '%H:%i:%s') BETWEEN '08:00:00' AND '17:00:00'      -- finish break are within range

This assumes that the start_break and end_break columns are datetime and you only want to compare the time of day.