In this final article in our web blog series, we will discuss the code that is required to edit and delete entries from the database. Features that will be covered here will include techniques to create ??sticky?? forms and the use of the mySQL update and delete commands. These pages use the hidden ??id?? field that we included in our previous sections to process the correct field.
Editor’s note: It has been brought to our attention that the line which establishes the actual SQL connection is missing. This line will use our function to establish the SQL connection and allow us to process the data. Therefore we must include the following line:
sql_connect('blog');
after the include line on all previous pages.
Two pages are required for the edit functionality of this application. The first page will be the form that is used to edit the data; the second page will process the update into the mySQL database.
The form page takes the data from the mySQL server and displays it in the forms. Since these forms will be using standard HTML, steps will need to be taken so that the stored text does not break the HTML.
We start the same on this page as with all other pages; we create our connection to the mySQL database.
<?php
require ('/some/local/path/sql_connect.inc');
//connecting to mySQL database
sql_conenct('blog');
Now that we have the database connection open, the POST data needs to be collected. This is the hidden ??id?? field that was placed in the administrative page. New here is the usage of ??where?? in the SQL query line. This is another method of restricting specific data collected from the database. In this case, we are limiting which rows are returned for display. The query is now sent to the mySQL server.
//collects the POST data
$id = $_POST['id']
//Define the query
$query = "SELECT * FROM blog_entries WHERE id={$id}";
//run the query
if ($results = mysql_query($query)) {
$row = mysql_fetch_array ($results); //retrieve the info
Now that the data has been retrieved, it needs to get processed for display. ??Title?? and ??entry?? are going to be displayed in text fields within the form. These fields may already contain data so the HTML needs to be stripped out and replaced with the equivalent escape characters. This is done so that any stored HTML tags don??t interfere with the tags on the edit page. PHP includes an ??htmlentities?? function which will accomplish this task.
Since this form’s data is going to be passed to another page for the update, the data still needs to be tracked. As a result,we hide the id field again:
//places the query info into variables for possible parsing or formating. $title = htmlentities($row['title']); $user = htmlentities($row['user]); $id = $row['blog_id']; $entry = htmlentities($row['entry']); ?>
The form is finally created. A shortcut that is commonly used in forms to echo back data is to use <?=$title; ?> rather then <?php echo $title; ?>. The displayed results are the same.
<center>
The page that will obtain this data is to be named editsave.php, so we point our form there:
<form action="editsave.php" method="POST">
<table>
<tr>
<td>
Entery Title:
</td>
<td>
<input type="text" name="title" size="40" maxsize="100" value="<?php echo $title; ?>" />
</td>
<td>
User:
</td>
<td>
<input type="text" name="title" size="20" maxsize="50" value="<?php echo $user: ?>" />
</td>
</tr>
<td>
<td>
<input type="text" name="user" size="20" maxsize="100" value="<?php echo $user; ?>" />
</td>
</tr>
<?php
}
?>
<tr>
<td colspan=2>
Entry Text:
</td>
</tr>
<tr>
<td>
<textarea name="entry" cols="100" rows="15"><?php echo $entry; ?></textarea>
</td>
</tr>
<tr>
<td>
If the user decides to save changes, the page goes to the editsave page:
<form action="editsave.php" method="post">
<input type="hidden" name="id" value="<?php echo $id ; ?>" />
<input type="submit" name="submit" value="Edit" />
</form>
</td>
<td>
If the user wants to cancel any edits, we redirect back to the administrative page:
<form action="admin.php" method="post">
<input type="submit" name="submit" value="Cancel" />
</form>
</td>
</tr>
</table>
</center>
<?php
We close this page with an error catch in case the entry was not found, and then we terminate the mySQL connection.
} else {
echo "<p>Could Not retreive news event. </p>";
}
mysql_close(); //Closes our SQL session
Two pages still need to be created. These are relatively small pages used to process information. Of the two final pages, we will finish the editing process by creating the page that will save the new information to the database. This page differs from the “add page” in that the query needs to modify existing data. To do this, we will use the update query command. Like all of the other pages, the connection to the SQL database will need to be established.
require ('/some/local/path/sql_connect.inc');
//connecting to our SQL database
sql_conenct('blog');
The data from the POST method now needs to be collected and prepared to be added to the database. Trim is used to removed extra spaces and addslashes is necessary if magicquotes is not enabled.
//collects the POST data $title = $_POST['title']; $entry = $_POST['entry']; $user = $_POST['user']; $id = "$_POST['id']; //Cleans the data before imputing the values trim ($title); trim ($entry); trim ($user); //adds necessary escape characters $title = addslashes ($title); $entry = addslashes ($entry); $user = addslashes ($user);
The query uses the hidden ??id?? field to obtain the specific data. The main difference with the processing of this query is that we do not need to actually obtain data. As a result, we use the method ??myql_affected_rows?? to make sure that one line was updated. The results of this method allow us to verify with an if-else statement whether the update was successful or not.
//define the query
$query = "UPDATE blog_entries SET title='$title', entry='$entry', user='$user' WHERE blog_id={$id}";
//executes the query
$results = mysql_query ($query);
//execute the query
?>
<table>
<?php
if (mysql_affected_rows() == 1) {
//if it updated
?>
<table>
<tr>
<td>
Update Saved Successful
</td>
</tr>
<?php
} else {
//if it failed
?>
<tr>
<td>
Update Failed
</td>
</tr>
<?php
}
?>
<tr>
<td>
<form action="admin.php" method="post">
<input type="submit" name="submit" value="Ok" />
</form>
</td>
</tr>
</table>
<?php
mysql_close(); //Closes our SQL session
?>
Finally we get to the delete page. This page is almost identical to the update page, with the difference being that we use the DELETE function of mySQL. This way we do not need to prep any data for the query. Again we use the ??mysql_affected_rows?? function to verify if the query was successful or not.
require ('/some/local/path/sql_connect.inc');
//connecting to our SQL database
sql_conenct('blog');
//Define the query
$query = "DELETE FROM blog_entries WHERE blog_id={$_POST['id']} LIMIT 1";
//sends the query to delete the entry
mysql_query ($query);
if (mysql_affected_rows() == 1) {
//if it updated
?>
<table>
<tr>
<td>
Update Saved Successful
</td>
</tr>
<?php
} else {
//if it failed
?>
<tr>
<td>
Update Failed
</td>
</tr>
<?php
}
?>
<tr>
<td>
<form action="admin.php" method="post">
<input type="submit" name="submit" value="Ok" />
</form>
</td>
</tr>
</table>
<?php
mysql_close(); //Closes our SQL session
?>
The web blog pages have now all been created! We learned how to use new functions such has htmlentities, mysql_affected_rows and also learned how to create the forms. New SQL commands were covered such as update and delete.
The process we learned here for adding, modifying and deleting data can also be applied in a variety of other applications–they are truly universal. A similar page can be used to store web links, or it could be used to allow users to post events on the page, rather then just the administrator.
I hope you have enjoyed this tutorial as much as I??ve enjoyed writing it. Stay tuned for another application that we will disect, and as always, we appreciate your comments and suggestions!
