PHP -SQL Select Where Greater Than Minute And Hour

Question

I wanna get specific shift time from my database but this scripts return "0 results";

This is my database

id | shift_title | start_time | end_time |
------------------------------------------
1  |   shift1    |   00:00    |  08:30   |
2  |   shift2    |   08:30    |  12:00   |
3  |   shift3    |   12:00    |  19:30   |
4  |   shift4    |   19:00    |  00:00   |

and this is my php script

include 'config.php';
$create_time = date('H:i');

        $sql = "SELECT * FROM shift_times WHERE start_time <= '$create_time' AND end_time > '$create_time'";
        $result = $conn->query($sql);
        if ($result->num_rows > 0) {
          while($row = $result->fetch_assoc()) {
            echo $row["shift_title"];
            }
        } else {
            echo "0 results";
        }
        $conn->close();

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 26 '21 at 20:53
Try `start_time` instead of `$start_time`. It's a column name, not a variable. Same for $end_time — ADyson, Sep 26 '21 at 21:02
You're trying to do a time-based comparison on a string. Mysql doesn't know how to do that. You need to make your columns a [TIME](https://dev.mysql.com/doc/refman/8.0/en/time.html) or some other [date-related](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-syntax.html) column type. — Cully, Sep 26 '21 at 21:50

score 0 · Answer 1 · edited Sep 27 '21 at 06:44

0

Try:

DATE_FORMAT(start_time, '%h:%i') <= DATE_FORMAT('$create_time', '%h:%i') AND DATE_FORMAT(end_time, '%h:%i') > DATE_FORMAT('$create_time', '%h:%i')

OR Try this:

TIME(start_time) <= TIME('$create_time') AND TIME(end_time) > TIME('$create_time')

edited Sep 27 '21 at 06:44

ClydeFrog

912
1
14
38

answered Sep 27 '21 at 05:36

Innoxent Ali Shan

1
3

PHP -SQL Select Where Greater Than Minute And Hour

1 Answers1