81

Chapter 4: Building the Database
Using PHP scripts
Because this book is about PHP and MySQL, the focus is on accessing MySQL
databases from PHP scripts. PHP and MySQL work well together. PHP pro-
vides built-in functions to interact with MySQL. You don’t need to know the
details of interacting with the database because the functions handle all the
details. You just need to know how to use the functions.
PHP functions connect to the MySQL server, select the correct database,
send a query, and receive any data that the query retrieves from the data-
base. I explain using PHP functions to interact with your MySQL database in
detail in Chapter 8.
3. If you’re starting the mysql client to access a database across the network, use the follow-
ing parameter after the mysql command:
-h host: host is the name of the machine where MySQL is located.
For instance, if you’re in the directory where the mysql client is located, the command might
look like this:
mysql -h mysqlhost.mycompany.com -u root -p
Press Enter after typing the command.
4. Enter your password when prompted for it.
The mysql client starts, and you see something similar to this:
Welcome to the MySQL monitor. Commands end with ; or
\g.
Your MySQL connection id is 459 to server version:
5.0.15
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the
buffer.
mysql>
5. Select the database that you want to use.
At the mysql prompt, type the following:

1. Open the phpMyAdmin main page in a browser.
The phpMyAdmin page opens. (Refer to Figure 4-1.)
2. Scroll down to the Create New Database heading.
The heading is located in the left column of the main panel.
3. Type the name of the database you want to create into the blank field.
4. Click Create.
When you create the new database, a new phpMyAdmin page is displayed, as
shown in Figure 4-3.
Notice that the new database name — Customer — is now shown in the left
pane. Customer is the named I typed in the field to name the new database.
The 0 after the database name means that there are, as yet, no tables in the
database.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
83

Chapter 4: Building the Database

Figure 4-3:
The
phpMyAdmin
new data-
base page.

In the main panel, the following is displayed
Database Customer has been created
Showing that the database was successfully created. It also shows the SQL
query that phpMyAdmin sent to create the database, which was:
CREATE DATABASE ‘Customer’
Below the SQL statement, the page shows that no tables have been created
and provides a section where you can proceed to create tables. I discuss cre-

2. Find and click the icon for MySQL databases.
In cPanel, the icon is located in the section labeled Databases. The icon
says MySQL Databases.
A page opens so that you can create a new database, shown in Figure 4-4.
The page lists your current databases, if you have any.
3. Type the name of the database you want to create into the blank field
labeled New Database.
4. Click the Create Database button.
A page displays informing you that the database was created success-
fully. From this page, you can go back to the control panel and then to
phpMyAdmin. You can see the new database listed on the phpMyAdmin
main page, in the left pane.

Figure 4-4:
The page
where you
create a
new data-
base.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
85

Chapter 4: Building the Database
Viewing the databases
You can see a list of the names of your current databases at any time by
opening the main phpMyAdmin page. The names are shown in the left pane
of the page. The list includes a number after the database name. This number
represents the number of tables currently defined for the database.
The SQL query that displays a list of database names is

To delete a database on your Web hosting account, you use a specific pro-
cedure provided by the Web hosting company. For example, in cPanel, you
use the same page that you used to create the database. As shown earlier
in Figure 4-4, the page lists all your existing databases in a table. The table
includes a column named Actions with a link for each database to Delete
Database. Click the Delete Database link to remove the database. However,
remember, after you delete the database, it’s gone forever.
Adding tables to a database
You can add tables to any database, whether it’s a new, empty database that
you just created or an existing database that already has tables and data in it.
In most cases, you create the tables in the database before the PHP script(s)
access the database. Therefore, in most cases, you use phpMyAdmin to add
the tables.
In the sample database designs that I introduce in Chapter 3, the
PetCatalog database is designed with three tables: Pet, PetType, and
PetColor. The MemberDirectory database is designed with two tables:
Member and Login.
The definition of the table, Pet, is shown in Table 4-1. The table shows a list
of the column names and data types. It also specifies which column is the pri-
mary key for the table.
Table 4-1 PetCatalog Database Table 1: Pet
Column Name Type Description
petID SERIAL
Sequence number for pet
(primary key)
petName VARCHAR(25)
Name of pet
petType VARCHAR(15)
Category of pet
petDescription VARCHAR(255)

