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.

Please add this on top of your file to see the error: error_reporting(-1); ini_set('display_errors', 'On'); set_error_handler("var_dump"); Then you can see your error and debug the error. — Ruhul Amin
– Ruhul Amin, Commented Dec 23, 2016 at 9:03
Try this : $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" ); — Sandeep Kapil
– Sandeep Kapil, Commented Dec 23, 2016 at 9:34

Tim Biegeleisen · Accepted Answer · 2016-12-23 09:31:40Z

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.

It already goes wrong before that, strtotime converts the strings to integers...
I meant it as an addition to your answer, not as critique :-)
@jeroen Feel free to edit, yes I can see that his datetime strings are probably messed up. Wish I had the PHP skills to fix that.
@TimBiegeleisen I'm sorry but I didn't use datetime value, I use time only on my variable.

Collectives™ on Stack Overflow

SELECT using BETWEEN in MYSQL and PHP Variable

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related