Chapter 5: Form Elements: Letting the User Work with Data
151
How It Works
This set of scripts is designed around a simple idea: passing data through multiple scripts from form
to form. The key to this has been
input elements with their type attribute set to hidden . These fields
are not displayed by the browser to the user, but their values are submitted with the rest of the form
fields ’ data. This is but one way to pass data between forms, though it is very common.
Summary
You ’ ve learned a lot of about forms in this chapter. Forms are composed of fields. Each field type has a
specific purpose and allows a certain data type to be entered. Text fields can be used to enter text or
numeric data. Lists can be used to enter any type of data and have a limited set of possible values. Lists
are a good way to drive user input when multiple choices are available. Check boxes are good for true or
false values.
Figure 5-9
c05.indd 151c05.indd 151 12/10/08 5:45:44 PM12/10/08 5:45:44 PM
Part I: Movie Review Web Site
152
Exercises
See how you might accomplish the following:
1. Create a form and a processing page that let you choose a rating (stars, thumbs up, number from
1 to 5, whatever), and provide comments for a movie.
2. Create a form with several text input boxes that allow you to populate the options of a select
field on a subsequent page.
3. Create a calculator form that takes two numbers and calculates their sum.
c05.indd 152c05.indd 152 12/10/08 5:45:45 PM12/10/08 5:45:45 PM
6
Letting the User Edit
the Database
Retrieving data from a database is all well and good when you ’ ve first fed the database some data.
But databases don ’ t generate their own content, and only a few get fed data by other systems, such

as integrated systems. What this means is that you have to feed your system with data that comes
from PHP. For our purposes here, and from what you ’ ve seen in previous chapters, all interaction
with the database uses SQL. You already know the basic SQL syntax to put your own data in a
table and retrieve it for users to see. But now, let ’ s look at the other side of the equation — data
processing.
This chapter covers database editing, including:
Adding entries, which is quite simple — but you will find that adding entries in a
relational database is yet another exercise .
Deleting entries without corrupting the database structure and referential integrity .
Modifying entries to replace some existing fields with new content in an existing record .
Preparing the Battlefield
We ’ ll continue to use the moviesite database from the previous chapters here. First you ’ ll start by
creating the administrative page that lists the movies and people in your database and provides
links for you to manage them. Then you will create the auxiliary pages that will let you add and
delete movie records.
❑
❑
❑
c06.indd 153c06.indd 153 12/10/08 5:47:47 PM12/10/08 5:47:47 PM
154
Part I: Movie Review Web Site
Try It Out Setting Up the Environment
First, you need a start page. Follow these steps to create one:
1. Create a file named admin.php , and enter the following code:
< ?php
$db = mysql_connect(‘localhost’, ‘bp6am’, ‘bp6ampass’) or
die (‘Unable to connect. Check your connection parameters.’);
mysql_select_db(‘moviesite’, $db) or die(mysql_error($db));
? >
< html >