base or states that no tables are found in the database. The page also
displays a section labeled Create New Table on database. The section
contains a field labeled Name.
3. Type the name of the table into the field.
4. Type the number of fields you want in the table into the field labeled
Number of fields.
Don’t worry about making a mistake. Nothing is set in stone. You can
change the table structure easily if you need to.
For example, for the Pet table defined in Table 4-1, you type 6 into the
field because the table contains six fields: petID, petName, petType,
petDescription, price, and pix.
5. Click Go.
The page that opens allows you to define each column, or field. The
page provides a table, which is quite wide, where you can define the
fields. Figure 4-6 shows the left half of the page, and Figure 4-7 shows
the right half.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
88
Part II: MySQL Database
6. Enter the definitions for all the fields.
Figure 4-6 shows the left side of the table definition with its cells filled in.
Type the field name in the first column.
In the second column, select the data type from a drop-down list. The
data type for the first field is SERIAL. If you don’t find SERIAL in the
drop-down list, select BIGINT for the field.
In the third column, type the length or values for the field. For instance,
for VARCHAR data types, enter the number of characters, such as 15.

Figure 4-6:
The table

You can view the tables in a database and their structure any time by going
to the database page. That is, you can open the main phpMyAdmin page and
click the name of the database. The page that opens lists the tables currently
in the database.
Each table is displayed in a row, beginning with the table name. Next, the
row shows several icons. The second icon is the structure icon. If you click
this icon, the structure of the table is displayed, showing the field names and
definitions.
Another icon shown in the listing for the table is a large red X. If you click this
icon, the table is dropped, removed completely.
Writing an SQL query
You can also create a table by writing your own SQL query and sending it to
the MySQL server. In some cases, it’s faster to just write the query.
The CREATE TABLE query creates a new table. The name is followed by the
names and definitions of all the fields, separated by commas, with parenthe-
ses around the entire set of definitions. For instance, the query you would
use to create the Pet table is
CREATE TABLE Pet (
petID SERIAL,
petName VARCHAR(25) NOT NULL,
petType VARCHAR(15) NOT NULL,
petDescription VARCHAR(255) NOT NULL,
price DECIMAL(9,2) NULL,
pix VARCHAR(15) DEFAULT “missing.jpg”,
)
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
90
Part II: MySQL Database
You can also define the first field using the following:
PetID BIGINT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY

add, drop, or rename a column; or change the data type or other attributes of
the column. You can change the structure even after the table contains data,
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
91

Chapter 4: Building the Database
as long as you do not change the definition of a field to a definition that’s
incompatible with the data currently in the column.
Changing a database is not a rare occurrence. You might want to change
your database for many reasons. For example, suppose that you defined
the column lastName with VARCHAR(20) in the Member table of the
MemberDirectory database. At the time, 20 characters seemed sufficient
for a last name. But now you just received a memo announcing the new CEO,
John Schwartzheimer-Losertman. Oops. MySQL will truncate his name to the
first 20 letters, a less-than-desirable new name for the boss. So you need to
make the column wider — pronto.
Using phpMyAdmin
To change the structure in phpMyAdmin, follow these steps:
1. Open the main phpMyAdmin page.
2. Click the name of the database that contains the table to be modified.
A page opens listing the tables that are in the database. Each table is
listed in a separate row on the page.
3. In the row for the table to be modified, click the second icon (the
structure icon).
The page that opens shows the structure of the table. Each field is listed
in a row on the page.
4. Click the pencil icon for the field you want to modify.
The pencil icon is in a column named Action, which contains several
icons. The pencil icon is the second icon.
A page opens where you can change any definition for the field. In

renames the column; definition
includes the data type and optional
definitions.
DROP columnname
Deletes a column, including all the data in
the column. The data cannot be recovered.
MODIFY columnname
definition
Changes the definition of a column;
definition includes the data type and
optional definitions.
RENAME newtablename
Renames a table.
You can make the lastName field wider by sending this query to change the
column in a second:
ALTER TABLE Member MODIFY lastName VARCHAR(50)
Moving Data Into and
Out of the Database
An empty database is like an empty cookie jar — it’s not much fun. And
searching an empty database is no more interesting or fruitful than searching
an empty cookie jar. A database is useful only with respect to the information
that it holds.
A database needs to be able to receive information for storage and to deliver
information on request. For instance, the MemberDirectory database needs
to be able to receive the member information, and it also needs to be able to
deliver its stored information when you request it. If you want to know the
address of a particular member, for example, the database needs to deliver
that information when you request it.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
93

