Building Database-Driven Applications with PHP and MySQL: Part II
Pre-Filling the Form for Editing
To edit an article, articles.php is called with the article's id number appended in the query string. In order to pre-fill the form we need to retrieve the associated row of data from the table. First, we check to make sure an id number is set, and then we query the database. Then, we query the database for the row matching the id number. Since the id number is unique and we can expect only one row to be returned, there is no need for a While Loop to cycle through the results of the query. We use the list function to assign variable names to each element of the result array.
This entire chunk of code is placed right before the actual form is outputted.
Notes on Security and Data Validation
Accepting external data (be it from the query string, a form, or any other means) is like opening your door to a crowd of strangers without knowing what they're bringing into your home; it is the most vulnerable point of access.
As a programmer, you don't always know who's coming in, but you can at least check their pockets on the way in. Even for an application like this, where the only input will come from yourself or another administrator, it is a good idea to get into the habit of validating incoming data.
Validation is a two-pronged process of first rending users' input harmless and then forcing it to conform to any additional guidelines you establish.
If you were collecting an email address, you might want to check to make sure it was a valid format. For file uploads, you would need to make sure it was of allowed file types and under a certain file size. In this script, likely validation would include making sure that all the fields are filled in and that the fields were stripped of any HTML or PHP.
Because all of the incoming form data is stored in a single array, $_POST, it is easy to validate each field simply by looping through the array. For example, this small function checks each field to make sure it has been filled out:
$_POST is a Super Global array, so it is automatically available within the local scope of any functions. Within this function, a Foreach Loop is used to check each field to make sure it is not empty. If it is, the field name is added to an array. The function returns the array if there are missing values (which can then be outputted to let the user know they've left out some information) or FALSE if everything has been filled in. The results of the function could be used in an If Statement to determine whether the form should be redisplayed with errors, or processing should continue.
A similar method of looping through the $_POST array can be used to strip any code from incoming data by way of PHP's strip_tags function.
strip_tags also accepts an optional second argument which allows certain tags to be specified as "allowed". This is convenient in instances where you may wish to allow some basic formatting tags.
Remember that quotes within a string that are the same as those used to terminate the same string must be escaped. This is done so that the parser does not see a matching quote and think that the string has been terminated.
So what happens when an article containing text like "That's a great idea!" is submitted and included in a query statement? Without escaping the quotes, the query will fail and MySQL will throw back an error like: "You have an error in your SQL syntax near 's a great'".
There are two ways to escape quotes. The first is to do it manually using addslashes() on each value within your query. The second way relies on a PHP configuration setting: magic_quotes_gpc. The setting automatically escapes " (double quotes), ' (single quotes) and \ (backslashes) in all incoming data. Most programmers have a love/hate relationship with magic_quotes_gpc. Superficially, having it on is convenient because it removes the burden of needing to add slashes manually when you are inserting information into the database. But there are a couple of downsides. First, quotes will have to be stripped off when any of the $_REQUEST variables are being outputted to the browser directly; second, though most servers do have magic_quotes_gpc turned on, there is a minority that does not. not all servers have magic_quotes_gpc turned on. If you program with the assumption that it will always be on, then you may run into trouble later if your script is ever distributed or moved to a different server. The sheer number of scripts that have been distributed without taking this into consideration are staggering.
Generally, if you plan on ever distributing your scripts, or forsee moving them to a different server, the rule of thumb here is to never assume that magic_quotes_gpc will be on. PHP has a built in function that checks to find the state of magic_quotes; it returns TRUE when its on, FALSE when its off. Using that, you can easily write a quick function that checks to see if the magic_quotes are on and remove slashes if it is. Included in the global.php file, it will ensure that your script works regardless of the server's configuration settings.
When you are sure that slashes are being removed from incoming data, all the time, you can change your queries to add slashes to your data. For example, the query that was used to add a new article would be changed to look like this:
News Article Listing – index.php
index.php is the main administration page. In addition to providing a link to the add article form, this page also lists all of the existing articles in the database. For each one, an edit and delete link is displayed.
The first line of the script is a call to include the global.php file. With a connection to the database established, we can execute the query to retrieve all the entries. To loop through them, we use a while loop and the mysql_fetch_array() function. Since each itineration of the loop handles just one row of the results, any html within the loop will affect that particular row, be repeated for the next and so on. In this way, it is easy to generate edit and delete links for each entry.
Notice the delete link points back to the running script. Since deleting an article is a relatively simple and small function, it can easily be included in the same script. To remove the article, we simply query the database and remove the row with the matching id.
Page 3 of 4