| |
| |
In this section of the tutorial, you will enhance the application by allowing users to subscribe to receive email messages when an ad that fits some entered criteria is posted. The subscribe_sub table will be used for the purpose of this improvement, as it will store the user's preferences for the ads they will receive by e-mail.
To create this enhancements, you must take two steps:
Create a page where users can subscribe to a certain type of ads, view their existing subscriptions and alter them.
Modify the ad posting page, so that it will send an e-mail to all users that subscribed to ads that have the same characteristics as the one entered.
The user's subscription page will be placed inside the user folder, and it will be available through a link on the user's home page. To create this page, follow the next steps:
Create the new files that will be used by users to manage their subscriptions in the user folder: view_subscription.php and subscribe.php.
Open the view_subscription.php page in Dreamweaver.
To allow only registered users to take advantage of the subscription improvement, apply a Restrict Access To Page Server Behavior A server behavior is a reusable component for server-side development. They add blocks of code to your pages for accomplishing specific tasks. Dreamweaver comes with several default server behaviors and the InterAKT extensions add many more to this list., which will act based on username, password and level. Configure it as shown in the User Authentication Tutorial.
To build the subscription list together with all links and functionality, you will use the NeXTensio List. Open the Create NeXTensio List Wizard from the MX Kollection tab of the Insert bar.
In the wizard's first step you have to set the database A database refers to data organized and stored on a computer that can be searched and retrieved by a computer program. Most industrial-strength and many smaller database applications can be addressed using SQL (Structured Query Language). details: the connection that you've created at the beginning of this tutorial (connJobs), the table to use for the list (subscribe_sub) and the table's primary key A primary key is one or more table columns whose values uniquely identify each record in a table. In general, a primary key is defined on a single column, but it is not uncommon to have it defined on two columns..
You also need to decide on the page that will contain the NeXTensio form. The form will be accessed when trying to add, edit or delete entries. Use subscribe.php.
At this step you can also specify how many entries will be displayed on a page. Enter a value in the Number of records text field.
Once you've set these details, move on to the more important step two, where you have to set up the fields to use.
In the second step, you will have to determine which table columns will be displayed in the list
The grid displays all available columns that will be shown in the page. You can add or remove columns by using the Plus (+) and Minus (-) buttons on top of the grid. Remove the idusr_sub field from the grid. Since the list will display subscriptions for the currently logged in user, the ID would always be the same, and therefore useless.
Next, you have to configure the other fields. To do so, select each one, and change their properties through the elements below the grid. Configure them as follows:
· For the iddom_sub field, set the label to Domain, and select the Look-up table as method to retrieve the data to display. In the table drop-down menu select the domain_dom table. Select the corresponding table columns for the label and value (name_dom for label and id_dom for value).
· For the location_sub field, use the same approach as for the domain, but use the location_loc table.
· For the minsal_sub field, you just have to set header to something more reasonable, like Minimum salary. To change this property, select the minsal_sub column in the grid, and enter the text in the Header text field displayed below.
When you have finished setting up the fields, press the Next button to move on to the wizard's third step.
In the third step of the wizard, you must set options for the filter elements. This will control what elements and with which default values will be used in the filter. For all of the list fields that use a look-up table, the filter element is automatically set to Menu. The menu contents are dynamic, using automatically generated recordsets on the same tables as selected in step two (2). You only need to setup the filter for the type_sub field. Since there are only three possible job types, set the Display as to menu, and configure its static options by pressing the Menu properties button:
In the last step, you must configure some of the list's properties:
· What table column to order the list by, whether to duplicate navigation and buttons, or the skin. Configure the dialog box as shown in the next image:
Click the Finish button to close the dialog box and add all elements into the page.
In Dreamweaver, you will notice the HTML elements added by the wizard, as well as the server behaviors, and recordset you've created for the fields. There is one more thing you need to do in order for the list to display subscriptions for the current user only: restricting the list's recordset on the currently logged in user's ID.
To accomplish this, follow the next steps:
Double-click the NeXTensio List recordset (usually called ListRecordSet in the Bindings tab. Add a new variable, called user, having the runtime value equal to $_SESSION['kt_login_id']. To add a parameter, click the Plus (+) button on top of the Variables grid, and set its properties in the dialog box that opens:
Note: If using ColdFusion, you must add a Page parameter. Configure it as shown in the image below:
Change the SQL query A query is a SQL command that will extract information from the tables of a database. Essentially, a query is a request for information from your database., so that it will take the new variable into account:
SELECT subscribe_sub.type_sub, iddom_sub AS old_iddom_sub, domain_dom.name_dom AS iddom_sub, location_sub AS old_location_sub, location_loc.name_loc AS location_sub, subscribe_sub.minsal_sub, subscribe_sub.id_sub
FROM subscribe_sub LEFT JOIN domain_dom ON subscribe_sub.iddom_sub = domain_dom.id_dom LEFT JOIN location_loc ON subscribe_sub.location_sub = location_loc.name_loc
WHERE filter
AND idusr_sub=user
ORDER BY sort
Click the OK button to close the recordset dialog box, save and upload the page to the server. You can test it by logging in as a user, and clicking the View subscription link on the home page. It will open the list, showing the subscriptions the respective user has made.
Create the page that allows users add and edit subscriptions. This page has been referred to already, when you specified it in the NeXTensio List Wizard last step. It will contain a form, allowing users decide the criteria for their subscription.
Open the subscribe.php page in Dreamweaver.
To create the form, you will use the NeXTensio Form Wizard. Because you've already created a list in this site, the user interface persistence will take effect and you will notice that the wizard's options are already filled in.
In the first step, the connJobs database connection, as well as the subscribe_sub table are already completed. Leave them as they are, because the values are correct, and move on to the second step: the field configuration.
The user interface persistence is working in this step too. You can notice that all the list fields are here, configured as menus, with the right recordsets (where applicable) or values. However, one more piece of information is required when entering data into the tables: the user ID.
To add a field to the form fields grid, simply click the Plus (+) button on top of it, and select the desired field from the list of available ones. Select the idusr_sub field.
Once it appeared in the grid, you have to configure it to be displayed as a hidden field, and to retrieve its value from the session A session is a way to preserve certain data across subsequent accesses of the same web application. The session object contains many variables to store user information on the web server in a location that you choose in special files. The session support allows you to register arbitrary numbers of variables to be preserved across requests. Sessions are specific to individual users. As a result, every user has a separate session and has access to a separate set of Session variables. variable storing the current user id. To do so, click on it in the grid, and in the Display as drop-down menu select Text. Also, in the Default Value text-field you have to have the session variable. Click the InterAKT Dynamic Data icon next to the field, and select the kt_login_id variable.
With all fields configured, you can safely click the Finish button.
If you have MX Form Validation installed, another step will be available in the wizard, where you can define validation rules for any of the fields in the form. You can skip this step, as most of the form elements are menus, where users have to select from predefined values.
Delete the first row in the form, the one containing the user ID element as text.
Save the page and upload it to the server. To test it out, log in with a regular user account and click the View subscriptions link. In the list, press the Add new button, as this will open the subscribe.php file you've just created. Fill in the form, and hit OK to add the subscription to your list:
The second part of this tutorial section, requires some modification to the job opportunity posting page. To be precise, it requires adding a Send e-mail to recipients from recordset server behavior, which will send a message regarding the job offer that is being submitted to all users that match the type, domain, location and minimum salary.
To create this page follow the next steps:
Open the post_job.php page from the company folder in Dreamweaver. On the page there is already an Insert Transaction A transaction is a group of SQL statements whose effects are logically connected. Anything from simple queries to inserting, and deleting operations can be considered a transaction, as well as more complex groups of several statements which accomplish a specific task., that allows posting new job offers.
To send the email to all users that are subscribed to the type, domain and location of the job 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 job type, domain, location and minimum salary 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 QuB3 view by clicking the QuB3 button on the right.
In the QuB3 view, select the database connection created at the beginning of the tutorial. Then name the query rsMail and click the New Query button on the bottom.
Type rsMail again in the name prompt and click OK. The MX Query Builder interface should then open. If this is your first time using MX Query Builder, you may want to take a quick look at the Workspace orientation page to understand the interface a bit better.
From the Tables panel on the left, select the two tables you will need for this query: subscribe_sub, and user_usr
The two tables will now appear in the Database Diagram. Here you will create a relation between the two tables using the foreign key field. Just drag and drop the idusr_sub field to the id_usr field as shown below:
After adding the relation, a blue line with an arrow will connect the two fields. Using the checkboxes from each table, select various fields as shown below. When done, the Database Diagram should look as follows:
By checking the boxes next to each field in the subscribe_sub table, each selected field is displayed below in the Query Management panel. Here is where the conditions are added so that the data retrieved matches the data requested from the form.
To add conditions, click the button under the Condition column of the Query Management panel:
Starting with the type_sub field, click the Condition button in the same row. An Edit SQL Condition window will appear. Configure it as shown below:
Repeat step 11 for iddom_sub, location_sub, and minsal_sub. For the Run-time Value field, make sure to use the appropriate variable from the previously created form (domain_job, location_job, salary_job).
Note: For the minsal_sub field to work properly, use "<=" for the condition instead of just "=".
Once all the conditions are added, you can save the query by going to the Query menu and clicking Save:
Now close MX Query Builder and go back into Dreamweaver.
The QuB3 Visual Recordset window should still be open. Click the Refresh button:
The rsMail query from MX Query Builder will now be in the dropdown, and its SQL code will be in the SQL field. Click OK.
Now for the actual sending of the mail messages you will use a trigger A trigger is a SQL procedure that performs an action when a transaction (INSERT, UPDATE, DELETE) occurs. You can use triggers to perform validation of input data, to automatically generate a value for a newly inserted row, to read from other tables for cross-referencing purposes, or to support alerts through e-mail messages. 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 rsMail 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 rsMail.
· 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 job offer.
· 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 job that matches what the user subscribed for has been added 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.
Now all modifications required for the subscription mechanism to work are completed, and you can save and upload all pages to the server. When a job offer is added, as a last operation on the page, a recordset will retrieve all users that match the job type, domain and location, have the minimum desired salary lower or equal to what is being offered, and send an e-mail message to the respective mail addresses.
You can further improve your site in this direction, by allowing subscribers view the ads directly in the mail message, or even apply for it. Also, a link in the mail message pointing directly to the job detail and application page is a handy feature.