< head >
< title > Movie database < /title >
< style type=”text/css” >
th { background-color: #999;}
.odd_row { background-color: #EEE; }
.even_row { background-color: #FFF; }
< /style >
< /head >
< body >
< table style=”width:100%;” >
< tr >
< th colspan=”2” > Movies < a href=”movie.php?action=add” > [ADD] < /a > < /th >
< /tr >
< ?php
$query = ‘SELECT * FROM movie’;
$result = mysql_query($query, $db) or die (mysql_error($db));

$odd = true;
while ($row = mysql_fetch_assoc($result)) {
echo ($odd == true) ? ‘ < tr class=”odd_row” > ’ : ‘ < tr class=”even_row” > ’;
$odd = !$odd;
echo ‘ < td style=”width:75%;” > ’;
echo $row[‘movie_name’];
echo ‘ < /td > < td > ’;
echo ‘ < a href=”movie.php?action=edit & id=’ . $row[‘movie_id’] . ‘” >
[EDIT] <
/a > ’;
echo ‘ < a href=”delete.php?type=movie & id=’ . $row[‘movie_id’] . ‘” >
[DELETE] < /a > ’;
echo ‘ < /td > < /tr > ’;

}
? >
< tr >
< th colspan=”2” > People < a href=”people.php?action=add” > [ADD] < /a > < /th >
< /tr >
< ?php
$query = ‘SELECT * FROM people’;
$result = mysql_query($query, $db) or die (mysql_error($db));


c06.indd 154c06.indd 154 12/10/08 5:47:48 PM12/10/08 5:47:48 PM
Chapter 6: Letting the User Edit the Database
155
$odd = true;
while ($row = mysql_fetch_assoc($result)) {
echo ($odd == true) ? ‘ < tr class=”odd_row” > ’ : ‘ < tr class=”even_row” > ’;
$odd = !$odd;
echo ‘ < td style=”width: 25%;” > ’;
echo $row[‘people_fullname’];
echo ‘ < /td > < td > ’;
echo ‘ < a href=”people.php?action=edit & id=’ . $row[‘people_id’] .
‘” > [EDIT] < /a > ’;
echo ‘ < a href=”delete.php?type=people & id=’ . $row[‘people_id’] .
‘” > [DELETE] < /a > ’;
echo ‘ < /td > < /tr > ’;
}
? >
< /table >
< /body >
< /html >

2. Now open the file in your browser. You will see the page as shown in Figure 6 - 1 .
Figure 6-1
c06.indd 155c06.indd 155 12/10/08 5:47:48 PM12/10/08 5:47:48 PM
156
Part I: Movie Review Web Site
All links are broken at the moment, but do not worry; that ’ s perfectly normal, because you haven ’ t yet
created the other pages.
How It Works
You must always have a central administration interface that allows you to perform actions on the
data and easily see the content. This script provides that admin interface. It shows you all the data and
allows you to manage everything in sight. So how does it do it?
As in Chapter 4, here, you connect to the database and display its contents. The code generates an HTML

table that holds the name of each movie and person, along with ADD, EDIT, and DELETE links.
Odd and even rows of the table appear in different colors, as a visual cue that helps line up the entry
with the EDIT and DELETE links. Before the start of each
while loop that is responsible for listing the
results of the database query, the variable
$odd is set to true . How the tr tag is generated upon each
iteration depends on the value of
$odd , and then the value of $odd is toggled in preparation for the
next iteration of the loop.

Inserting a Record in a Relational Database
Databases often hold more than just one table. All those tables can be totally independent, but that
would be like using your car to store things in the trunk, but never to drive around in. Usually the tables
are related to one another in some manner.
In old systems in which relational databases didn ’ t exist, every row held all the information possible.
Imagine your system running with only one table holding all the information for your application. Your


movie table, for example, would store all the data about the actors and the directors and the movie
types. Each record would have all this information specified. Now suppose that one day you were to
decide that a movie category should be changed from “ action ” to “ adventure. ” You would then have to
go through all the records in the table records to change the movie type label. The possibility for
mistakes is exponentially greater as well!
This is not the case in modern relational database management systems (RDBMS); you can create a

movietype table storing a reference of all the possible movie types, and then link movies to the relevant
movie type. To link different tables, you use a primary key/foreign key pair.
A primary key is a value or set of values that can be used to uniquely identify each record in a table. The
primary key of the
movietype table is the numeric identification of each type of movie stored in the

movietype_id field. For example, in your database, the id 1 references comedy. The foreign key is a
value in another table that can be used to reference back to the primary key. The reference in the
movie
table is to the
movietype primary key.
In the following exercise, you use PHP and SQL to insert a movie in your database. This movie is of a
known movie type from the
movietype reference table.
c06.indd 156c06.indd 156 12/10/08 5:47:49 PM12/10/08 5:47:49 PM
Chapter 6: Letting the User Edit the Database
157
Try It Out Inserting a Movie with Known Movie Type and People
This time, let ’ s do something a bit more complicated. You ’ ll be able to add a movie to the system while
specifying an existing movie type and existing actor and director.
1. Create a new empty file with your text editor, and enter the following code. Save it as movie.php .
< ?php
$db = mysql_connect(‘localhost’, ‘bp6am’, ‘bp6ampass’) or

die (‘Unable to connect. Check your connection parameters.’);
mysql_select_db(‘moviesite’, $db) or die(mysql_error($db));
? >
< html >
< head >
< title > Add Movie < /title >
< /head >
< body >
< form action=”commit.php?action=add & type=movie” method=”post” >
< table >
< tr >
< td > Movie Name < /td >
< td > < input type=”text” name=”movie_name”/ > < /td >
< /tr > < tr >
< td > Movie Type < /td >
< td > < select name=”movie_type” >
< ?php
// select the movie type information
$query = ‘SELECT
movietype_id, movietype_label
FROM
movietype
ORDER BY
movietype_label’;
$result = mysql_query($query, $db) or die(mysql_error($db));

// populate the select options with the results
while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $value) {
echo ‘ < option value=”’ . $row[‘movietype_id’] . ‘” > ’;

echo $row[‘movietype_label’] . ‘ < /option > ’;
}
}
? >
< /select > < /td >
< /tr > < tr >
< td > Movie Year < /td >
< td > < select name=”movie_year” >
c06.indd 157c06.indd 157 12/10/08 5:47:49 PM12/10/08 5:47:49 PM
158
Part I: Movie Review Web Site
< ?php
// populate the select options with years
for ($yr = date(“Y”); $yr > = 1970; $yr ) {
echo ‘ < option value=”’ . $yr . ‘” > ’ . $yr . ‘ < /option > ’;
}
? >
< /select > < /td >
< /tr > < tr >
< td > Lead Actor < /td >
< td > < select name=”movie_leadactor” >
< ?php
// select actor records
$query = ‘SELECT
people_id, people_fullname
FROM
people
WHERE
people_isactor = 1
ORDER BY

people_fullname’;
$result = mysql_query($query, $db) or die(mysql_error($db));

// populate the select options with the results
while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $value) {
echo ‘ < option value=”’ . $row[‘people_id’] . ‘” > ’;
echo $row[‘people_fullname’] . ‘ < /option > ’;
}
}
? >
< /select > < /td >
< /tr > < tr >
< td > Director < /td >
< td > < select name=”movie_director” >
< ?php
// select director records
$query = ‘SELECT
people_id, people_fullname
FROM
people
WHERE
people_isdirector = 1
ORDER BY
people_fullname’;
$result = mysql_query($query, $db) or die(mysql_error($db));

