|
|
|
|
In this tutorial topic you will enhance the Classified Ads application by allowing users subscribe to receive email notifications when certain combinations of ad type, category and location are met. This enhancement contains two pages:
view_subscription.php - where a list with the existing subscriptions is shown. Links to add, edit or delete a subscription are displayed on the page.
subscription.php - where a form allows adding or editing subscriptions.
Create these two files in the site's root folder. To implement this enhancement, you will use the NeXTensio list and form wizard.
First off, let's create the subscription list page:
Open the view_subscription.php file in Dreamweaver. To ensure that unregistered users cannot nose into other's subscriptions, make sure the Restrict Access To Page server behavior was applied, as shown here.
Next start the NeXtensio
List Wizard, to display the subscriptions. Configure each step
as shown below:
· In the
first step, simply select the database connection created at the beginning
of this tutorial, connAds, and the table
to list: subscribe_sub. The primary key field
is auto-detected, and can be left at its default. Also, set the redirect
page to the subscription file
· In the
second step you must define which table columns will be displayed and
how. By default, all fields except the Primary
Key are displayed in the List Fields grid. You should remove the
idusr_sub field too, as the only user that
would appear is the currently logged in one.
· For the
idtyp_sub, idcat_sub
and idloc_sub fields, use a look-up table
to retrieve data. For each field select its corresponding table, and the
appropriate label and value columns from the appropriate drop-down menus
(e.g. for the idtyp_sub use the type_typ table, with the name_typ column
for label, and the primary key of the table - id_typ - for the value):
·
Step 3 of the wizard allows setting up the form elements used in the list
filter. Since each field is based on a look-up table, the corresponding
filter element will be a menu which retrieves its data form the tables
configured above.
·
In the wizard's last step, configure the list options: the page containing
the add/edit form (subscription.php), the number
of records, and the table column to sort the list after:
· Now the
HTML elements, as well as the NeXTensio recordset and server behaviors
are added to the page. The only problem of this list is the fact that,
at this point, it displays the subscriptions for all users. To restrict
it to the current user only, you have to modify the recordset, and add
a condition.
Note: If using ColdFusion, you can skip to step 3.
· To do so,
double-click the list recordset in the Bindings tab. Add a new variable,
called user, having the runtime value equal to $_SESSION['kt_login_id'].
To add a variable, click the Plus (+) button
on top of the
Variables grid, and set its properties in
the dialog box that opens:
· Now edit
the SQL, to add the condition of equality between the able column storing
the user id, and the newly defined variable. The new code to enter in
the SQL text area is:
SELECT type_typ.name_typ AS idtyp_sub, category_cat.name_cat AS idcat_sub,
location_loc.name_loc AS idloc_sub, subscribe_sub.id_sub
FROM subscribe_sub LEFT JOIN type_typ ON subscribe_sub.idtyp_sub =
type_typ.id_typ LEFT JOIN category_cat ON subscribe_sub.idcat_sub = category_cat.id_cat
LEFT JOIN location_loc ON subscribe_sub.idloc_sub = location_loc.id_loc
WHERE NXTFilter AND subscribe_sub.idusr_sub =user
ORDER BY NXTSort
If using ColdFusion, you do not need to add the
page parameter. Simply add a new condition to the query, which will check
the idusr_sub column against the kt_login_id
session variable. Add the following condition after the existing WHERE clause, and before the ORDER
BY clause:
AND idusr_sub = #SESSION.kt_login_id#
The query should look like the following: (page
parameter names may vary, based on your particular setup):
SELECT type_typ.name_typ AS idtyp_sub, category_cat.name_cat AS idcat_sub,
location_loc.name_loc AS idloc_sub, subscribe_sub.id_sub
FROM ((subscribe_sub LEFT JOIN type_typ ON subscribe_sub.idtyp_sub
= type_typ.id_typ) LEFT JOIN category_cat ON subscribe_sub.idcat_sub =
category_cat.id_cat) LEFT JOIN location_loc ON subscribe_sub.idloc_sub
= location_loc.id_loc
WHERE #PreserveSingleQuotes(SESSION.filter_tfi_listsubscribe_sub1)#
AND idusr_sub = #SESSION.kt_login_id#
ORDER BY #SESSION.sorter_tso_listsubscribe_sub1#
Simply replace the existing SQL code with this one, and the recordset will filter out any other users than the one that is currently visiting the page.
Now all operations on this page are done, and you can save and upload it to the server, and move on to the form.
To create the form, you must open the subscription.php file in Dreamweaver. The page must have the same name as the one specified in the list wizard last step, or the links will not work correctly. This page will contain a NeXTensio form, which will be added and configured through the NeXTensio Form Wizard.
Open the wizard from the MX
Kollection tab of the Insert Panel,
and configure each step as shown below:
· In the
first step select the same connection and table as for the List
wizard. Since you've applied the List Wizard first, the UI
persistence should have completed all fields.
· In the
second step, all fields are correctly configured by the persistence. If
not in the grid, you will have to add the idusr_sub
field. Set it as text with the default value of the session variable kt_login_id:
· The third
step is available only if you have MX Form Validation
installed,
and provides a way to define validation rules for all fields involved
in the form. Since all fields are menus, you do not need validation and
can just click the Finish button to add
all elements into the page.
The NeXTensio form elements, recordset and server behaviors are now inserted into the page. Delete the first row from the table, the one containing the idusr_sub text. All that remains is to save the page and upload it to the server. This way, new users can define subscriptions for different types of ads easily.
The last part of the tutorial is an improvement to be made to the ad posting page. The subscription list is created and managed by the users, but you have to implement the actual sending of the emails.
For this enhancement, you will use MX Send E-mail, on the post_ad.php page. To modify the page, follow the next steps:
Open the post_ad.php page in Dreamweaver. On the page there is already an Insert Transaction , that allows posting new ads.
To send the email to all users that are subscribed to the type, category and location of the ad that's being posted, you must add two things: a recordset to retrieve their email addresses, and a Send email trigger.
First the recordset: it must be filtered, since the user's preferred ad type, category and location must match the ones entered in the post ad form. Create a new recordset from the Bindings panel, and when the dialog box opens, switch to the advanced view.
In the Advanced view, select the database connection created at the beginning of the tutorial.
Create three new variables, by clicking the Plus (+) button on top of the variables grid.
Configure them as follows:
· Name: type; Default value: -1; Runtime value: $_POST['idtyp_ads'].
· Name: category; Default value: -1; Runtime value: $_POST['idcat_ads'].
· Name: location; Default value: -1; Runtime value: $_POST['idloc_ads'].
Note: For ColdFusion
you have to add three page parameters, configured as follows:
· Name: #FORM.idtyp_ads# Default value:-1
· Name:#FORM.idcat_ads# Default
value:-1
· Name: #FORM.idloc_ads# Default value:-1
When the form is submitted, these variables will
retrieve the values of their corresponding form elements, and will be
used to compare against the fields in the subscribe_sub
table. The comparison is done by the SQL query you must enter in the SQL text area: (for the PHP
and ASP VBScript server model)
SELECT *
FROM subscribe_sub LEFT JOIN user_usr ON subscribe_sub.idusr_sub=user_usr.id_usr
WHERE idtyp_sub=type AND idcat_sub=category AND idloc_sub=location
Note: If using ColdFusion,
the query is different:
SELECT *
FROM subscribe_sub LEFT JOIN user_usr ON subscribe_sub.idusr_sub=user_usr.id_usr
WHERE idtyp_sub=#FORM.idtyp_ads# AND idcat_sub=#FORM.idcat_ads# AND
idloc_sub=#FORM.idloc_ads#
As you may notice, aside from comparing the type, category and location, this SQL code performs a JOIN operation between the subscription table and the user table, in order to retrieve the e-mail address for each one. The recordset creation is now complete, and you can close the dialog box by clicking the OK button.
Now for the actual sending of the mail messages you will use a trigger provided by MX Send E-mail: Send E-mail to recipients from recordset. This trigger will send a mail message to all mail addresses retrieved by a recordset, in our case the rsMailList one.
Before applying the server behavior, you should check out the mail server settings in Control Panel, to see if they are correct in your case. To learn more about the mail server settings, click here.
Apply the Send E-mail
to recipients from recordset server behavior from the Server
Behaviors tab -> MX Kollection -> Send E-mail. From the dialog
box that opens, you only need to configure the options on the first tab.
Set them as follows:
· In the
Recordset drop-down menu select rsMailList.
· In the
Email to field drop-down menu select the
recordset field containing the user's mail address.
· In the
From text box you can enter an email address
specific for your site, or leave it at default, to use the control panel
defined sender.
· In the
Subject text-box enter the mail's subject:
New ad posted.
· For the
mail's content, you have two choices: to use a predefined template, or
to write the content in the text area below. Select the latter version,
as there is no need for a special template.
· In the
Content text area enter the mail text. Usually,
a notice that a new ad that matches what the user subscribed for has been
added, and a link to the site is enough.
· The completed
dialog box should look like the image below:
· Once all
dialog box required options are completed, simply click the OK
button to add the server behavior into the page.
Note: If using the ASP VBScript server model you must configure the E-mail server settings in the Control Panel. You must fill in the server address (or name), the port (by default it is 25), user name and password. Optionally, you can also set the default sender field.
Save the page and upload it to the server. When a user will post an ad, all those that are subscribed to the same ad type, category and location will receive an email message warning them of the new post.