member. Now you’re looking at more than 400 hours of typing — who has
time for that?
If you have a large amount of data to enter, consider some alternatives.
Sometimes scanning in the data is an option. Or perhaps you need to beg,
borrow, or hire some help. In many cases, it could be faster to enter the data
into a big text file than to enter each row manually.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
94
Part II: MySQL Database
With phpMyAdmin, you can read data from a big text file (or even a small
text file). So, if your data is already in a computer file, you can work with that
file; you don’t need to retype all the data. Even if the data is in a format other
than a text file (for example, in an Excel, Access, or Oracle file), you can usu-
ally convert the file to a big text file, which can then be read into your MySQL
database. If the data isn’t yet in a computer file and there’s a lot of data, it
might be faster to enter that data into the computer in a big text file and
transfer it into MySQL as a second step.
Most text files can be read into MySQL, but some formats are easier than
others. If you’re planning to enter the data into a big text file, read the
“Adding a bunch of data” section to find the best format. Of course, if the
data is already on the computer, you have to work with the file as it is.
Adding one row at a time with an SQL query
It’s common to want your PHP script to store data in your database. For
instance, when you sell a product, the customer enters her name, address,
product she wants to buy, and other information into forms on the Web page.
Your PHP script needs to add this data to your database. You use an SQL
query in the script to add the data to the database.
You use the INSERT query to add a row to a database. This query tells
MySQL which table to add the row to and what the values are for the fields in
the row. The general form of the query is

default, the default value would be entered, but because it doesn’t, the field is
left empty. Notice that the value stored for fax is an empty string.
Adding one row at a time with phpMyAdmin
Many Web database applications include a database of information that you
display on the Web page. For instance, a product catalog contains product
information that the application displays when the customer wants to view
it. In this type of application, you add the information to the database outside
the application. You can create the catalog using phpMyAdmin.
To add data to the database table using phpMyAdmin, follow these steps:
1. Open the main phpMyAdmin page.
Figure 4-1, which appears earlier in the chapter, shows the main page.
2. Click a database name.
3. Click the insert icon.
In the action column, in the row for the table, the insert icon is the
fourth icon.
The page shown in Figure 4-8 opens where you can enter the data for
a row.

Figure 4-8:
phpMyAdmin
page where
you enter a
row.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
96
Part II: MySQL Database
4. Add your data to each row.
You enter the values in the column named Values. Notice that there
is also a column named Function, which contains a drop-down list of

to output the data into a delimited file. For a text file, you might be able to
convert it to delimited format by using the search-and-replace function of an
editor or word processor. For a truly troublesome file, you might need to seek
the help of an expert or a programmer.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
97

Chapter 4: Building the Database
To insert data into your database table with phpMyAdmin, follow these
steps:
1. Open the main phpMyAdmin page.
Figure 4-1, earlier in this chapter, shows the main page.
2. Click a database name.
3. Click the table name.
The table names are listed in the left pane of the page.
4. Click the Import tab at the top of the page.
The phpMyAdmin Import page opens, as shown in Figure 4-9.

Figure 4-9:
phpMyAdmin
import page
where you
can import a
file of data.

5. Click the Browse button.
6. Navigate to the file that contains the data to be imported.
7. Select the CSV or the CSV Using LOAD DATA option.
The CSV option imports each row using a separate INSERT statement
for each row. The CSV Using LOAD DATA option uses a LOAD DATA

FIELDS TERMINATED BY ‘,’ ESCAPED BY ‘\\’ LINES
TERMINATED BY ‘\r\n’
To use the LOAD DATA INFILE query, the MySQL account must have the
FILE privilege on the server host. I discuss the MySQL account privileges in
Chapter 5.
Viewing information
You can browse the data in a database table at any time. You may want to be
sure that the data you entered is correct. Or, you may want to see what type
of data customers are entering into the forms in your application.
To look at the information in a table, you can do this:
1. Open the main phpMyAdmin page.
Refer to Figure 4-1 to see the main page.
2. Click a database name.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
99

Chapter 4: Building the Database
A page opens that lists the tables currently in the database.
3. Click the browse icon.
In the action column, in the row for the table, the browse icon is the first
icon.
Retrieving information
The only purpose in storing information is to have it available when you need it.
A database lives to answer questions. What pets are for sale? Who are the mem-
bers? How many members live in Arkansas? Do you have an alligator for sale?
How much does a dragon cost? What is Goliath Smith’s phone number? And on
and on. Your application may need to display the answers to any one of these
questions. To query the database from your application, you use an SQL query.
You use the SELECT query to ask the database questions. The simplest, most
basic SELECT query is