// populate the select options with the results
while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $value) {

echo ‘ < option value=”’ . $row[‘people_id’] . ‘” >
’;
c06.indd 158c06.indd 158 12/10/08 5:47:49 PM12/10/08 5:47:49 PM
Chapter 6: Letting the User Edit the Database
159
echo $row[‘people_fullname’] . ‘ < /option > ’;
}
}
? >
< /select > < /td >
< /tr > < tr >
< td colspan=”2” style=”text-align: center;” >
< input type=”submit” name=”submit” value=”Add” / >
< /td >
< /tr >
< /table >
< /form >
< /body >
< /html >
2. Create a new empty file named commit.php , and enter the following code:
< ?php
$db = mysql_connect(‘localhost’, ‘bp6am’, ‘bp6ampass’) or
die (‘Unable to connect. Check your connection parameters.’);
mysql_select_db(‘moviesite’, $db) or die(mysql_error($db));
? >
< html >
< head >
< title > Commit < /title >
< /head >
< body >

< ?php
switch ($_GET[‘action’]) {
case ‘add’:
switch ($_GET[‘type’]) {
case ‘movie’:
$query = ‘INSERT INTO
movie
(movie_name, movie_year, movie_type, movie_leadactor,
movie_director)
VALUES
(“’ . $_POST[‘movie_name’] . ‘”,
‘ . $_POST[‘movie_year’] . ‘,
‘ . $_POST[‘movie_type’] . ‘,
‘ . $_POST[‘movie_leadactor’] . ‘,
‘ . $_POST[‘movie_director’] . ‘)’;
break;
}
break;
c06.indd 159c06.indd 159 12/10/08 5:47:50 PM12/10/08 5:47:50 PM
160
Part I: Movie Review Web Site
}

if (isset($query)) {
$result = mysql_query($query, $db) or die(mysql_error($db));
}
? >
< p > Done! < /p >
< /body >
< /html >

3. Open your browser on the admin.php page, and click the ADD link in the movie table ’ s
header. You should see on the screen the form in which you can enter movie information.
4. Add a movie named “ Test ” with a random movie type, actor, and director, as shown in Figure 6 - 2 .
Figure 6-2
5. Click the Add button, and you will see the confirmation message shown in Figure 6 - 3 .
c06.indd 160c06.indd 160 12/10/08 5:47:50 PM12/10/08 5:47:50 PM
Chapter 6: Letting the User Edit the Database
161
How It Works
HTML forms allow you to drive the way users enter the data. Once submitted, the form sends the
server information that PHP can use to generate and run the
SQL INSERT statement.
As you see in the movie insertion form in
movie.php , you have four select boxes and a text field. The
text field content is left to your discretion, but the select boxes are quite directive and only allow you
to chose a predetermined value.
To generate the list of movie types, you simply query the database, retrieve the records and display
the types, and reference their primary key as the item value. Each known movie type will have an
item in the select box.
Back in Chapter 2, you were introduced to the
while loop and the foreach loop. The year list is a
selection of years from 1970 to the current year, and is generated with a new type of loop, the
for
loop.
When using these types of loops, you should know that
for loops will offer a more restricted way of
setting up code for repetitive execution. They begin with the keyword
for and then a set of three
statements: first a variable set to an initial value to be used as the loop ’ s counter, then a conditional
Figure 6-3

