Issues with conditions

[monsieurmerle]

Hi everyone,
I have begun using SQLPage for an internship project, and as part of the functionalities, I would like to be able to display alerts when some conditions are fulfilled, but I can't make it work. (As an aside, I'm no programmer and I have a little experience with python and even less with SQL.)

• I first need to compare two dates to display a message if the maintenance date is overdue. I would have liked to use DATEDIFF with WHERE and have it display when the DATEDIFF is under a given amount of days. The function DATEDIFF doesn't seem to work with SQLPage however, is there an alternative ?

• I am trying to get the user to fill out a professional email address, and display an alert in the user homepage until done. I am using :

  WHERE NOT (SELECT mail_pro FROM clients_company WHERE id = (SELECT user_ide FROM sessions WHERE session_token = sqlpage.cookie('session_token')) IS NOT NULL)

It either never displays the message or it always does when I try to play with the NOT for the WHERE and the NULL conditions (the session token part is copied from Nick Antonaccio's tutorial and works the other times it is used). The email addresses are supposed to be NULL by default unless they are filled by the administrator when creating the users.

Thank you in advance to anyone who can help.

[monsieurmerle]

Hi @monsieurmerle !

In SQLPage, you can use the functions provided by the database you are connected to . Which database are you using ? By default, when you don't configure a database, SQLPage uses SQLite. The list of date and time functions supported by SQLite is documented here: https://sqlite.org/lang_datefunc.html . Similar functions exist in Postgresql and in MySQL.

In sqlite you could have a conditional alert like

select 'alert' as component,
    'Hurry up' as title,
    'Maintenance is due in less than two days !' as description,
    'red' as color
from maintenance
where
  id = $id AND
  datetime(due_date, '+0000-00-02') < current_date;

[monsieurmerle]

Thank you for the answer, I will try this !

EDIT: I have tried it and it seems to work, I have had to substract instead of add time using the datetime() function, otherwise it works.

[lovasoa]

For your second question about finding whether the current user has a professional email, what you want is probably

select 'alert' as component, 'Missing email' as title
WHERE NOT EXISTS ( -- show the alert if no saved session has an associated user with an email
  SELECT 1 FROM sessions s
  JOIN clients_company c ON c.id = s.user_ide
  WHERE s.session_token = sqlpage.cookie('session_token') AND
        c.mail_pro IS NOT NULL
);

Be careful and ensure that your mail_pro column really contains null, and not an empty string for instance, when the user has not filled in their email.

[monsieurmerle]

I am afraid it might be an issue of empty string, I will have to inverstigate. Will come back when I know more if I am unable to fix it.
Thanks !