php - 在 MYSQL 和 PHP 变量中使用 BETWEEN 进行选择

Question

我在下面有这段代码。

<?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;

}

?>

我尝试执行 php 文件，页面没有任何显示，mysql 查询似乎不正确。但是，当我尝试在 phpmyadmin SELECT start_break, finish_break FROM break_time WHERE start_break AND finish_break BETWEEN '08:00:00' AND '17:00:00' 中运行此查询时，查询给了我如下结果预期。

谁能帮我解决这个问题？我尝试做一些研究，但据我所知，我只需要将 strtotime 添加到我的变量中，我已经这样做了，但没有任何反应。

任何帮助将不胜感激。

Answer 1

这是您当前的查询:

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

WHERE 子句表示 start_break 计算结果为真，finish_break 在上午 8 点到下午 5 点之间。这可能不是您想要的，因为 start_break 的计算结果总是为真。来自 MySQL documentation :

MySQL evaluates any nonzero, non-NULL value to TRUE

除了 WHERE 子句中的逻辑问题之外，您还试图将开始和结束列直接与仅时间字符串进行比较。除非这些列也是时间，否则这是行不通的，我对此表示怀疑。

这是您可能在逻辑上想要的查询:

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

这假设 start_break 和 end_break 列是 datetime 并且您只想比较一天中的时间。