Deleting Authors from a PEAR Content Management System - Extracting Database Information
(Page 3 of 4 )
This part of the code is at the heart of the page. It is responsible for extracting the needed database information. In order for this code to use any kind of database abstraction, it requires the DB classes. The methods contained in the class are what we will use to interact with the database server and tables.
Our main aim is to get a list of author names as well as their author IDs. Since we know that the authors database table has only two fields, we construct a simple query that retrieves all the fields from the table, and then run it using the DB’s query() function. If the table had contained more then two fields, we would have had a SQL query that looked something like this:
$sql=”SELECT aid,name FROM authors”;
instead of
$sql=”SELECT * FROM authors”;
The reason for this is that we require only two fields; retrieving any other fields would just slow down the data extraction process. To make the DB class and its methods available to our script, we include the DB.php file. Now, to make those methods and function useful, we need the database connection details. These are contained in the connx.php file. So we include the connx.php file:
<table width="100%" border="1">
<?php
include 'db.php';
include 'connx.php';
We then run the SQL code that is required using DB’s $db->query() function and store the returned rows in a variable called $res:
$sql = "SELECT * FROM authors";
$res = $db->query($sql);
The next thing we do is test the $res variable to see if it actually contains any values. So we run a conditional check, and if the variable contains any data we run a while loop. The while loop will iterate through the results set and lists the name of the author together with the delete option. All of this is displayed in a dynamic table whose rows are created as the records are retrieved:
if($res){
while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
?>
<tr>
<td width="25%" class="auth"><a href="authart.php?aid=<?=$row->aid?>" class="auth">$row->name</a></td>
<td width="75%" class="auth"><a href="delete.php?aid=<?=$row->aid?>">Delete</a> </td>
</tr>
Notice that when the rows are retrieved, links to the authart and delete scripts are created.
Along with these links is included the author ID, which will be used by the relevant script to either delete or retrieve information based on that ID:
<td width="25%" class="auth"><a href="authart.php?aid=<?=$row->aid?>" class="auth">$row->name</a></td>
<td width="75%" class="auth"><a href="delete.php?aid=<?=$row->aid?>">Delete</a> </td>
If the $res variable does not contain any records, then the code displays a message stating that it did not find any authors in the database:
<? }
}else{
?>
<tr>
<td colspan="3" class="maintxt"><p class="auth">No authors found</p></td>
</tr>
<? } ?>
</table>
The HTML table is then closed and the HTML for the page is also closed.
<!-- InstanceEndEditable --></td>
</tr>
<tr class="copy">
<td colspan="2">©2008</td>
</tr>
</table>
</body>
<!-- InstanceEnd --></html>
Next: Adding Authors >>
More PEAR Articles Articles
More By David Web