c06.indd 161c06.indd 161 12/10/08 5:47:51 PM12/10/08 5:47:51 PM
162
Part I: Movie Review Web Site
statement, and finally the increment in which the counter value is adjusted after each execution of the
loop. The code block that gets executed is bracketed and follows the
for statement, following this
syntax:

for (initialize; condition; increment) {
// code
}

The initial value of $yr is set to the current year with the help of the date() function. The condition is
set so the loop will continue executing as long as the value
$yr is greater than or equal to 1970. Each
time the loop executes, the value of
$yr is reduced by 1. The code that is repeatedly executed uses the
value of
$yr to place the years into the select ’ s items.

for loops are more restricted than the other loops, in that the variable is intended only to act as a
counter mechanism. It wouldn ’ t be good practice to change its value within the code block! This is
compared to the
do - while and while loops, where the value is intentionally changed in the code
block to affect the loop ’ s behavior.
The same steps followed to generate the movie type listing are followed for the actor and director
select fields. A query is sent to the database, the results are retrieved, and the person is displayed with
the primary key as the item value. The only difference between the queries is the
WHERE clause that
filters the retrieved results to first just the actors and then just the directors.

Now that your form is ready, you need to have a script that uses this data to create records. This is the
purpose of
commit.php . As you can see, the switch statement using the $_GET[‘action’] value is
totally useless for now, but in the next exercises you will add a lot of code to the
movie.php script so
you can use it to edit the movies. Then
commit.php ’ s switch will be more important.
Deleting a Record
Deleting records is easy (perhaps a bit too easy at times). Deleting always means losing data, so be
especially careful when doing so. To delete a record you need to point to the record through a set of
conditions in a
WHERE statement. Once this statement is executed, there is no turning back. Records are
deleted without hope of return; that ’ s why we advise caution when using the
DELETE statement. MySQL
deletes everything that matches the query, and forgetting one little thing in your
WHERE clause could
have disastrous consequences.
Because deleting records is irrevocable, you may find it beneficial to make sure your
WHERE clause causes
the correct records to be selected first. You can use MySQL ’ s command - line program, first discussed in
Chapter 1, to issue a
SELECT statement and then review the result set that is returned. Then, when you
are certain the correct records are selected, you can prepare your
DELETE statement.
c06.indd 162c06.indd 162 12/10/08 5:47:51 PM12/10/08 5:47:51 PM
Chapter 6: Letting the User Edit the Database
163
Try It Out Deleting a Single Record
Before asking PHP to delete anything though MySQL, let ’ s first try it ourselves to familiarize
ourselves with the

DELETE statement.
1. Open a console window and connect to the MySQL server with the command - line program,
as in Chapter 1:

“C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe” -u root -p
2. Select the movies database by entering the following:
USE movies;
3. Test the WHERE clause using a SELECT statement:
SELECT * FROM movie WHERE movie_id = 4;
4. Verify the WHERE clause is correct by examining the returned results to see that it is indeed the
record you wish to delete.
5. Delete the record by using a DELETE statement:
DELETE FROM movie WHERE movie_id = 4;
6. See that the record was deleted by reissuing the SELECT statement:
SELECT * FROM movie WHERE movie_id = 4;
How It Works
The DELETE SQL statement is very simple to use. The most important aspect is to make sure you have
the proper selection of records with the
WHERE clause in your query.
As you know, a database often holds related records in different tables. Deleting some records without
considering their relations introduces you to chaos and heavy manual database tweaking. MySQL
unfortunately doesn ’ t manage relations for you, and thus will not automatically preserve referential
integrity.
To avoid that problem, you can use a more elaborate form of the
DELETE statement, the Cascade Delete ,
as discussed in the following section.

c06.indd 163c06.indd 163 12/10/08 5:47:51 PM12/10/08 5:47:51 PM
164
Part I: Movie Review Web Site

