CODEWITHSUNDEEP
phpmysqlsql
user1881190
user1881190

Reputation:

Issues selecting who's on shift based on time in MySQL

Disclaimer: still a beginner.

I am making a small script to show who is supposed to be on shift at the present, filtered by role. These shifts do go over midnight. As of right now, the time and role is hard-coded, but I intend on allowing the user to specify both later on (hence why I'm not using NOW() and such).

So given the data below, say it's 4:30 PM on Monday and I want to know what fruits are on schedule, when this script is executed, it will echo out as follows:

Apple
Banana

However, when the code below is executed on the table below, it pretty much always selects the entire table. I am certain there is an issue with the query itself, but I included the rest just for sake of being thorough. I have tested selecting just the day and role itself and it works fine, but something in the way that it selects time evaluates to true erroneously. I'd appreciate any suggestions.

Here is an example of my table:

+----------+-------------+----------+----------+-------------+
| Name     | Days        | Start    | End      | Role        |
+----------+-------------+----------+----------+-------------+
| Apple    | Sun,Mon,Tue | 15:30:00 | 20:00:00 | Fruit       |
| Banana   | Mon,Tue,Wed | 16:00:00 | 20:30:00 | Fruit       |
| Cheese   | Mon,Tue,Wed | 08:30:00 | 10:30:00 | Dairy       |
| Carrot   | Fri,Sat,Sun | 22:00:00 | 07:00:00 | Vegetable   |
+----------+-------------+----------+----------+-------------+

Here is my code, nothing left out:

<?php
    date_default_timezone_set('Etc/GMT+6'); // Set default time zone
    $time = date("H:i:s"); // Time with seconds in a 24-hour, leading zero format: i.e. 07:00:00 or 15:30:00
    $today = date("D"); // Today, in a three-letter format: i.e. Mon, Tue
    $role = "Fruit";

    $username="example-username";  
    $password="example-password";
    $database="example-database";

    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT *
    FROM work 
    WHERE `days` LIKE '%$today%'  AND
    `role` LIKE '%$role%' AND 
    (start > end AND ('$time' < end OR '$time' > start)) OR
    ('$time' BETWEEN start AND end)";
    $result=mysql_query($query);

    $num=mysql_numrows($result);

    mysql_close();

    $i=0;
    while ($i < $num) {

    $victim=mysql_result($result,$i,"name");

    echo        "<li>$victim</li>";

    $i++;
    }
?>

Upvotes: 2

Views: 264

Answers (1)

Laurence
Laurence

Reputation: 10976

Your parentheses are wrong, your query has OR ('$time' BETWEEN start AND end) on the end, so a row that meets this criteria will ignore the role and days clauses. You want an extra pair around

((start > end AND ('$time' < end OR '$time' > start)) OR
('$time' BETWEEN start AND end))

As a general point, using comma separated values in a database is cutting against the grain of a relational database. Things usually work out better if you have separate rows for each.

Upvotes: 2

Related Questions