For instance, the following query retrieves all the last names and first names
stored in the Member table:
SELECT lastName,firstName FROM Member
You can perform mathematical operations on columns when you select them.
For example, you can use the following SELECT query to add two columns:
SELECT col1+col2 FROM tablename
Or you could use the following query:
SELECT price,price*1.08 FROM Pet
The result is the price and the price with the sales tax of 8 percent added.
You can change the name of a column when selecting it, as follows:
SELECT price,price*1.08 AS priceWithTax FROM Pet
The AS clause tells MySQL to give the name priceWithTax to the second
column retrieved. Thus, the query retrieves two columns of data: price and
priceWithTax.
In some cases, you don’t want to see the values in a column, but you want to
know something about the column. For instance, you might want to know the
lowest value in the column or the highest value in the column. Table 4-3 lists
some of the information that is available about a column.
Table 4-3 Information That Can Be Selected
SQL Format Description of Information
AVG(columnname) Returns the average of all the values in columnname
COUNT(columnname) Returns the number of rows in which columnname
is not blank
MAX(columnname) Returns the largest value in columnname
MIN(columnname) Returns the smallest value in columnname
SUM(columnname) Returns the sum of all the values in columnname
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
101

Chapter 4: Building the Database

same value as petType:
SELECT * FROM Pet GROUP BY petType
You can use GROUP BY and ORDER BY in the same query.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
102
Part II: MySQL Database
Retrieving data from a specific source
Frequently, you don’t want all the information from a table. You want infor-
mation from selected database objects, that is, rows. Three SQL words are
frequently used to specify the source of the information:
✓ WHERE: Allows you to request information from database objects with
certain characteristics. For instance, you can request the names of mem-
bers who live in California, or you can list only pets that are cats.
✓ LIMIT: Allows you to limit the number of rows from which information
is retrieved. For instance, you can request all the information from the
first three rows in the table.
✓ DISTINCT: Allows you to request information from only one row of
identical rows. For instance, in the Login table, you can request
loginName but specify no duplicate names, thus limiting the response
to one record for each member. This would answer the question, “Has
the member ever logged in?” rather than the question “How many times
has the member logged in?”
The WHERE clause of the SELECT query enables you to make complicated
selections. For instance, suppose your boss asks for a list of all members
whose last names begin with B, who live in Santa Barbara, and who have an
8 in either their phone or fax number. I’m sure there are many uses for such
a list. You can get this list for your boss with a SELECT query by using a
WHERE clause.
The basic format of the WHERE clause is
WHERE expression AND|OR expression AND|OR expression

49999 or lower
column <= value zip <= “50000”
Selects only the rows
where the zip code is
50000 or lower
column BETWEEN
value1 AND
value2
zip BETWEEN
“20000” AND
“30000”
Selects only the rows
where the zip code is
greater than 19999 but
less 30001
column IN
(value1,value2,…)
zip IN
(“90001”,”30044”)
Selects only the rows
where the zip code is
90001 or 30044
column NOT IN
(value1,value2,…)
zip NOT IN
(“90001”,”30044”)
Selects only the rows
where the zip code is
any zip code except
90001 or 30044

WHERE lastName LIKE “B%”
AND city = “Santa Barbara”
AND (phone LIKE “%8%” OR fax LIKE “%8%”)
Notice the parentheses in the last line. You would not get the results that
your boss asked for without the parentheses. Without the parentheses, each
connector would be processed in order from the first to the last, resulting
in a list that includes all members whose names begin with B and who live
in Santa Barbara and whose phone numbers have an 8 in them and all mem-
bers whose fax numbers have an 8 in them, whether or not they live in Santa
Barbara and whether or not their name begins with a B. When the last OR is
processed, members are selected whose characteristics match the expres-
sion before the OR or the expression after the OR. The expression before the
OR is connected to previous expressions by the previous ANDs and so does
not stand alone, but the expression after the OR does stand alone, resulting in
the selection of all members with an 8 in their fax number.
LIMIT specifies how many rows can be returned. The form for LIMIT is
LIMIT startnumber,numberofrows
The first row that you want to retrieve is startnumber, and the number of
rows to retrieve is numberofrows. If startnumber is not specified, 1
is assumed. To select only the first three members who live in Texas, use
this query:
SELECT * FROM Member WHERE state=”TX” LIMIT 3
Some SELECT queries will find identical records, but in this example, you want
to see only one — not all — of the identical records. To prevent the query
from returning all identical records, add the word DISTINCT immediately
after SELECT.
Combining information from tables
In previous sections of this chapter, I assume that all the information you
want is in a single table. However, you might want to combine information
from different tables. You can do this easily in a single query.

club and a separate table of current members. You can get a list of all mem-
bers, both current and resigned, with the following query:
SELECT lastName,firstName FROM Member UNION ALL
SELECT lastName,firstName FROM OldMember
The result of this query is the last and first names of all current members, fol-
lowed by the last and first names of all the members who have resigned.
Depending on how you organized your data, you might have duplicate names.
For instance, perhaps a member resigned, and his name is in the OldMember
table — but he joined again, so his name is added to the Member table. If you
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.