SELECT *
FROM afspraken
WHERE user_id = '2'
AND datum > '2012-06-05'
OR (
user_id = '2'
AND datum = '2012-06-05'
AND begintijd >= '22:47'
)
ORDER BY datum DESC , begintijd ASC
This above is my query.. And while i should get zero result i still get one which is:
id user_id datum begintijd opmerking
114 2 2012-06-05 9:30 Deze afspraak is online gemaakt.
Now if i have this correctly, this query has 2 options to give results.. which is: The user_id must be the same AND the date has to be higher then 2012-06-05.. Since its the same date this won't give the result..
Now the second option which is when the user_id is the same, the date is the same and when the beginningtime is the same or higher.. Now the time in the query shown is 22:47.. The time in the database is 9:30 morning.. How is 9:30 morning higher or equal to 22:47??
id int(10)
user_id int(10)
datum date
begintijd varchar(5)
opmerking varchar(8000)
reminder int(10)
The idea would be to cast your time columns and values to Time:
SELECT CAST('20:05' AS Time) > CAST('9:06' AS Time) #returns 1 (compares times - what you want)
SELECT '20:05' > '9:06' #returns 0 (compares strings - not what you want)
Read more here.
It looks like your begintijd column is a varchar column.
So, as strings, '22:47' < '9:30'.
A solution would be to convert your begintijd column to a time datatype.
If you are able to change your database layout why not use DATETIME like this:
id int(10)
user_id int(10)
afspraak_dt datetime
opmerking varchar(8000)
reminder int(10)
Your data:
id user_id afspraak_dt opmerking
114 2 2012-06-05 9:30 Deze afspraak is online gemaakt.
Examples selects:
SELECT * FORM afspraken WHERE user_id = 2 AND afspraak_dt > '2012-06-05 22:47'
Select all afspraken on that day for user id 2
SELECT * FORM afspraken WHERE user_id = 2 AND DATE(afspraak_dt) = '2012-06-05'
Select whith unix timestamp (select all afspraken in the future for user 2)
SELECT * FROM afspraken WHERE user_id = 2 AND UNIX_TIMESTAMP(afspraak_dt) > NOW()
The MySQL datetime has numerous select options for handling date and time. See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html