Topic: MYSQL DAYOFWEEK() DAYNAME()
hello,
Please i need help with correct syntax.
i have a customer table, with field fullname (string), monday, tuesday, wednesday, thursday, friday, saturday, sunday (all boolean). this day of week will be a checkbox on a form. i can check 1-3 items as the delivery schedule (ex. monday, thursday, sunday).
I want to display customers on a grid or combo box if they are check for the current day. (ex. today is Wednesday, if a customer wednesday field is true then this customer will be displayed in the list.)
Option 1, I try to use Search button and load it to Grid but I cant make query works, the idea is like this:
SELECT DayName(CURDATE()) AS myDay
if myday = 'Sunday' THEN
SELECT id, fullname FROM customer WHERE sunday = True
ELSEIF myday = 'Monday' THEN
SELECT id, fullname FROM customer WHERE Monday = True
ELSEIF myday = 'Tuesday' THEN
SELECT id, fullname FROM customer WHERE Tuesday = True
ELSEIF myday = 'Wednesday' THEN
SELECT id, fullname FROM customer WHERE Wednesday = True
ELSEIF myday = 'Thursday' THEN
SELECT id, fullname FROM customer WHERE Thursday = True
ELSEIF myday = 'Friday' THEN
SELECT id, fullname FROM customer WHERE Friday = True
ELSEIF myday = 'Saturday' THEN
SELECT id, fullname FROM customer WHERE Saturday = True
END IF
option 2, i try the scripting to add it in combo box. it could work but i have problem going to the next record?
if intDayWeek = 1 then SQLQuery('SELECT count(id) as iCount FROM customer WHERE sunday=True', Results)
else if intDayWeek = 2 then SQLQuery('SELECT count(id) as iCount,fullname FROM customer WHERE monday=True', Results)
else if intDayWeek = 3 then SQLQuery('SELECT count(id) as iCount,fullname FROM customer WHERE tuesday=True', Results)
else if intDayWeek = 4 then SQLQuery('SELECT count(id) as iCount,fullname FROM customer WHERE wednesday=True', Results)
else if intDayWeek = 5 then SQLQuery('SELECT count(id) as iCount,fullname FROM customer WHERE thursday=True', Results)
else if intDayWeek = 6 then SQLQuery('SELECT count(id) as iCount,fullname FROM customer WHERE friday=True', Results)
else if intDayWeek = 7 then SQLQuery('SELECT count(id) as iCount,fullname FROM customer WHERE saturday=True', Results)
else showmessage('wrong day');
rCnt := results.fieldbyname('iCount').asinteger;
for i := 0 to rCnt-1 do
begin
Form1.cbSchedule.dbAddRecord(i,Results.FieldByName('fullname').AsString);
results.next //????
end;
Results.Free;
I have tried to make this work my self but my limited skills dont allow me. Can someone please help me correct the syntax for the best approach of what I need to accomplished? Either option 1 or 2 will do.
Thanks a lot!