MySQL Query: Query with conditional statements

Question

i have this query

SELECT 
IF(isnull(ub.user_lecture_id), 0, ub.user_lecture_id) as  IsPurchased,
cs.title,cs.start_date, cs.start_time, cs.end_time 
FROM campus_schedule cs 
LEFT JOIN campus_bookinfo cb ON cs.bookid=cb.idx_campus_bookinfo 
LEFT JOIN user_lectures ub ON ub.id_product = cs.idx_campus_schedule AND ub.id_customer = 11 
WHERE cs.idx_campus = 1 and cs.title like '%%' and cs.status=1

Which Shows: Click to view Output

Explanation: if (IsPurchased == 0) it is not yet bought my customer

My Question: if you look at the time of row with IsPurchased=1, the time range is conflicting with the time in IsPurchases=0. how can i compare and conclude that the time of the same date of the query is conflicting to the time and date of the other rows. results may be 1 or 0 in a "conflict" field name

Hope you got the point. Thanks for the help!!!

Answer 1

To compare times, you will find it easier to use DATETIME fields.

To check for "conflicting" rows, you'll probably need to have a subquery in the WHERE clause.

Answer 2

Subquery should work but will be inefficient in mysql. You should create temporary table and analyze it. Or do the same inline, like:

set @lastdate=0;
set @lasttime=0;

select IsPurchased, title, start_date, start_time, end_time, if(@lastdate = start_date, @lasttime < end_time, 1) as CONFLICT, @lastdate:=start_date, @lasttime:=start_time
from (your_query ORDER BY start_date, start_time, end_time) t ;

that is just an idea, it worked for me several times.

Answer 3

I just deal the trapping with PHP.. but if there's an answer to this that may optimized and simplify all things.. then im willing to wait and accept your answer