Try It Out Cascade Delete
Now that you know how to use DELETE , you will implement it to delete a known person from your
application ’ s database. Because you store references to known people in the
movie table, you will
need to update the
movie table content so you don ’ t reference deleted people. (The update - specific
exercises come next in this chapter.) Deleting the person only would be like throwing away your car
keys and expecting your parking spot to be empty. You need to make sure no reference to a deleted
record is left in the remaining data.
Follow these steps to implement the Cascade Delete:
1. Create a new text file named delete.php , and enter the following code:
< ?php
$db = mysql_connect(‘localhost’, ‘bp6am’, ‘bp6ampass’) or
die (‘Unable to connect. Check your connection parameters.’);
mysql_select_db(‘moviesite’, $db) or die(mysql_error($db));

if (!isset($_GET[‘do’]) || $_GET[‘do’] != 1) {
switch ($_GET[‘type’]) {
case ‘movie’:
echo ‘Are you sure you want to delete this movie? < br/ > ’;
break;
case ‘people’:
echo ‘Are you sure you want to delete this person? < br/ > ’;
break;
}
echo ‘ < a href=”’ . $_SERVER[‘REQUEST_URI’] . ‘ & do=1” > yes < /a > ‘;
echo ‘or < a href=”admin.php” > no < /a > ’;
} else {
switch ($_GET[‘type’]) {
case ‘people’:

$query = ‘UPDATE movie SET
movie_leadactor = 0
WHERE
movie_leadactor = ‘ . $_GET[‘id’];
$result = mysql_query($query, $db) or die(mysql_error($db));

$query = ‘DELETE FROM people
WHERE
people_id = ‘ . $_GET[‘id’];
$result = mysql_query($query, $db) or die(mysql_error($db));
? >
< p style=”text-align: center;” > Your person has been deleted.
< a href=”movie_index.php” > Return to Index < /a > < /p >
< ?php
break;
case ‘movie’:
$query = ‘DELETE FROM movie
WHERE
movie_id = ‘ . $_GET[‘id’];
$result = mysql_query($query, $db) or die(mysql_error($db));
? >
< p style=”text-align: center;” > Your movie has been deleted.
c06.indd 164c06.indd 164 12/10/08 5:47:51 PM12/10/08 5:47:51 PM
Chapter 6: Letting the User Edit the Database
165
< a href=”movie_index.php” > Return to Index < /a > < /p >
< ?php
break;
}
}

? >
2. Open admin.php in your browser again, and note the DELETE links next to each movie or
person.
3. Try deleting the test movie you added in the previous exercise by clicking the DELETE link
next to its name. You will be asked for confirmation, as in Figure 6 - 4 .
Figure 6-4
4. Click the “ yes ” link to confirm the deletion, and wait for the confirmation message, shown in
Figure 6 - 5 .
c06.indd 165c06.indd 165 12/10/08 5:47:52 PM12/10/08 5:47:52 PM
166
Part I: Movie Review Web Site
How It Works
First, you need to understand that in a relational database you cannot delete records and just forget
about them. Deleting data must be considered carefully. For example, if you delete a person from the

people table, this prevents you from finding a potential reference to that person in the movie table. If
you delete Jim Carrey from the
people table, who will Bruce Almighty ’ s lead actor be? If you don ’ t do
anything, Jim Carrey ’ s id will remain in the record, and you will have a corrupted database. You don ’ t
want that, do you? The answer is: Certainly not! The solution to this problem is to make sure that you
always have the round peg (a foreign key) in the round hole (a record). The script runs in two modes,
with the help of an
if statement. First, when it is called by following the link from admin.php , the
query string is simply the record type (either a movie or person) and the record ’ s id. A parameter named

do does not appear. This triggers the first block of code of the if construct to be executed by PHP:
switch ($_GET[‘type’]) {
case ‘movie’:
echo ‘Are you sure you want to delete this movie? < br/ > ’;
break;

case ‘people’:
echo ‘Are you sure you want to delete this person? < br/ > ’;
break;
}
echo ‘ < a href=”’ . $_SERVER[‘REQUEST_URI’] . ‘ & do=1” > yes < /a > ‘;
echo ‘or < a href=”admin.php” > no < /a > ’;

Figure 6-5
c06.indd 166c06.indd 166 12/10/08 5:47:52 PM12/10/08 5:47:52 PM
Chapter 6: Letting the User Edit the Database
167
A switch statement is used to display the correct prompt, depending on the type submitted, and
then links are generated to confirm the delete process or to cancel it. The link to confirm the process
directs you back to the current page with the same URL parameters, but also appends
do .
The second time the script is called, the
do parameter is set, and so the next block of the if structure is
executed, and again a
switch statement is used to act appropriately, depending on the type of record
to be deleted.
Because we want to preserve the integrity of the database, the
movie table must be updated first,
before deleting a record from the
person table:
$query = ‘UPDATE movie SET
movie_leadactor = 0
WHERE
movie_leadactor = ‘ . $_GET[‘id’];
$result = mysql_query($query, $db) or die(mysql_error($db));


