CODEWITHSUNDEEP
androidandroid-sqliteselect-query
Mahesh
Mahesh

Reputation: 1589

Sqlite Select Query give me wrong result in android?

i want select images and video from sqlite database here i implement my query but it gives me wrong result my current date is 2014-07-15 but it provide me the 2014-07-25 date result which is wrong.

My Query :

SELECT * 
FROM myfiles 
WHERE (( Date('2014-07-15') >= startdate 
AND Date('2014-07-15') <= enddate ) 
AND ( strftime('%H:%M:%S', starttime) >= '02:09:50' 
OR  '02:09:50'  <= strftime('%H:%M:%S', endtime )) 
AND ( strftime('%H:%M:%S',timer_from) >= '02:09:50' 
OR '02:09:50' <= strftime('%H:%M:%S',timer_to )) 
OR ( strftime('%H:%M:%S',timer_from) = '00:00:00' 
OR '00:00:00' = strftime('%H:%M:%S',timer_to ))) 
AND ( Tuesday = 1 OR Everyday = 1) 
AND download = 1 
AND playlist_id = 65 
AND user_id='265' 
ORDER BY position_id ASC, subposition_id ASC

-> link for download this image http://screencast.com/t/yS4SqUDqhe

Mysqlite Original database image

enter image description here

after executing query execute query image

enter image description here

Upvotes: 0

Views: 351

Answers (1)

A.Vynohradov
A.Vynohradov

Reputation: 276

Following part evaluates the whole check to true even if the date is invalid:

OR ( strftime('%H:%M:%S',timer_from) = '00:00:00' OR '00:00:00' = strftime('%H:%M:%S',timer_to ))

That is, there's no surprise that the result contains marked row (its timer_from = '00:00:00' and timer_to = '00:00:00').

Update
Try to change OR to AND in the aforementioned query part.

Update 2
Add following check to the end of your query before ORDER BY position_id ASC, subposition_id ASC and after AND user_id='265':

AND (Date('2014-07-25') > startdate)

Upvotes: 1

Related Questions