1 (edited by rhyacm 2019-03-20 21:07:39)

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!

Re: MYSQL DAYOFWEEK() DAYNAME()

Hi,
Quite a few different ways to approach this, I imagine.
The easiest way of all would simply be to use a tablegrid and have filters on each of the days - click the appropriate filter for whatever day of the week and the rows in the tablegrid adjust accordingly.  And you shouldn't need to use a script with this approach.
But if you want to see a delivery schedule for discrete days in a separate tablegrid, then I think I'd try it this way (see attached);  I've tried to keep the script to a minimum.
As per your write-up, when you run the program, it defaults to the current day of the week and customers who have deliveries for that day.
But I imagine someone is always going to want to know about tomorrow etc etc (LOL!), so just enter a different day number (Monday = 1 - Sunday = 7) and you can see different schedules for different days.  And if you enter a space, it shows ALL schedules.  Deleting the number (or the space), clears the tablegrid.
Perhaps this gives you some ideas.
Derek.

Post's attachments

Attachment icon deliveryschedule.zip 340.33 kb, 114 downloads since 2019-03-20 

Re: MYSQL DAYOFWEEK() DAYNAME()

derek wrote:

Hi,
Quite a few different ways to approach this, I imagine.
The easiest way of all would simply be to use a tablegrid and have filters on each of the days - click the appropriate filter for whatever day of the week and the rows in the tablegrid adjust accordingly.  And you shouldn't need to use a script with this approach.
But if you want to see a delivery schedule for discrete days in a separate tablegrid, then I think I'd try it this way (see attached);  I've tried to keep the script to a minimum.
As per your write-up, when you run the program, it defaults to the current day of the week and customers who have deliveries for that day.
But I imagine someone is always going to want to know about tomorrow etc etc (LOL!), so just enter a different day number (Monday = 1 - Sunday = 7) and you can see different schedules for different days.  And if you enter a space, it shows ALL schedules.  Deleting the number (or the space), clears the tablegrid.
Perhaps this gives you some ideas.
Derek.


This is a clever solution derek! I will apply this now but I feel like my loading time is getting longer and heavy the more objects I add. I already have tons of images because I use it as buttons (like hovering effects switching images). Still I would love to learn how to make my sample mySQL statement or script to work, this will also help me solve future problem. Thanks a lot as always for the sample I will use this so I can run my system sooner. smile

4 (edited by derek 2019-03-21 11:35:36)

Re: MYSQL DAYOFWEEK() DAYNAME()

Hi,
I take your point about loading times.
Attached is a slightly different approach you could try (I've replaced the multiple tablegrid approach in my earlier suggestion with a single tablegrid and a script that filters/hides columns as required.  Probably it's a more efficient way of doing it.
Just enter 'MON', 'TUE', 'WED' etc to get your daily delivery schedules or 'blank' to see everything.
Regards,
Derek.

Post's attachments

Attachment icon schedule by script.zip 339.46 kb, 132 downloads since 2019-03-21 

Re: MYSQL DAYOFWEEK() DAYNAME()

derek wrote:

Hi,
I take your point about loading times.
Attached is a slightly different approach you could try (I've replaced the multiple tablegrid approach in my earlier suggestion with a single tablegrid and a script that filters/hides columns as required.  Probably it's a more efficient way of doing it.
Just enter 'MON', 'TUE', 'WED' etc to get your daily delivery schedules or 'blank' to see everything.
Regards,
Derek.


Thanks a lot derek! this is much better smile cheers!

Re: MYSQL DAYOFWEEK() DAYNAME()

Just a final thought - if you wanted to see which customers have weekend delivery schedules (ie Saturday or Sunday), you could add something like this to the script:
  if form1.edit1.text = 'W/E' then
    begin
      form1.tablegrid1.dbFilter := '(sat = "1") or (sun = "1")';
      derekhidecols;
      form1.tablegrid1.columns[6].visible := true;
      form1.tablegrid1.columns[7].visible := true;
    end;
Derek.

7 (edited by Asifmute 2019-08-08 22:33:59)

Re: MYSQL DAYOFWEEK() DAYNAME()

KING DEREK you are awesome.

JUST LEARNING, O GOD HELP ME.