$query = ‘DELETE FROM people
WHERE
people_id = ‘ . $_GET[‘id’];
$result = mysql_query($query, $db) or die(mysql_error($db));

The UPDATE statement works in a very simple way. It sets the fields specified to the new value specified
in all records, meeting the requirements of the
WHERE statement. You might wonder what would happen
if someone were to forget the
WHERE part. Well, curiosity is a fine quality: This would update all records
in the table, which is probably not something you want to do in real life.

Editing Data in a Record
Having data in the database is all well and good, but data has a mind of its own and tends to want to be
updated. To update data, you need to identify the data to update and present the system user with a nice
interface to do so. Using the same interface as was used to create the data is often a good practice.
Try It Out Editing a Movie
In this exercise, you create a script that enables you to edit a movie. You will build on the existing

movie.php script you created earlier.
1. Open movie.php in your text editor and modify the code as follows:
< ?php
$db = mysql_connect(‘localhost’, ‘bp6am’, ‘bp6ampass’) or
die (‘Unable to connect. Check your connection parameters.’);
mysql_select_db(‘moviesite’, $db) or die(mysql_error($db));

c06.indd 167c06.indd 167 12/10/08 5:47:52 PM12/10/08 5:47:52 PM
168
Part I: Movie Review Web Site
if ($_GET[‘action’] == ‘edit’) {

//retrieve the record’s information
$query = ‘SELECT
movie_name, movie_type, movie_year, movie_leadactor, movie_director
FROM
movie
WHERE
movie_id = ‘ . $_GET[‘id’];
$result = mysql_query($query, $db) or die(mysql_error($db));
extract(mysql_fetch_assoc($result));
} else {
//set values to blank
$movie_name = ‘’;
$movie_type = 0;
$movie_year = date(‘Y’);
$movie_leadactor = 0;
$movie_director = 0;
}
? >
< html >
< head >
< title > < ?php echo ucfirst($_GET[‘action’]); ? > Movie < /title >
< /head >
< body >
< form action=”commit.php?action= < ?php echo $_GET[‘action’]; ? > & type=movie”
method=”post” >
< table >
< tr >
< td > Movie Name < /td >
< td > < input type=”text” name=”movie_name”
value=” < ?php echo $movie_name; ? > ”/ > < /td >

< /tr > < tr >
< td > Movie Type < /td >
< td > < select name=”movie_type” >
< ?php
// select the movie type information
$query = ‘SELECT
movietype_id, movietype_label
FROM
movietype
ORDER BY
movietype_label’;
$result = mysql_query($query, $db) or die(mysql_error($db));

// populate the select options with the results
while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $value) {
if ($row[‘movietype_id’] == $movie_type) {
echo ‘ < option value=”’ . $row[‘movietype_id’] .
‘” selected=”selected” > ’;
} else {
echo ‘ < option value=”’ . $row[‘movietype_id’] . ‘” > ’;
}
echo $row[‘movietype_label’] . ‘ < /option > ’;
c06.indd 168c06.indd 168 12/10/08 5:47:53 PM12/10/08 5:47:53 PM
Chapter 6: Letting the User Edit the Database
169
}
}
? >
< /select > < /td >

< /tr > < tr >
< td > Movie Year < /td >
< td > < select name=”movie_year” >
< ?php
// populate the select options with years
for ($yr = date(“Y”); $yr > = 1970; $yr ) {
if ($yr == $movie_year) {
echo ‘ < option value=”’ . $yr . ‘” selected=”selected” > ’ . $yr .
‘ < /option > ’;
} else {
echo ‘ < option value=”’ . $yr . ‘” > ’ . $yr . ‘ < /option > ’;
}
}
? >
< /select > < /td >
< /tr > < tr >
< td > Lead Actor < /td >
< td > < select name=”movie_leadactor” >
< ?php
// select actor records
$query = ‘SELECT
people_id, people_fullname
FROM
people
WHERE
people_isactor = 1
ORDER BY
people_fullname’;
$result = mysql_query($query, $db) or die(mysql_error($db));


