CODEWITHSUNDEEP
phpmysqlsql
coder
coder

Reputation: 299

MySQL / PHP: Select Where with multiple conditions does not work

I've got a following database entry:

id                date                start_time 
1                 2015-12-25          08:00:00
2                 2015-12-30          08:00:00
3                 2015-12-30          09:00:00

Now I just want to select the date of those entries where both start_time entries 08:00:00 and 09:00:00 exists.

I tried to use this SQL query:

$sqlquery = mysqli_query($myconnection,"SELECT date 
                                        FROM mytable 
                                        WHERE start_time LIKE '08:00:00' 
                                        AND '09:00:00'") or die ("crashed");

But it returns me both dates 2015-12-25 and 2015-12-30. It should only return 2015-12-30 because for this date 08:00:00 and 09:00:00 exists.

I want to select those dates which have an entry for 08:00:00 and 09:00:00 too.

It should not select dates with only an entry for 08:00:00 but none for 09:00:00 and also not those which have an entry for 09:00:00 but none for 08:00:00.

Upvotes: 0

Views: 183

Answers (3)

David
David

Reputation: 532

i assume that you basically want to select date that has both '08:00:00' and '09:00:00', then you should not use 'BETWEEN'. try this query:

SELECT t1.date
FROM mytable AS t1
INNER JOIN mytable AS t2 ON t1.date = t2.date
INNER JOIN mytable AS t3 ON t1.date = t3.date
INNER JOIN mytable AS t4 ON t1.date = t4.date
WHERE t1.start_time = '08:00:00' 
    AND t2.start_time = '09:00:00'
    AND t3.start_time = '10:00:00'
    AND t4.start_time = '11:00:00'
GROUP BY t1.date

or you can try another approach

SELECT t1.date
FROM mytable AS t1
GROUP BY t1.date
HAVING SUM(IF(t1.start_time = '08:00:00', 1, 0)) > 0
AND SUM(IF(t1.start_time = '09:00:00', 1, 0)) > 0
AND SUM(IF(t1.start_time = '10:00:00', 1, 0)) > 0
AND SUM(IF(t1.start_time = '11:00:00', 1, 0)) > 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Don't use like for date/time columns. Here, you seem to want between:

SELECT date
FROM mytable
WHERE start_time BETWEEN '08:00:00' AND '09:00:00';

Your original formulation is parsed like this:

WHERE (start_time LIKE '08:00:00') AND '09:00:00'

The second part is a string value in a boolean/integer context. It gets converted to 9, which is always true. So, the where clause ends up being equivalent to:

WHERE start_time = '08:00:00'

EDIT:

Your clarification changes my understanding of the question. If you want days that have both times, use aggregation:

SELECT date 
FROM mytable 
WHERE start_time IN ('08:00:00', '09:00:00')
GROUP BY date
HAVING COUNT(*) = 2;

Upvotes: 2

davejal
davejal

Reputation: 6143

As mentioned in the comments there are different ways to achieve it depending on what you actually want to do with the result.

  1. Easy->Just count the records with specific dates

    select date, count(start_time) 
from mytable 
group by date 
having count(start_time) > 1

2.Advanced->Display the records by using a case

   select * 
   from (
          Select date,
                case when start_time = '08:00:00' then 1 end as startat8, 
                case when start_time = '09:00:00' then 1 end as startat9 
           from mytable
         ) a 
    where a.startat8=1 and a.startat9=1;

Upvotes: 0

Related Questions