| The next three pages to be developed will allow users to add URLs, view those already added, and edit the existing records. This section of the site relies upon three tables in the content database: urls, url_categories, and url_associations. See the "Database Scheme" sidebar for details. Adding URLs The add_url.php script will be one of the most complicated ones in the whole Web application on account of the normalized database structure. The script's form will take a URL, a URL title (or name), a description, and the categories with which it should be associated. Upon receiving the form, the URL, URL title, and description values will be added to the urls table. Then the primary key from that table (url_id) will be used along with the url_category_id values to add the record (or records, if multiple categories were selected) to the url_associations table. To refresh your memory as to the why's and how's of this process, refer back to Chapter 5, where these steps were taken within the mysql client. To determine the url_id for the just-added URL, I'll use the mysql_insert_id() function, which I have not discussed before. Whenever a query is run on a table that contains an automatically incremented field (normally this is the primary key), MySQL will use the next logical value for that field. The mysql_insert_id() function will return that value. Database Scheme The database being used in this chapter is called content and was designed and created back in Chapter 5, "Advanced SQL and MySQL." At that time, three normalized tables were defined for handling URLs (the complex structure allows for a URL to be categorized under multiple types). For this chapter, I'll add an uploads table to manage the files. The complete structure for the database can be re-created with CREATE TABLE uploads ( upload_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, file_name VARCHAR(30) NOT NULL, file_size INT(6) UNSIGNED NOT NULL, file_type VARCHAR(30) NOT NULL, description VARCHAR(100) DEFAULT NULL, date_entered TIMESTAMP, PRIMARY KEY (upload_id), KEY (file_name), KEY (date_entered) ) CREATE TABLE urls ( url_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT, url VARCHAR(60) NOT NULL, title VARCHAR(60) NOT NULL, description TINYTEXT NOT NULL, PRIMARY KEY (url_id) ) CREATE TABLE url_categories ( url_category_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, category VARCHAR(20) NOT NULL, PRIMARY KEY (url_category_id) ) CREATE TABLE url_associations ( ua_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT, url_id SMALLINT(4) UNSIGNED NOT NULL, url_category_id TINYINT(3) UNSIGNED NOT NULL, date_submitted TIMESTAMP, approved CHAR(1) DEFAULT 'N' NOT NULL, PRIMARY KEY (ua_id) )
Remember that you can always list the tables in a database using the SQL command SHOW TABLES. To confirm the structure of a table, use DESCRIBE tablename. |
To create add_url.php 1. | Create a new PHP document in your text editor (Script 12.5).
<?php # Script 12.5 - add_url.php $page_title = 'Add a URL'; include ('./includes/header.html'); require_once ('../mysql_connect.  php);
Script 12.5. This script allows users to add URLs, associated with specific categories, to the database.  
 