// populate the select options with the results
while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $value) {
if ($row[‘people_id’] == $movie_leadactor) {
echo ‘ < option value=”’ . $row[‘people_id’] .
‘” selected=”selected” > ’;
} else {
echo ‘ < option value=”’ . $row[‘people_id’] . ‘” > ’;
}
echo $row[‘people_fullname’] . ‘ < /option > ’;
}
}
? >
< /select > < /td >
< /tr > < tr >
< td > Director < /td >
< td > < select name=”movie_director” >
< ?php
// select director records
$query = ‘SELECT
c06.indd 169c06.indd 169 12/10/08 5:47:53 PM12/10/08 5:47:53 PM
170
Part I: Movie Review Web Site
people_id, people_fullname
FROM
people
WHERE
people_isdirector = 1
ORDER BY
people_fullname’;

$result = mysql_query($query, $db) or die(mysql_error($db));

// populate the select options with the results
while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $value) {
if ($row[‘people_id’] == $movie_director) {
echo ‘ < option value=”’ . $row[‘people_id’] .
‘” selected=”selected” > ’;
} else {
echo ‘ < option value=”’ . $row[‘people_id’] . ‘” > ’;
}
echo $row[‘people_fullname’] . ‘ < /option > ’;
}
}
? >
< /select > < /td >
< /tr > < tr >
< td colspan=”2” style=”text-align: center;” >
< ?php
if ($_GET[‘action’] == ‘edit’) {
echo ‘ < input type=”hidden” value=”’ . $_GET[‘id’] . ‘” name=”movie_id” / > ’;
}
? >
< input type=”submit” name=”submit”
value=” < ?php echo ucfirst($_GET[‘action’]); ? > ” / >
< /td >
< /tr >
< /table >
< /form >
< /body >

< /html >

2. Open the commit.php script and edit its content to match this new code:
< ?php
$db = mysql_connect(‘localhost’, ‘bp6am’, ‘bp6ampass’) or
die (‘Unable to connect. Check your connection parameters.’);
mysql_select_db(‘moviesite’, $db) or die(mysql_error($db));
? >
< html >
< head >
< title > Commit < /title >
< /head >
< body >
< ?php
switch ($_GET[‘action’]) {
c06.indd 170c06.indd 170 12/10/08 5:47:54 PM12/10/08 5:47:54 PM
Chapter 6: Letting the User Edit the Database
171
case ‘add’:
switch ($_GET[‘type’]) {
case ‘movie’:
$query = ‘INSERT INTO
movie
(movie_name, movie_year, movie_type, movie_leadactor,
movie_director)
VALUES
(“’ . $_POST[‘movie_name’] . ‘”,
‘ . $_POST[‘movie_year’] . ‘,
‘ . $_POST[‘movie_type’] . ‘,
‘ . $_POST[‘movie_leadactor’] . ‘,

‘ . $_POST[‘movie_director’] . ‘)’;
break;
}
break;
case ‘edit’:
switch ($_GET[‘type’]) {
case ‘movie’:
$query = ‘UPDATE movie SET
movie_name = “’ . $_POST[‘movie_name’] . ‘”,
movie_year = ‘ . $_POST[‘movie_year’] . ‘,
movie_type = ‘ . $_POST[‘movie_type’] . ‘,
movie_leadactor = ‘ . $_POST[‘movie_leadactor’] . ‘,
movie_director = ‘ . $_POST[‘movie_director’] . ‘
WHERE
movie_id = ‘ . $_POST[‘movie_id’];
break;
}
break;
}

if (isset($query)) {
$result = mysql_query($query, $db) or die(mysql_error($db));
}
? >
< p > Done! < /p >
< /body >
< /html >

3. Now open your browser and go to the admin.php page.
4. Try clicking the EDIT link next to the Bruce Almighty movie, change a few boxes and the movie

name, and press the Edit button in the form, as shown in Figure 6 - 6 .
c06.indd 171c06.indd 171 12/10/08 5:47:54 PM12/10/08 5:47:54 PM
172
Part I: Movie Review Web Site
5. Edit the Bruce Almighty entry again with the procedure in step 4, and fix it so it ’ s back to its own
old self.
Now the EDIT links for movies actually do something!
You see that the script loads the stored values and allows you to edit the data easily. Play around a bit,
and get a feel for the way it all works.
How It Works
The commit.php code is very much the same as what you saw already, with the exception of a new
branch of code in the
switch statement that handles updating the record in the database with the
incoming values. But there is an interesting twist in
movie.php , so let ’ s look at it a bit more closely.
First, look at the
if statement at the start of the script. You defined its branches on a query string
parameter named
action . If the action is edit , you query the database for a record corresponding to
the id specified in the
id query string parameter and set some variables. These variables are set to
void if
action is not edit .
Figure 6-6
c06.indd 172c06.indd 172 12/10/08 5:47:54 PM12/10/08 5:47:54 PM
Chapter 6: Letting the User Edit the Database
173
if ($_GET[‘action’] == ‘edit’) {
//retrieve the record’s information
$query = ‘SELECT

movie_name, movie_type, movie_year, movie_leadactor, movie_
director
FROM
movie
WHERE
movie_id = ‘ . $_GET[‘id’];

$result = mysql_query($query, $db) or die(mysql_error($db));
extract(mysql_fetch_assoc($result));
} else {
//set values to blank
$movie_name = ‘’;
$movie_type = 0;
$movie_year = date(‘Y’);
$movie_leadactor = 0;
$movie_director = 0;
}

