Web Tutorial: The Self-affirmations WordPress Plugin (Part 1/4)

Last September, I walked you through setting up a Mailing List Registration Form in Oracle APEX. This month is a follow up. We are going to use the database created then, to drive what we're making today. So if you didn't pay attention the last time, tough luck, chum! We'll be continuing on our work from the last round.

We want to set up a script in WordPress to send out those emails on a scheduled basis, to the users in the database. For that, first of all, we have to set up REST endpoints for the database in Oracle APEX.
Log in to your workspace and go straight to SQL Workshop. There should be a range of big buttons. We want the one that says RESTful Services.

Over here you should see this screen, with no Modules yet. Click the Create Module button.

This should come up. Make up a name for the Module, and an endpoint name. Click Save.

Now you should see this. Click on the Module name.

Now you will see the Module. You'll want to create Templates. The button is on the lower right, in a nice aqua color.

Here we add the template. For this, we use the name "readytoreceive". This is where we get a list of all users that have not received an email in the last n number of days, determined by the value of DAYS.

Now it's there, but this is just a Template. We'll need to create a Handler for it.

In here, set the Method to "GET". Then click Create Handler.

Now you'll see this Handler for "GET" has been created. Click on it...

...and you're in this interface. Set Source Type to "Collection Query". And input the SQL below.

This is the SQL. We basically say that if the user's LAST_SENT value is less than the value of today's date minus DAYS minus 1, it's added to the list. Why minus 1? Well, DAYS could be 1, and if user's LAST_SENT value was yesterday, it would not get entered into the list. Thus, we do a minus 1 to take care of that case.

SELECT EMAIL, FIRST_NAME, LAST_NAME, DOB, GENDER FROM MAILING_LIST WHERE LAST_SENT < TO_DATE(CURRENT_DATE - (DAYS - 1))

So that's your first endpoint!

The next endpoint is called "terms". We set it up the same way, first creating a Template. Unlike the last time, we have ":email" in the URI Template setting. This is the parameter which we will use later.

Then we create a Handler for it by clicking Create Handler. For this, everything is the same as previously, except for the SQL.

The SQL is as follows. We want to get everything from the MAILING_LIST_TERMS table where the EMAIL field matches the value of email.

SELECT TYPE, TERM FROM MAILING_LIST_TERMS WHERE EMAIL = :email

We need to define the parameter, email, for the query. In the Parameters section right at the bottom, click the Add Row button and fill in the details as shown. This defines the parameter for the query as a string.

Done with the second endpoint!

The third one also uses a parameter, but it will update the database. Create a Template for it, like this.

Then create a Handler. This time, the Source Type is "PL/SQL".

This is the SQL. It sets the value of LAST_SENT for the MAILING_LIST table where the EMAIL value matches email, to today's date.

UPDATE MAILING_LIST SET LAST_SENT = CURRENT_DATE  WHERE EMAIL = :email

Of course, we will need to set up the parameter, email.

Time to test!

In your browser, test this URL for readytoreceive. You should get this.

Now test terms using an email address in the database. You should have this list, assuming you've entered data for it.

Test setreceived with the email. There's no visual indication, but if you check the database, the LAST_SENT column for that email should be today's date. Now if you test setreceived again, this email should not appear in the list!

In this part of the web tutorial, we set up REST endpoints to get and set data. Subsequently, we will use these endpoints for the script.

Next

The WordPress plugin, setting up testing.