Using variables in a SQL query
Changed To out whether an author has already been registered, you need to check the authors table to see if any record matches the values submitted in the first_name and family_name fields. In other words, you need to search the database (or in this case, a single table). If there's a match, you need to stop the Insert Record server from executing. Otherwise, the insert operation can go ahead. Since you don't know what will be entered in the form fields, you need to pass their values as variables to the query that creates the recordset.
Passing form values to a SQL query
The way you do this changed in a subtle but important way with the release of the Dreamweaver 8.0.2 updater. If you are upgrading from an earlier version of Dreamweaver, pay careful attention to the instructions in this section. Continue working with author_insert.php.
1. Open the Recordset dialog box in Advanced mode. Name the recordset checkAuthor, and select connAdmin in the Connection field.
2. Expand Tables in the Database items area, highlight the authors table, and click SELECT. Expand authors, highlight first_name, and click WHERE. Then do the same with family_name. You should now have a SQL query that looks like this:
SELECT * FROM authors
WHERE authors.first_name AND authors.family_name
The WHERE expression needs to search for the names entered in the first_name and family_name fields. Although you don't know what the names will be, they will be stored in the $_POST array when the Insert author button is clicked. Instead of entering the PHP variables directly in the SQL query, you need to use runtime variables and define them in the Variables area in the center of the Recordset dialog box.
The runtime variables are not PHP variables, so they shouldn't begin with a dollar sign. You can use any alphanumeric characters to create the variables, as long as they don't clash with the names of columns or any other part of the SQL query. I normally call the runtime variables varl, var2, and so on, but another common convention is to use coll, col2, and so on.
Dreamweaver uses runtime variables to prevent a type of malicious attack known as SQL injection, which exploits poorly written scripts to inject spurious code into SQL queries. SQL injection can be used to gain unauthorized access to a database and even wipe out all the stored data. Dreamweaver changed its approach to SQL injection with the 8.0.2 updater for Dreamweaver 8, so if you're upgrading from an earlier version of Dreamweaver, the way you insert these runtime variables has changed slightly. You will probably also find that recordsets built with versions of Dreamweaver prior to 8.0.2 need to be rebuilt.
Dreamweaver replaces the runtime variables with PHP format specifiers (normally %s or %d), and uses the GetSOLValueString() function (see "Inspecting the server behavior code" in Chapter 14) to handle quotes and other characters that might cause problems with the SQL query. It also automatically adds quotes around text values. This is an important change. Prior to Dreamweaver 8.0.2, you needed to add the quotes around the runtime variables yourself. Now you insert the runtime variables without quotes.
3. I'm going to use varl and var2 as my runtime variables, so change the last line of the SQL query like this:
WHERE authors.first_name = varl AND authors.family_name = var2
4. You now need to define the runtime variables. Click the plus button alongside the Variables label in the Recordset dialog box. This opens the Add Variable dialog box, which has the following four fields:
■ Name: This is the name of the runtime variable that you want to define.
■ Type: This is a drop-down menu with four options: Numeric, Text, Date, and Double. Numeric accepts whole numbers (integers) only. Text is self-explanatory. The Date option doesn't have any practical use in PHP, so you can ignore it. Double is for floating-point numbers with a decimal fraction.
■ Default value: As you'll see in the next chapter, Dreamweaver handles this value in an unexpected way. The only time it's used is when you click the Test button inside the Recordset dialog box or when the page first loads. You must enter a value in this field, because Dreamweaver uses it to prevent a MySQL error if the variable defined as Runtime value doesn't exist. Unless you want to display a default recordset result when a page first loads, set this to -1 or anything that produces no results.
■ Runtime value: This is the value you want to use instead of the runtime variable.
5. When the form is submitted, you want var1 to use the value in the first_name field, so set Runtime value to $_POST['first_name']. Unless you want to check the SQL with the Test button, enter anything in the Default value field. Here are the settings that I used:
|
Add Variable |bÉÊ3B| | ||
|
Name: varl |
1 OK : | |
|
Typer [Text ▼] |
Cancel | |
|
Default value: none |
Help ] | |
|
Runtime value: s_POST[first_name'J | ||
PHP is case sensitive, so make sure that $_POST is all uppercase. Click OK.
6. Define var2 in the same way, using $_POST['family_name'] as Runtime value. The central section of the Recordset dialog box should look like this:
SQL: SELECT"
FROM authors
WHERE authors.first_name = varl AND authors. family_name = var2
Variables:
|
varl |
Name: var2 Type: Text |
|
Default value: none | |
|
Run-time Value: $_POST[family_name] | |
|
| Edit... | |
7. Click OK to close the Recordset dialog box, and save author_insert.php. You can check your code against author_insert_02.php.
Preventing duplicate entries
The recordset that you created in the preceding section checks whether there's already an author of the same name registered in the table. Unfortunately, Dreamweaver puts the code for a recordset immediately above the DOCTYPE declaration, so it's after the Insert Record server behavior. I know what you're thinking, but it doesn't matter which order you enter them. Dreamweaver always puts recordsets beneath Insert Record and Update Record server behaviors, so you need to move it manually.
1. Open Code view. Locate the section of code in the following screenshot:
ivarl_checkAuthcr = "none": if (isset(i_FOST[1first^name1])) {
ivar2_checkAuthcr = "none": if (isset (i_FOST [1 fainil^name1 ] ) ) {
inysql_select_dh (idatabase_ccnrLA±ninr iccnnAdiniri) :
iquery_checkAuthcr = sprint=("SELECT * FROM authors WHERE authors.first_name = AND authors, f air.il y_name = " r Sets QLVa lue S tring ($va rl_che ctcAuthc r, "tint") , GetSQLValue String ( ivar2_checkAuthcrr "text") ) :
iChectcAuthcr = mysql_query ($query_checkfiuthDr, iccnnAdinin) cr die(mysql_errDr()); $rDW_chectiuthcr = inysql_fetch_asscc(icheckAuthcr): itctalRcws_checkAuthcr = inysql_nuin_rcvfs (icheckAuthor) ;
This is the code for the checkAuthor recordset. You can easily identify it, because the first line begins with $var1_checkAuthor, which is the way Dreamweaver defines var1, which you created in step 5. The part of the code that interacts with the database begins with mysql_select_db on line 61 and continues to the end of the line that reads as follows:
$totalRows_checkAuthor = mysql_num_rows($checkAuthor);
As you can probably guess, $totalRows_checkAuthor contains the total number of records in the checkAuthor recordset. You can use this information to determine whether a record already exists for the same author. If the number of rows is zero, there are no matching records, so you can safely insert the new author. But if any matching records are found, you know it's a duplicate, so you need to skip the insert operation and display a warning.
2. Highlight the code shown on lines 53-65 in the screenshot, and cut them to the clipboard.
3. Scroll up about 17 lines, and paste the recordset in the position indicated here:
4. Make sure your cursor is at the end of the code you have just pasted, and press Enter/Return to make room to insert the following code highlighted in bold:
$totalRows_checkAuthor = mysql_num_rows($checkAuthor); // assume that no match has been found $alreadyRegistered = false;
// check whether recordset found any matches if ($totalRows_checkAuthor > 0) {
// if found, reset $alreadyRegistered
$alreadyRegistered = true; }
// go ahead with server behavior if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "forml")) {
Note that false and true in this code block are keywords. They must not be enclosed in quotes.
5. Position your cursor right at the end of the code shown on line 37 in the previous screenshot (it should now be around line 60). This is the beginning of the Insert Record server behavior. Click the Balance Braces button on the Coding toolbar (or press Ctrl+'/Cmd+') to find the end of the server behavior, and insert a closing brace (}) to match the opening one at the end of the code in step 10.
This wraps the Insert Record server behavior in an else clause to prevent it from running if a matching record is found in the authors table.
6. All that remains now is to display a warning message if the insert is abandoned. Scroll down until you find the following code (around line 86):
<h1>Insert new author</h1>
7. Add the following code immediately after it: <?php if ($_POST && $alreadyRegistered) { echo '<p class="warning">'.$_POST['first_name'].' '. ^
$_POST['family_name'].' is already registered</p>'; }
This section of code will run only if the $_POST array has been set (in other words, the insert form has been submitted) and if $alreadyRegistered has been set to true.
8. Save the page, and preview it in a browser. Try inserting a name that you know already exists in the table, such as William Shakespeare. You should see a warning that William Shakespeare is already registered.
Then try a name you know hasn't been registered. You'll see a warning that author_list.php wasn't found (you haven't created it yet), but when you reload quote_insert.php, the new name should be listed in the drop-down menu of authors' names. Check your code against author_insert_03.php if you have any problems.
Although this is an adequate safeguard for a basic content management system, it won't prevent you from entering similar names or misspelled ones.
Post a comment