The variables set in the preceding code are used to set the default value of the form fields. Each field
has a known value if you are editing a record, and has a blank value if you are creating a new record.

< td > < input type=”text” name=”movie_name”
value=” < ?php echo $movie_name; ? > ”/ > < /td >

In this example, the movie_name field takes the $movie_name variable ’ s content as its default value.
This allows you to reload the form with data from the record to edit it.
Editing a text field is pretty straightforward, but setting the value in a list is another story. You can ’ t
just display the list and hope the user will reset the value to the original when he or she edits the
record. You need to reload the whole list and make the previously set value appear as the default in
the list, so the user can just skip it if he or she doesn ’ t want to edit it.

How do you do this? The script holds the solution:

< td > Movie Type < /td >
< td > < select name=”movie_type” >
< ?php
// select the movie type information
$query = ‘SELECT
movietype_id, movietype_label
FROM
movietype
ORDER BY
movietype_label’;

$result = mysql_query($query, $db) or die(mysql_error($db));

// populate the select options with the results
while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $value) {
c06.indd 173c06.indd 173 12/10/08 5:47:54 PM12/10/08 5:47:54 PM
174
Part I: Movie Review Web Site
if ($row[‘movietype_id’] == $movie_type) {
echo ‘ < option value=”’ . $row[‘movietype_id’] .
‘” selected=”selected” > ’;
} else {
echo ‘ < option value=”’ . $row[‘movietype_id’] . ‘” > ’;
}
echo $row[‘movietype_label’] . ‘ < /option > ’;
}
}

? >
< /select > < /td >

You load the list as you would have done if adding a record, but you compare the current value in the

foreach iteration to the default value. If they are identical, add a simple selected= “ selected ” flag
to the option value. This sets the default list value to the current value in the table.
Summary
As you ’ ve learned in this chapter, there are three basic actions in modifying the content of a database:
Insert
Delete
Update
These actions are performed by the database itself through SQL queries that PHP executes on MySQL.
Read up on the SQL statements used in this chapter to get a good feel for how far they can take you, and
at what level you feel confident using these commands.
And finally, always remember that testing your query alone in MySQL ’ s command - line client or in a
program such as MySQL Query Browser can save you a lot of time debugging it before you incorporate
it into your PHP script.
Exercise
It may seem as if we ’ re about to take it easy on you, with only one exercise, but don ’ t be fooled! This
single exercise covers a lot of what we mentioned in this chapter.
1. Create the edit/delete code for the people table. Use the movie code as an example.
❑
❑
❑
c06.indd 174c06.indd 174 12/10/08 5:47:54 PM12/10/08 5:47:54 PM
7
Manipulating and Creating
Images with PHP
Now that you ’ ve been rocking and rolling with manipulating and displaying data using PHP, why

stop there? Did you know that PHP can also manipulate and create images on the fly? It can, with
a little help from the GD library. GD loosely stands for “ Graphics Draw, ” but the industry
generally refers to it in short as the GD library.
This chapter covers the following:
Enabling your PHP setup to use the GD library .
Allowing your users to upload their own images .
Retrieving information about an image, such as size or file type .
Creating a new image .
Copying an image or a portion of an image .
Creating thumbnails (smaller versions of images) .
Creating black - and - white versions of images .
Adding watermarks and captions to images .
Working with the GD Library
GD is written in C++ and allows for the manipulation of certain image types. Because PHP can ’ t
automatically process images with its core built - in functions, you need to make sure you have the
GD library and extension enabled. Fortunately, a bundled version comes with all recent versions of
PHP. While we recommend you use the bundled version included with PHP, if for some reason
you don ’ t have the bundled version, then you can find an external version of the library at

.
❑
❑
❑
❑
❑
❑
❑
❑
c07.indd 175c07.indd 175 12/10/08 6:01:08 PM12/10/08 6:01:08 PM