Both the form itself and the handling of the form require a database connection, so the connection script is included almost immediately. Remember to change the path of your require statement if your connection script is not stored in the directory above this file (see Figure 12.3).
| 2. | Check if the form has been submitted.
if (isset($_POST['submitted'])) {
As this page will both display and handle the form, a hidden form input, named submitted, will be used to determine if the form needs to be handled.
| 3. | Validate a submitted URL.
if (eregi ('^([[:alnum:]\-\.])+  (\.)([[:alnum:]]){2,4}([[:alnum:] /+=%&_\.~?\-]*)$', $_POST['url])) { $u = escape_data($_POST['url']); } else { $u = FALSE; echo '<p><font color="red"> Please enter a valid URL! </font></p>'; }
A regular expression, similar to one defined in Chapter 10, "Web Application Security," is used to validate the URL. It has been slightly modified, so that it does not look for an initial http://, https://, or ftp:// section. If a valid URL is not submitted, or if any field is not filled out, an error message will be displayed (Figure 12.5).
Figure 12.5. If the user fails to properly fill out the form, they'll see errors like these. 
| 4. | Validate the URL's title and description.
if (!empty($_POST['title'])) { $t = escape_data($_POST['title']); } else { $t = FALSE; echo '<p><font color="red"> Please enter a URL name/title! </font></p>'; } if (!empty($_POST['description'])) { $d = escape_data($_POST ['description]); } else { $d = FALSE; echo '<p><font color="red"> Please enter a description! </font></p>'; }
These two form inputs are just checked for a value. If they aren't empty, each value is run through the escape_data() function and assigned to a new variable. If a value is empty, an error message is displayed and the new variable is given a FALSE value.
| 5. | Check that a category has been selected.
if (isset($_POST['types']) && (is_array($_POST['types]))) { $type = TRUE; } else { $type = FALSE; echo '<p><font color="red"> Please select at least one category!</font></p>'; }
The user will be given the option of submitting a URL in several categories, based upon the available types set in the url_categories table. These will be listed in a select box (Figure 12.6), whose name will be types[]. If at least one of the categories is selected, then $_POST['types'] will be set and be of an array type.
Figure 12.6. The user must select at least one category for the URL to be filed under. The select box is populated using the url_categories table. 
| 6. | If everything's okay, add the URL to the urls table.
if ($u && $t && $d && $type) { $query = "INSERT INTO urls (url, title, description) VALUES ('$u, '$t', '$d')"; $result = @mysql_query ($query); $uid = @mysql_insert_id();
Because of the structure of the database, the URL itself must be added to the urls table before an entry can be made into the url_associations table. This query will add the URL and then return the url_id (the auto-incremented primary key for the table), using the mysql_insert_id() function. This value can then be used in the second query (Step 7).
The error suppression operator (@) is used to avoid ugly error messages, which will instead be handled more gracefully later in the script.
| 7. | Build the url_associations INSERT query.
if ($uid > 0) { $query = 'INSERT INTO url_associations (url_id, url_category_id, approved) VALUES '; foreach ($_POST['types'] as $v) { $query .= "($uid, $v, 'Y'), "; } $query = substr ($query, 0, -2);
If the $uid value was retrieved, it's safe to continue with the second query (adding the record to the url_associations table). To do so, I begin defining the query, assigning the initial part to the $query variable. Then I loop through every chosen category, adding a record to the query for each. Finally, I use the substr() function to chop off the last two characters (the comma and the space) from the query. The resulting query will be something along the lines of INSERT INTO url_associations (url_id, url_category_id, approved) VALUES (23, 3, 'Y'), (23, 7, 'Y').
| 8. | Run the second query and report upon the results.
$result = @mysql_query ($query); if (mysql_affected_rows() == count($_POST['types])) { echo '<p><b>Thank you for your submission!</b></p>'; $_POST = array(); } else { echo '<p><font color="red"> Your submission could not be processed due to a system error. We apologize for any inconvenience.</font></p>'; echo '<p><font color="red">' . mysql_error() . '<br /><br /> Query: ' . $query . '</font> </p>'; $query = "DELETE FROM urls WHERE url_id=$uid; @mysql_query ($query); }
After running the query, the conditional checks to see if the number of affected rows is equal to the number of selected URL categoriescount($_POST['types']). If so, the user is thanked and the $_POST array is reset so that the sticky form does not reshow the values.
If a problem occurred, three things happen. First, a generic, public message is printed. Second, a debugging message, containing both the MySQL error and the query itself, is printed for yourthe developer'sbenefit. Finally, the URL is removed from the urls table because if no association could be made between the URL and a specific category (or categories), the URL itself is not needed.
| 9. | Complete the remaining two conditionals.
} else { echo '<p><font color="red"> Your submission could not be processed due to a system error. We apologize for any inconvenience. </font></p>'; echo '<p><font color="red">' . mysql_error() . '<br /> <br />Query: ' . $query . '</font></p>'; } } else { echo '<p><font color="red"> Please try again.</font> </p>'; } }
The first else clause applies if the urls INSERT query does not run properly. Again, a public message and a debugging message are both included in this script, although the debugging one should never be displayed to the public at large.
The second else clause applies if the user failed to fill out the form completely.
| 10. | Begin defining the HTML form.
?> <form action="add_url.php" method="post> <fieldset><legend>Fill out the form to submit a URL:</legend> <p><b>URL:</b> <input type="text" name="url size="60" maxlength="60 value="<?php if (isset($_POST['url])) echo $_POST['url]; ?>" /><br /> <small>Do NOT include the initial <i>http://</i>.</small> </p> <p><b>URL Name/Title:</b> <input type="text name="title" size="60 maxlength="60" value="<?php if (isset($_POST ['title])) echo $_POST ['title]; ?>" /></p> <p><b>Description:</b> <textarea name="description cols="40 rows="5"><?php if (isset($_POST['description])) echo $_POST['description]; ?></textarea></p>
The form itself has four inputs. The first two are text inputs, and the third is a textarea. For sake of consistency, each of these inputs uses the same name and maximum size as the corresponding field in the database.
Each input recalls the previously submitted value, in case an error occurs (Figure 12.7). This is accomplished by setting the value attributes of text inputs and printing a value between the textarea tags. If you have Magic Quotes enabled, you'll want to use the stripslashes() function here as well.
Figure 12.7. The HTML form is sticky, remembering previously entered values. 
| 11. | Create the categories select box.
<p><b>Category/Categories:</b> <select name="types[]" multiple="multiple size="5"> <?php $query = "SELECT * FROM url_categories ORDER BY category ASC; $result = @mysql_query ($query); while ($row = mysql_fetch_array ($result, MYSQL_NUM)) { echo "<option value=\"$row[0]\""; if (isset($_POST['types']) && (in_array($row[0], $_POST ['types]))) { echo ' selected="selected"'; } echo ">$row[1]</option>\n"; } ?> </select></p>
The easiest way to allow for a URL to be associated with multiple categories is to use a select box that accepts multiple selections. The initial select tag<select name="types[]" multiple="multiple" size="5">accomplishes that. You need to also use an array as your select name (types[]) so that an array of values will be passed to PHP.
To populate the options, a query retrieves all of the records from the url_categories table. This alone will create the source code shown in Figure 12.8. The next hurdle is to make this menu sticky. To do so, the code selected="selected" needs to be added to the proper options (Figure 12.9). I accomplish this by checking if the $_POST['types'] variable is set, meaning that the form has been submitted, and if the current category ID value is in $_POST['types'].
Figure 12.8. The HTML source code for the dynamically generated select menu. 
Figure 12.9. The HTML source code for the dynamically generated select menu, with two values remembered (the menu is sticky). 
| 12. | Complete the form and the page.
</fieldset> <input type="hidden" name= "submitted value="TRUE" /> <div align="center"><input type="submit name="submit" value="Submit /></div> </form> <?php mysql_close(); include ('./includes/footer.html'); ?>
Again, the hidden input is used to determine when the form has been submitted. Finally, the database connection is closed and the footer file is included.
| 13. | Save the file as add_url.php, upload to your Web server, and test in your Web browser (Figure 12.10).
Figure 12.10. If the URL submission process worked, a message is displayed and the form is shown again. 
|
 Tips
If you want to deny the use of HTML in submitted values, the strip_tags() function can be used to remove all HTML and PHP tags. Refer back to Chapter 10 for more information. Another alteration you could make to this script would be a check ensuring that a URL is not already in the database. Theoretically, the rows in the urls table should all be unique. The mysql_insert_id() function is specific to each individual connection (interaction with a database). For this reason, you need not be concerned with the wrong value being returned even if this script were being run simultaneously by several different users. The mysql_insert_id() function is the PHP equivalent of MySQL's LAST_INSERT_ID() function.
Viewing submitted URLs The script for viewing URLs will have two aspects to it: an upper portion that displays a pull-down menu of available categories and a lower portion that displays all of the links for a particular category. The first time a user accesses the page, no URLs are displayed. Once a user selects a category and submits the form, the page is displayed again, listing the URLs for that category (and the pull-down menu will still be available). This page will also provide links to edit_url.php, to be written next. Obviously this is the kind of link you would provide in order to administer a site; the ability to edit a URL would not be a publicly available feature. The list() Function I have not yet formally discussed the list() function that's incorporated into the view_urls.php script. This function takes the values of an array and assigns them to individual variables. For example: $var = array ("Larry", "Ullman"); list ($first, $last) = $var;
Now the $first and $last variables will have the values of Larry and Ullman, respectively. Personally, I most frequently use list() when interacting with databases, specifically when selecting just a couple of columns of information from one row. For example: $result = mysql_query ("SELECT tablename WHERE ($result, MYSQL_NUM);
Rather than assigning the results of a mysql_fetch_array() call to a $row variable and then using it as an array, it is sometimes easier to create new variables using list(). |
To create view_urls.php 1. | Create a new PHP document in your text editor, including the requisite files (Script 12.6).
<?php # Script 12.6 - view_urls.php $page_title = 'View URLs'; include ('./includes/header.html'); require_once ('../mysql_connect. php);
Script 12.6. The view_urls.php page displays both a menu of URL categories and the URLs for a particular category. It also provides links to edit a record. 
 
| 2. | Begin the HTML form.
echo '<div align="center"> <form method="get" action= "view_urls.php> <select name="type"> <option value="NULL">Choose a Category:</option> ';
Upon first arriving at the page, I'll want to show a form (Figure 12.11) that is just a pull-down menu and a submit button. Here, I've begun the HTML for the form. Notice that the form uses the GET method and is submitted back to this same page.
Figure 12.11. The page displays just the pull-down menu of categories when first viewed. 
| 3. | Retrieve all of the available URL categories, adding each to the pull-down menu.
$query = 'SELECT * FROM url_categories ORDER BY category ASC; $result = mysql_query ($query); while ($row = mysql_fetch_array ($result, MYSQL_NUM)) { echo "<option value=\"$row[0] \">$row[1]</option> "; }
This code will retrieve every URL type from the url_categories table and use the returned records to create the HTML code (Figure 12.12) for the pull-down menu.
Figure 12.12. The dynamically generated HTML source code of the form. 
| 4. | Complete the HTML form.
echo '</select> <input type="submit" name="submit" value="Go!"> </form> </div> ';
| 5. | Check if a category has been selected and retrieve the information for that category.
if (isset($_GET['type'])) { $type = (int) $_GET['type']; } else { $type = 0; } if ($type > 0) { $query = "SELECT category FROM url_categories WHERE url_category_id=$type; $result = mysql_query ($query); list ($category) = mysql_fetch_array ($result, MYSQL_NUM); echo "<hr /><div align=\"center\"> <b>$category Links</b><br /> <small>(All links will open in their own window. Recently added links are listed first.)</small></div>\n;
If a category has already been selected (in which case, it will have been appended to the URL and available through the $_GET array), the URLs for that category should be retrieved.
The first step is to use typecasting and a conditional to ensure that $type is a positive integer before using it in a query. This concept was discussed in Chapter 10. Then I retrieve the name of the specific category selected using the list() function (see the sidebar). This value is displayed as a header for the page.
| 6. | Initialize a $first variable and query the database.
$first = TRUE; $query = "SELECT u.url_id, url, title, description FROM urls AS u, url_associations AS ua WHERE u.url_id = ua.url_id AND ua.url_category_id=$type AND ua.approved = 'Y ORDER BY date_submitted DESC; $result = mysql_query ($query);
The $first variable will be used for two purposes. First, it will indicate that the HTML table should be started before displaying the first record. Second, it will be used to test whether or not there were any URLs returned by the query. The query itself is a JOIN on two tables, using the URL category in a WHERE condition.
| 7. | Print out all of the returned records.
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) { if ($first) { echo '<table border="0" width="100%" cellspacing="3 cellpadding="3 align="center> <tr> <td align="right" width="40%"> <font size="+1>Link</font></td> <td align="left" width="50%"> <font size="+1>Description </font></td> <td align="center" width= "10%"> </td> </tr>'; $first = FALSE; } echo " <tr> <td align=\"right\"><a href=\ "http://{$row['url]}\" target=\"_new\">{$row ['title]}</a></td> <td align=\"left\ ">{$row['description]}</td> <td align=\"center\"><a href=\ "edit_url.php?uid={$row ['url_id]}\">edit</a></td> </tr>\n"; }
The while loop will return every record retrieved by the query. Within the while loop, each record is printed in a three-column table. The first column is the URL name, printed as a link to the actual URL. The second column is the description. And the third is an edit link, which passes the URL ID to the edit_url.php page.
Before the first record is displayed, the table and table header will be sent to the browser (Figure 12.13). This occurs if $first is trUE, which it will be the first time that the loop is executed. The variable is then set to FALSE, so that the table header is not printed for each returned record.
Figure 12.13. A list of URLs for a particular type. 
| 8. | Print a message if no URLs were returned and complete the main conditional.
if ($first) { echo '<div align="center"> There are currently no links in this category.</div>'; } else { echo '</table>'; } }
The $first variable will be set to FALSE in the while loop if any rows are returned. Therefore, if $first is still TRUE at this point, there were no records retrieved and a message should be sent saying such (Figure 12.14); otherwise, the table should be completed.
Figure 12.14. The resulting page if a category has no associated URLs. 
| 9. | Complete the HTML page.
mysql_close(); include ('./includes/footer.html'); ?>
| 10. | Save the file as view_urls.php, upload to your Web server, and test in your Web browser.
|
Tips I have not included any MySQL-related error management in this script. The reason is that the queries are essentially static, with only one valuethe URL category IDchanging. If you have problems developing this script, use the standard PHP-MySQL debugging techniques: print out queries, print out MySQL errors, run queries using the mysql client or other interfaces, etc. If you want, you can apply the nl2br() function (it stands for newline to break) to the description field. This function will turn every newline charactercreated by pressing Return or Enterinto an (X)HTML <br /> tag.
Editing URLs The serialize() and urlencode() Functions Another previously unmentioned function to be used in this chapter is serialize(). This function can turn complex data structures into a more portable format. This function is necessary because, for example, an array cannot be inserted into a database, stored in a hidden form input, or sent in a cookie. Serialize() takes data and creates a string that represents the original structure and value. Say you have an array defined like so: $array = array("Penn", "State");
The serialized version of that array would be: a:2:{i:0;s:4:"Penn";i:1;s:5:"State";}
Looking at that syntax, you can see that the data represents an array of two elements (a:2). The first element, indexed at 0, is a string of four letters whose value is Penn. The second element, indexed at 1, is a string of five letters whose value is State. Because the serialized form of data often contains quotation marks and other problematic characters, you'll want to address those before storing the data. In this example, I will use the urlencode() function, which encodes data so that it is safe to pass from Web page to Web page (in a URL or a form). To return data to its original format, use the unserialize() function. If you encoded the data before storing it, use the corresponding decode functionlike urldecode()prior to using unserialize(). You'll see all of this in action in this example. If you still don't understand any of these functions, see the PHP manual or post a message to the book's support forum. |
The final URL-related script will let a user edit or delete an existing URL record. The process is generally easier than one might think. First, the URL's existing information must be retrieved from the database and displayed in the form. This is essentially a sticky form, just like the add_url.php page. Added to this form is a pair of radio buttons letting the user select whether they want to edit or delete the record. Upon form submission, the database information needs to be updated. If the URL is being deleted, DELETE queries must be run on both the urls and url_associations tables. If the URL's information is being changed, then UPDATE queries are required. For the url_associations table, this is a little more complex, as a URL's information could be stored in multiple rows (for multiple categories). One way to facilitate this update is by noting what the current URL categories are. To do so, I'll make use of the serialize() function (see the sidebar) in storing the current values as a hidden form input. Then the handling page will receive both the original and the new URL categories. Finally, remember that in SQL your UPDATE and DELETE queries normally use a primary key in a WHERE clause (e.g., DELETE FROM tablename WHERE pk=X). For this reason, the edit_url.php page receives the URL's url_id value, through a link in view_urls.php, as $_GET['uid']. This value also needs to be stored in the form as a hidden input so that the handling script can reference it. To create the edit_url.php page 1. | Start a new PHP document in your text editor or IDE (Script 12.7).
<?php # Script 12.7 - edit_url.php $page_title = 'Edit a URL'; include ('./includes/header.html');
Script 12.7. The edit_url.php page lets the user edit or delete existing URL records. 
 
 
| 2. | Check for a valid URL ID.
if (isset($_GET['uid'])) { $uid = (int) $_GET['uid']; } elseif (isset($_POST['uid'])) { $uid = (int) $_POST['uid']; } else { $uid = 0; } if ($uid <= 0) { echo '<p><font color="red"> This page has been accessed incorrectly!</font></p>'; include ('./includes/footer. html); exit(); }
This page will only work if it always has access to the URL's ID. The first conditional checks for its presence in either the $_GET or $_POST array, type-casting it to an integer in either case (just to be safe). If neither array contains a uid element, then $uid is set to 0.
If $uid is not a positive number, meaning either that it wasn't received or an invalid value was received, the script should be terminated. To do so, an error message is displayed, the footer is included, and the execution of the script is halted (Figure 12.15).
Figure 12.15. If the script does not receive a valid URL ID, this is the result. 
| 3. | Include the database connection and see if the form has been submitted.
require_once ('../mysql_connect. php); if (isset($_POST['submitted'])) {
| 4. | Delete the existing record, if applicable.
if ($_POST['which'] == 'delete') { $query = "DELETE FROM urls WHERE url_id=$uid; $result = mysql_query($query); $affect = mysql_affected_rows(); $query = "DELETE FROM url_associations WHERE url_id=$uid; $result = mysql_query($query); $affect += mysql_affected_rows();
The form (Figure 12.16) has a pair of radio buttons, allowing the user to indicate whether they are editing or deleting a record (edit is the default). This first conditional checks if the user has opted to delete the record. If so, DELETE queries need to be run on both tables, using the url_id in a WHERE clause. To be able to report upon the success of the queries (see Step 5), the $affect variable will store the total number of affected rows.
Figure 12.16. At the top of the form are two radio buttons so that the user can specify whether the record should be merely edited or removed altogether. 
| 5. | Report on the success of the DELETE queries and complete the page.
if ($affect > 0) { echo '<p><b>The URL has been deleted!</b></p>'; } else { echo '<p><font color="red"> Your submission could not be processed due to a system error. We apologize for any inconvenience.</font></p>'; } include ('./includes/footer.html'); exit();
A bare minimum of testing is done here, just seeing if at least one row was affected by the deletion. Messages report upon the overall success. Then the footer is included and the page is terminated so that the form is not shown for a no-longer-existing record.
| 6. | Validate all of the form fields to begin the edit thread.
} else { if (eregi ('^([[:alnum:]\-\.])+ (\.)([[:alnum:]]){2,4}([[: alnum:]/+=%&_\.~?\-]*)$', $_POST['url])) { $u = escape_data($_POST ['url]); } else { $u = FALSE; echo '<p><font color="red"> Please enter a valid URL! </font></p>'; } if (!empty($_POST['title'])) { $t = escape_data($_POST ['title]); } else { $t = FALSE; echo '<p><font color="red"> Please enter a URL name/title! </font></p>'; } if (!empty($_POST['description'])) { $d = escape_data($_POST ['description]); } else { $d = FALSE; echo '<p><font color="red"> Please enter a description! </font></p>'; } if (isset($_POST['types']) && (is_array($_POST['types]))) { $type = TRUE; } else { $type = FALSE; echo '<p><font color="red"> Please select at least one category!</font></p>'; }
This else clause takes effect if $_POST ['which'] is not equal to delete. The first steps are to validate the form data, exactly as you do in the add_url.php page.
| 7. | If the data passed all of the tests, update the record in the urls table.
if ($u && $t && $d && $type) { $query1 = "UPDATE urls SET url='$u, title='$t', description='$d WHERE url_id=$uid; $result1 = mysql_query($query1);
Updating the urls table is quite simple, needing only a basic UPDATE query.
| 8. | Retrieve the original categories.
$exist_types = unserialize(urldecode ($_POST['exist_types]));
In order to update the list of categories associated with a URL, I need to know what they originally were and what they are now. The original categories are stored as a hidden form input. To access them again, I reverse the serialization and encoding process. Here the $_POST['exist_types'] value is first run through urldecode() and then through unserialize(). The result will be an array of the URL's original category IDs.
| 9. | Determine what category updates, if any, need to be made.
if ($_POST['types'] != $exist_types) { $add = array_diff($_POST['types'], $exist_types); $delete = array_diff($exist_types, $_POST['types]);
If the user did not change the associated categories for this URL, no updates are required. To test for this, I compare the array of original categories ($exist_types) to the array of selected categories ($_POST['types']). If they are not the same, I need to determine exactly how they differ.
To do that, I use the array_diff() function twice. This function returns an array of values that are in the first argument but not in the second. Any categories that are in $_POST['types'] but not $exist_types have been added, meaning they were additionally selected by the user. Any categories that are in $exist_types but not $_POST['types'] have been deleted, meaning they were deselected by the user.
| 10. | Add the new category types for this URL to the database.
if (!empty($add)) { $query2 = 'INSERT INTO url_associations (url_id, url_category_id, approved) VALUES '; foreach ($add as $v) { $query2 .= "($uid, $v, 'Y'), "; } $query2 = substr ($query2, 0, -2); $result2 = mysql_query ($query2); } else { $result2 = TRUE; }
If the user has associated this URL with new categories, I need to add them to the url_associations table. The process and query for doing so is exactly like that in the add_url.php page. Also, I'm using a $result2 variable to indicate the success of this specific operation. If there are no categories to be addedmeaning no query was runI set this value to TRUE.
| 11. | Remove the old categories associated with this URL from the database.
if (!empty($delete)) { $query3 = "DELETE FROM url_associations WHERE (url_id=$uid) AND (url_category_id IN (". implode (',', $delete) . "))"; $result3 = mysql_query($query3); } else { $result3 = TRUE; }
If the user deselected any categories, those records should be deleted from the url_associations table. Using an IN condition and PHP's implode() function, I can quickly generate a query of the form DELETE FROM url_associations WHERE (url_id=X) AND (url_category IN (A, B, C)).
Again, a flag variable ($result3) is used to track the success of this operation.
| 12. | Report on the success of the edits.
} else { $result2 = TRUE; $result3 = TRUE; } if ($result1 && $result2 && $result3) { echo '<p><b>The URL has been edited!</b></p>'; } else { echo '<p><font color="red"> Your submission could not be processed due to a system error. We apologize for any inconvenience.</font></p>'; }
The first else clause takes effect if the selected categories haven't changed. Then I check if $result1, $result2, and $result3 are all trUE. If so, every query ran successfully and a message is printed. If not, a generic message is printed and you'll need to do some debugging detective work to find the problem.
| 13. | Complete all of the conditionals.
} else { echo '<p><font color="red"> Please try again.</font> </p>'; } } }
The first else clause prints a message if the URL was being edited and the user omitted a field (Figure 12.17). The penultimate closing brace completes the edit/delete conditional. And the final closing brace completes the submission conditional.
Figure 12.17. The editing process still checks, and reports upon the validity of, the form data. 
| 14. | Retrieve the URL's current information.
$query = "SELECT url, title, description, url_category_id FROM urls LEFT JOIN url_associations USING (url_id) WHERE urls.url_id=$uid; $result = mysql_query ($query); $exist_types = array(); list($url, $title, $desc, $exist_types[]) = mysql_fetch_array ($result, MYSQL_NUM); while ($row = mysql_fetch_array ($result, MYSQL_NUM)) { $exist_types[] = $row[3]; }
To populate the form with the current URL data, I first need to retrieve it from the database. To do so, I run a LEFT JOIN query, grabbing the url, title, and description from the urls table as well as the url_category_id from the url_associations table. The first three values I only need to retrieve once, but they'll be present in every returned row (see the results in Figure 12.18). Therefore, I call mysql_fetch_array() once to set the $url, the $title, and the $description variables. Then I loop through the remaining records in order to finish creating the $exist_types array. The end result will be three strings$url, $title, and $descriptionand one array, $exist_types.
Figure 12.18. The query, executed here in the mysql client, returns all of the information I require, although some of it is redundant. 
Because the form submissions section of the script also uses an $exist_types array, I first reset this variable with the array() function.
| 15. | Begin the HTML form.
?> <form action="edit_url.php" method="post> <fieldset><legend>Edit a URL: </legend> <p><b>Select One:</b> <input type="radio name="which" value="edit checked="checked" /> Edit <input type="radio name="which value="delete" /> Delete</p>
The form itself (Figure 12.19) will be a lot like the add_url.php form, although it does have these two radio buttons at the top.
Figure 12.19. The HTML form, as it looks upon first arriving (after clicking on an edit link in view_urls.php). 
| 16. | Continue creating the HTML form, inserting existing values as needed.
<p><b>URL:</b> <input type="text" name="url s ize="60" maxlength="60 value="<?php echo $url; ?>" /><br /><small>Do NOT include the initial <i>http:// </i>.</small></p> <p><b>URL Name/Title:</b> <input type="text name="title" size="60 maxlength="60" value="<?php echo $title; ?>" /></p> <p><b>Description:</b> <textarea name="description cols="40" rows="5><?php echo $desc; ?></textarea></p>
Each of the form inputs has a preset value using the currently stored value in the database.
| 17. | Create the categories select menu box.
<p><b>Category/Categories:</b> <select name="types[]" multiple= "multiple size="5"> <?php $query = "SELECT * FROM url_categories ORDER BY category ASC; $result = @mysql_query ($query); while ($row = mysql_fetch_array ($result, MYSQL_NUM)) { echo "<option value=\"$row[0]\""; if (in_array($row[0], $exist_types)) { echo ' selected="selected"'; } echo ">$row[1]</option>\n"; } ?> </select></p>
This section of code is also exactly like its predecessor in add_url.php, except that its sticky quality is based upon the $exist_types array, not $_POST['types'].
| 18. | Add the remaining elements, including two new hidden inputs.
</fieldset> <input type="hidden" name="submitted value="TRUE" /> <?php echo '<input type="hidden" name="exist_types value="' . urlencode(serialize ($exist_types)) . '" /> <input type="hidden" name="uid" value="' . $uid . '" /> '; ?> <div align="center"><input type="submit name="submit" value="Submit /></div>
The exist_types hidden form input stores an array of the currently associated URL categories. Doing so requires using both the serialize() and urlencode() functions (see the sidebar). The next hidden form input is the URL's ID, which will be needed to update or delete the records in the database. The resulting HTML source code is shown in Figure 12.20.
Figure 12.20. Two of the three hidden form inputs are populated by PHP. Both are required by the form processing side of the script. 
| 19. | Complete the form and the page.
</form> <?php mysql_close(); include ('./includes/footer.html'); ?>
Site Administration Although I have not created an administrative side to this application, doing so would not be difficult. First, add an approved field to the uploads table. Then change both add_url.php and add_file.php so that approved is set to N by default. On the administrative side, create a page that retrieves all the content that has not yet been approved. To approve recently added content, run an UPDATE query on the appropriate table for that record, changing approved to Y. On the public side, change the view_urls.php and view_files.php queries so that they only select records where approved is equal to Y. One of the benefits of this file upload system is that it's easy to correlate files on the server to those in the database (since the upload_id is used as the filename). If you write an administrative script to remove files from the server, you can easily delete both the record from the table and the file from the uploads folder using the upload_id. The examples in the next chapter cover user registration and authentication. Those techniques could be coupled with this application if you wanted to protect the managed information. |
| 20. | Save the script as edit_url.php, upload to your Web server, and test in your Web browser.
|
Tips For more information about the array_diff() or other functions used here, see the PHP manual. Because the mysql_error() function returns the error caused by the latest query associated with a connection, you must call it after each mysql_query() call in order to accurately debug this script. If you are confused by any of the queries, print out the query using PHP, then run it using the mysql client or other interface in order to see the results.
|
