SELECT using BETWEEN MYSQL and a 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 am trying to execute a php file, the page does not show me anything, the mysql query seems to be wrong. 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.

Can anybody help me? I tried to do some research, but as far as I know, I just need to add strtotime to my variable, I did it already and nothing happens.

Any help would be greatly appreciated.

Answer 1

Here is your current request:

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

The WHERE indicates where start_break is true and finish_break is between 8:00 and 17:00. This is probably not what you want, because start_break will always evaluate to true. From the MySQL documentation:

MySQL computes any nonzero value other than NULL to TRUE

Besides the logical problem in the WHERE , you also tried to compare the start and end columns directly with a time string. This will not work if these columns are also not time, which I doubt.

Here is a query that you probably logically assumed:

 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 

The columns are assumed to be start_break and end_break datetime columns, and you want to compare the time of day.