Today i have been asked to re-build a webpage that collects and displays statistics based on students grading each lesson. There are three grades. Each day on the schedule is comprised of four lessons, starting at 8.45 to 10:05, next one starting 10:20 to 11:40. The students select a grade on an ipad on the wall, and each grade is saved in db. The ipad shows a page with the three grades and load these based on which lesson it is:
Short:
select
from [table]
where curtime() > pass_tb.pass_start and curtime() < pass_tb.pass_stop
This works ok, then clock strikes 08:45, the corrent class shows up on the ipad and the students can grade the lesson. When lesson ends, students cannot grade that lesson.
Problem is, i need to rebuild a few things since the school needs to be able to change start and stop time on each lesson. The DB is not build correctly but the solution COULD be to change the SELECT statement above using IFs in the where-clause if applicable? Ie selecting where on another column instead based on a value?
Like this?
IF klassrumsstat_tb.av_tid = 0
then WHERE curtime() > pass_tb.pass_start and curtime() < pass_tb.pass_stop
ELSE if klassrumsstat_tb.avtid = 1
then WHERE curtime() > klassrumsstat_tb.av_tid_start and curtime() < klassrumsstat_tb.av_tid_stop
The entire SELECT:
select klassrumsstat_tb.*,
pass_tb.pass_namn,
pass_tb.pass_start,
pass_tb.pass_stop,
klasser_tb.klassnamn,
personer_tb.förnamn,
personer_tb.efternamn,
klassrum_tb.rumsnamn,
ämnen_tb.ämne
from klassrumsstat_tb
inner join pass_tb on pass_tb.pasid = klassrumsstat_tb.pasid
inner join klasser_tb on klasser_tb.klassid = klassrumsstat_tb.klass
inner join personer_tb on personer_tb.uid = klassrumsstat_tb.uid
inner join klassrum_tb on klassrum_tb.klaid = klassrumsstat_tb.klaid
inner join ämnen_tb on ämnen_tb.aid = klassrumsstat_tb.aid
where klassrumsstat_tb.klaid = 1
and dag = 'Mon'
and curtime() > pass_tb.pass_start
and curtime() < pass_tb.pass_stop;
The klassrumsstat_tb contains a column (klassrumsstat_tb.av_tid) that is 0 if time does not differ and 1 if times differ. klassrumsstat_tb also contains two columns (av_tid_start and av_tid_stop) which is the new times the lesson should start.
English is not my native language, i hope you understand.
I have searched and googled on how to use IF-statements in the where-clause, but i cannot seem to find a solution to get my desired results. Is it even possible? I dont want to rewrite the entire product.
case expression may be what you're after, something like this
CASE WHEN klassrumsstat_tb.av_tid = 0
then curtime() > pass_tb.pass_start and curtime() < pass_tb.pass_stop
WHEN klassrumsstat_tb.avtid = 1
then curtime() > klassrumsstat_tb.av_tid_start and curtime() < klassrumsstat_tb.av_tid_stop
END