Along with the addition, deletion, and modification of jokes in our database, we also need to be able to assign categories and authors to our jokes. Furthermore, we're likely to have many more jokes than authors or categories. As a result, to try to display a complete list of jokes, as we did for the authors and categories, could result in an unmanageably long list, and no easy way to spot the one joke we're after. So we need to create a more intelligent method of browsing our library of jokes.
At different times we may know the category, author, or some of the text in a joke we wish to work with, so let's support all of these methods for the location of jokes in our database. When we're done, it should work like a simple search engine. The form that will prompt the administrator for information about the desired joke must present lists of categories and authors. The code for this is as follows:
<!-- -->
<l>
<head>
<title> Manage Jokes </title>
</head>
<body>
<h1>Manage Jokes</h1>
<p><a href=">Create New Joke</a></p>
<?php
$dbcnx = mysql_connect('localhost', 'root', 'mypasswd');
mysql_select_db('jokes');
$authors = mysql_query('SELECT ID, Name FROM Authors');
$cats = mysql_query('SELECT ID, Name FROM Categories');
?>
<form action="jokelist.php" method="post">
<p>View jokes satisfying the following criteria:<br />
By Author:
<select name="aid" size="1">
<option selected value=">Any Author</option>
<?php
while ($author = mysql_fetch_array($authors)) {
$aid = $author['ID'];
$aname =lspecialchars($author['Name']);
echo("<option value='$aid'>$aname</option>\n");
}
?>
</select><br />
By Category:
<select name="cid" size="1">
<option selected value=">Any Category</option>
<?php
while ($cat = mysql_fetch_array($cats)) {
$cid = $cat['ID'];
$cname =lspecialchars($cat['Name']);
echo("<option value='$cid'>$cname</option>\n");
}
?>
</select><br />
Containing Text: <input type="text" name="searchtext" /><br />
<input type="submit" name="submit" value="Search" />
</form>
<p align="center"><a href=">Return to Front Page</a>
</p>
</body>
<l>
Note that the \n at the end of
the strings that are output by the echo function is the
special code for a new line, which serves to make the HTML code output by
this script more readable.[1] Also, note the use of It's up to jokelist.php to use the values submitted
in the above form to build a list of jokes that satisfies the criteria specified.
Obviously, this will be done with a SELECT query, but the
exact nature of that query will depend on what was entered through the form
we defined above. Because the building of this SELECT statement
is a fairly complicated process, let's work through jokelist.php a
little at a time. First, we get the preliminaries out of the way: Now, to start, we define a few
strings that, when strung together, form the SELECT query
we'd need if no constraints had been selected in the form: The WHERE clause in the above code might be somewhat
confusing. The idea here is for us to be able to build on this basic SELECT statement,
depending on which constraints are selected in the form. These constraints
will require us to add to the FROM and WHERE clauses
(portions) of the SELECT statement. But if there were no
constraints specified (i.e. the administrator wanted a list of all jokes in
the database), there would be no need for a WHERE clause
at all! Because it's difficult to add to a WHERE clause
that doesn't exist, we needed to come up with a "do nothing" WHERE clause
that will always be true. Thus, we have introduced the requirement that 1
equals 1, which fits the bill nicely[2]. Our next task is to check each of the possible constraints (author,
category, and search text) that may have been set in the form, and adjust
the SQL accordingly. First, we deal with the possibility that an author was
specified. The "Any Author" option in the form was given a value of " (the
empty string), so if the value of that form field (stored in $_POST['aid'])
is not equal to ", then an author has been specified,
and we must adjust our query: .=,
the string concatenation operator is used to tack a
new string onto the end of an existing one. In this case, we add to the WHERE clause
the condition that the AID in the Jokes table must match the author ID selected in
the form ($aid). Next, we handle the specification of a joke category: As the categories associated with a particular joke are stored in the JokeLookup table, we need to add this table to the
query to create a join. To do this, we simply tack the name of the table onto
the end of the $from variable. And to complete the join,
we must also specify that the ID column (in the Jokes table)
must match the JID column (in JokeLookup), so we add this condition to the $where variable.
Finally, we require the CID column (in JokeLookup) to match the category ID selected in
the form ($cid). Handling search text is fairly simple, and uses the LIKE"Getting Started with MySQL": Now that we've built our SQL query, we can use it to retrieve and display
our jokes, along with links that allow us to edit and delete them, just like
we did for authors and joke categories. For readability, we display our jokes
in an HTML table: With jokelist.php out of the way, let's tackle ,
which is linked-to at the top of . This page
will be very similar to newauthor.php and newcat.php.
However, in addition to specifying the joke text, this page must allow an
administrator to assign an author and categories to a joke. These features
make the code of this file worth some examination. We know from viewing the code of newauthor.php,
that the PHP code that processes the form submission comes before the form
code itself. It doesn't have to, but this is the layout we've used so far.
Let's begin by looking at the form code, so that the code for handling form
submissions makes more sense. First, we fetch lists of all the authors and categories in the database: Next, we create our form. We begin with a standard text area for typing
in the text of the joke: We'll prompt the administrator to select an author from a drop-down
list of those authors in the database: However, a drop-down list won't suffice for the selection of categories,
because we want the administrator to be able to select multiple categories.
Thus, we'll use a series of check boxes—one for each category. Since
we have no way to know in advance the number of check boxes we'll need, the
matter of naming them becomes an interesting challenge. What we'll actually
do is use a single variable for all of the check boxes;
thus, all the check boxes will have the same name. To be able to receive multiple
values from a single variable name, we must make that variable an array.
Recall from "Getting Started with PHP" that an array is a
single variable with 'compartments', each of which can hold a value. To submit
a form element as part of an array variable, we simply add a pair
of square brackets to the end of the variable name (making it cats[] in
this case).[3]
With all of our check boxes named the same, we'll need a way to identify
which particular check boxes have been selected. To this end, we assign a
different value to each check box—the ID of the corresponding category
in the database. Thus, what gets submitted by the form is an array that contains
all category IDs to which the new joke should be added. And we finish off our form as usual: Since we're submitting an array for the first time, the code that
processes this form is not totally straightforward. It starts off pretty simply
as we add the joke to the Jokes table.
Since an author is required, we make sure that $_POST['aid'] contains
a value. This prevents the administrator from choosing the "Select One" option
in the author select list, as that choice has a value of " (the
empty string). The last line in the above code uses a function that we haven't seen
before: mysql_insert_id. This function returns the number assigned to the last-inserted
entry by the AUTO_INCREMENT feature in
MySQL. In other words, it retrieves the ID of the newly inserted joke, which
we'll need later. The code that adds the entries to JokeLookup based
on which check boxes were checked is not so simple. First of all, we've never
seen how a check box passes its value to a PHP variable before. Also, we need
to deal with the fact that these particular check boxes will submit into an
array variable. A typical check box will pass its value to a PHP variable if it is checked,
and will do nothing when it is unchecked. Check boxes without assigned values
pass "on" as the value of their corresponding variables when they are checked.
However, we've assigned values to our check boxes (the category IDs), so this
is not an issue. The fact that these check boxes submit into an array actually adds quite
a measure of convenience to our code. In essence, what we'll receive from
the submitted form is either an array of category IDs to add the joke to, or nothing at all (if none of the check boxes were checked). First, let's handle the latter, special case, by creating an
empty array when we find that the $cats variable is empty: The array function that appears here is used
to create a new array in PHP. The parameters that are passed to it become
the elements of the array. Because we're passing no parameters to it here,
it'll simply create an empty array. Now that we've
guaranteed that the $cats variable contains an array, we
can use a loop to consider each category ID in the array in turn, and to insert
the appropriate entry into the database. Since this array isn't based on a
database row, you might wonder how we can access the values in the array.
After all, we've usually retrieved an array value using its database column
name (e.g. $cat['Name']). In this case, our array was created
simply by feeding a series of values into the same variable name. When this
happens, PHP automatically assigns numerical indices to the values in the
array. For instance, the value of the first check box that was checked will
be submitted first into the array and will be accessible as $cat[0].
That is, PHP assigns it an array index of 0. The second check box that is
checked will have its value stored with an index of 1, accessible as $cat[1].
So if there are n check boxes checked, then the
value of the last check box will be in $cat[n-1].
By counting up through the array indexes as we proceed through a loop in our
code, we can process the elements of this array one at a time. But wait... what is n? We have no way of
knowing in advance how many check boxes will be checked, so how should the
loop know when to stop counting? Well, there are two ways. The first is to
use a PHP function called count that takes an array as a parameter
and counts the number of elements in it. Here's what our while loop would look like
if we use this method: As you can see, this loop uses a counter variable ($i),
that is, a variable that counts the number of times the loop has executed.
The first time through the loop, it will have a value of 0, then at the end
of the loop we'll add 1 to it. Therefore, the second time through the loop
it will have a value of 1, and so on. Within the loop, we can use this variable
as the array index to pull a category ID out of the $cats array.
The loop stops looping when $i reaches count($cats),
the number of elements in the $cats array. If $cats doesn't
contain any elements (i.e. if no categories were selected), then $i will start
out equal to count($cats), and the contents
of the loop won't be executed at all! This all seems very slick, but there's actually a better way. Instead
of using the count function, we can simply keep going
until we reach a value of $i for which $cat[$i] is
empty. When we do, we know we'll have reached the end of the list of category
IDs: This will run a little faster because we don't call a function each
time through the loop. Plus, it's a teeny bit more clever, and we programmers
have to have our fun when we can! Believe it or not, however, PHP spoils our
fun by having a completely separate type of loop that's specialized for looping
through arrays, called a foreach loop. Here's what the code looks like in this case: This foreach loop will execute the code inside the
loop once for each item in the $cats array
(you see where the foreach loop gets its name), and will
assign the item for each loop to the variable $catID. Since
this code is indisputably tidier than the equivalent while loop,
we'll settle on this as a solution. All that remains is to determine what
to do for each selected category ID.
Before we became sidetracked by all these different types of loops,
we were about to take our array of category IDs and use it to place our newly-inserted
joke into its corresponding categories. A cursory examination of our database
layout reveals that we just have to insert an entry into the JokeLookup table
for each category of which that joke should be a member. Recall that each
entry in the JokeLookup table consists
of a joke ID (JID) and a category ID (CID), which together indicate that a particular joke
belongs to a particular category. Here's the finished foreach loop: The word IGNORE in the INSERT query
used here is a precaution only. Recall that when we defined the JokeLookup table we set the JID and CID columns to be the primary key for the table.
If somehow the JID/CID pair
that is inserted already exists in the table, an attempt to insert it again
would normally cause an error. By adding IGNORE to the
command, a re-insert of the same pair is simply ignored by MySQL and no error
occurs. This situation should never actually happen, but it's better to be
safe than sorry. The two files that remain, editjoke.php and deletejoke.php,
mirror their author and category counterparts, with minor adjustments. editjoke.php must
provide the same author select box and category check boxes as ,
except that this time they must be initialized to reflect those values stored
in the database for the particular joke we've selected. deletejoke.php,
meanwhile, must not only delete the selected joke from the Jokes table,
but must also remove any entries in the JokeLookup table
for that joke. The code for both of these files is provided in the code archive,
but we won't spend time examining its details, since these files are just
an application of skills that should be fairly familiar to you by now. [1]Other special character codes include \r (carriage
return) and \t (tab). Like variables, these codes only work inside double-quoted strings. [2]In fact, the “do
nothing” WHERE clause could just be ' WHERE
1', since MySQL considers any positive number true. Feel free to
change it if you don't find the idea confusing. [3]Another way to submit an array is with a <select
multiple> tag. Again, you would set the name attribute
to end with square brackets. What will be submitted is an array of all the <option> values
selected from the list by the user. Feel free to experiment with this approach
by modifying to present the categories in
a list.<!-- jokelist.php -->
<l>
<head>
<title> Manage Jokes </title>
</head>
<body>
<h1>Manage Jokes</h1>
<p><a href=">New Search</a></p>
<?php
$dbcnx = mysql_connect('localhost', 'root', 'mypasswd');
mysql_select_db('jokes');
// The basic SELECT statement
$select = 'SELECT DISTINCT ID, JokeText';
$from = ' FROM Jokes';
$where = ' WHERE 1=1';
$aid = $_POST['aid'];
if ($aid != '') { // An author is selected
$where .= " AND AID='$aid'";
}
$cid = $_POST['cid'];
if ($cid != '') { // A category is selected
$from .= ', JokeLookup';
$where .= " AND ID=JID AND CID='$cid'";
}
$searchtext = $_POST['searchtext'];
if ($searchtext != '') { // Some search text was specified
$where .= " AND JokeText LIKE '%$searchtext%'";
}
?>
<table border="1">
<tr><th>Joke Text</th><th>Options</th></tr>
<?php
$jokes = @mysql_query($select . $from . $where);
if (!$jokes) {
echo('</table>');
die('<p>Error retrieving jokes from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}
while ($joke = mysql_fetch_array($jokes)) {
echo("<tr valign='top'>\n");
$id = $joke['ID'];
$joketext =lspecialchars($joke['JokeText']);
echo("<td>$joketext</td>\n");
echo("<td>[<a href=''>Edit</a>|".
"<a href=''>Delete</a>]</td>\n");
echo("</tr>\n");
}
?>
</table>
</body>
<l>
Adding Jokes
<?php
else: // Allow the user to enter a new joke
$authors = mysql_query('SELECT ID, Name FROM Authors');
$cats = mysql_query('SELECT ID, Name FROM Categories');
?>
<form action="'PHP_SELF']?>" method="post">
<p>Enter the new joke:<br />
<textarea name="joketext" rows="15" cols="45" wrap>
</textarea></p>
<p>Author:
<select name="aid" size="1">
<option selected value=">Select One</option>
<option value=">---------</option>
<?php
while ($author = mysql_fetch_array($authors)) {
$aid = $author['ID'];
$aname =lspecialchars($author['Name']);
echo("<option value='$aid'>$aname</option>\n");
}
?>
</select></p>
<p>Place in categories:<br />
<?php
while ($cat = mysql_fetch_array($cats)) {
$cid = $cat['ID'];
$cname =lspecialchars($cat['Name']);
echo("<input type='checkbox' name='cats[]' value='$cid' />" .
"$cname<br />\n");
}
?>
</p>
<p><input type="submit" name="submit" value="SUBMIT" /></p>
</form>
<?php endif; ?>
<?php
$dbcnx = mysql_connect('localhost', 'root', 'mypasswd');
mysql_select_db('jokes');
if (isset($_POST['submit'])):
// A new joke has been entered
// using the form.
$aid = $_POST['aid'];
$joketext = $_POST['joketext'];
$cats = $_POST['cats'];
if ($aid == '') {
die('<p>You must choose an author ' .
'for this joke. Click "Back" ' .
'and try again.</p>');
}
$sql = "INSERT INTO Jokes SET
JokeText='$joketext',
JokeDate=CURDATE(),
AID='$aid'";
if (@mysql_query($sql)) {
echo('<p>New joke added</p>');
} else {
echo('<p>Error adding new joke: ' .
mysql_error() . '</p>');
}
$jid = mysql_insert_id();
if ($cats == '') $cats = array();
$i = 0; // First index
while ($i < count($cats)) { // While we're not at the end
// process $cats[$i]
$i = $i + 1;
}
$i = 0; // First index
while ($cats[$i] != ") { // While we're not at the end
// process $cats[$i]
$i = $i + 1;
}
foreach ($cats as $catID) {
// Process $catID
}
$numCats = 0;
foreach ($cats as $catID) {
$sql = "INSERT IGNORE INTO JokeLookup
SET JID=$jid, CID=$catID";
$ok = @mysql_query($sql);
if ($ok) {
$numCats = $numCats + 1;
} else {
echo("<p>Error inserting joke into category $catID: " .
mysql_error() . '</p>');
}
}
?>
<p>Joke was added to <?=$numCats?> categories.</p>
<p><a href="'PHP_SELF']?>">Add another Joke</a></p>
<p><a href=">Return to Joke Search</a></p>
Editing and Deleting Jokes