67
259
Altering Tables After Creation
Updating Records in the Database
In addition to retrieving data from the database,you often want to change it.For exam-
ple,you might want to increase the prices of books in the database.You can do this using
an
UPDATE
statement.
The usual form of an
UPDATE
statement is
UPDATE [LOW_PRIORITY] [IGNORE] tablename
SET column1=expression1,column2=expression2,...
[WHERE condition]
[ORDER BY order_criteria]
[LIMIT number]
The basic idea is to update the table called
tablename
,setting each of the columns
named to the appropriate expression.You can limit an
UPDATE
to particular rows with a
WHERE
clause and limit the total number of rows to affect with a
LIMIT
clause.
ORDER BY
is usually used only in conjunction with a
LIMIT
clause;for example,if you are going to
update only the first 10 rows,you want to put them in some kind of order first.
LOW_PRIORITY
and
IGNORE
,if specified,work the same way as they do in an
INSERT
statement.
Let’s look at some examples.If you want to increase all the book prices by 10%,you
can use an
UPDATE
statement without a
WHERE
clause:
update books
set price=price*1.1;
If,on the other hand,you want to change a single row—say,to update a customer’s
address—you can do it like this:
update customers
set address = ‘250 Olsens Road’
where customerid = 4;
Altering Tables After Creation
In addition to updating rows,you might want to alter the structure of the tables within
your database.For this purpose,you can use the flexible
ALTER TABLE
statement.The
basic form of this statement is
ALTER TABLE [IGNORE] tablename alteration [, alteration ...]
Note that in ANSI SQL you can make only one alteration per
ALTER TABLE
statement,
but MySQL allows you to make as many as you like. Each of the alteration clauses can
be used to change different aspects of the table.
If the
IGNORE
clause is specified and you are trying to make an alteration that causes
duplicate primary keys, the first one will go into the altered table and the rest will be
deleted.If it is not specified (the default), the alteration will fail and be rolled back.
73
260
Chapter 10 Working with Your MySQL Database
The different types of alterations you can make with this statement are shown in
Table 10.5.
Table 10.5 Possible Changes with the ALTER TABLE Statement
Syntax
Description
ADD [COLUMN] column_description
Adds a new column in the specified location
[FIRST | AFTER column ]
(if not specified, then the column goes at the
end). Note that
column_descriptions
need a name and a type,just as in a
CREATE
statement.
ADD [COLUMN] (column_description,
Adds one or more new columns at the end
column_description,...)
of the table.
ADD INDEX [index] (column,...)
Adds an index to the table on the specified
column or columns.
ADD [CONSTRAINT [symbol]]
Makes the specified column or columns the
PRIMARY KEY (column,...)
primary key of the table.The
CONSTRAINT
notation is for tables using foreign keys.See
Chapter 13”Advanced MySQL program-
ming,”for more details.
ADD UNIQUE [CONSTRAINT [symbol]]
Adds a unique index to the table on the
[index] (column,...)
specified column or columns.The
CONSTRAINT
notation is for InnoDB tables
using foreign keys. See Chapter 13 for more
details.
ADD [CONSTRAINT [symbol]]
Adds a foreign key to an InnoDB table.
FOREIGN KEY [index] (index_col,...)
See Chapter 13 for more details.
[reference_definition]
ALTER [COLUMN] column {SET DEFAULT
Adds or removes a default value for a
value | DROP DEFAULT}
particular column.
CHANGE [COLUMN] column new_column
Changes the column called
column
so that it
description
has the description listed. Note that this syn-
tax can be used to change the name of a col-
umn because a
column_description
includes a name.
MODIFY [COLUMN] column_description
Similar to
CHANGE
.Can be used to change
column types, not names.
DROP [COLUMN] column
Deletes the named column.
DROP PRIMARY KEY
Deletes the primary index (but not the
column).
DROP INDEX index
Deletes the named index.
DROP FOREIGN KEY key
Deletes the foreign key (but not the
column).
61
261
Altering Tables After Creation
DISABLE KEYS
Turns off index updating.
ENABLE KEYS
Turns on index updating.
RENAME [AS] new_table_name
Renames a table.
ORDER BY col_name
Re-creates the table with the rows in a par-
ticular order. (Note that after you begin
changing the table,the rows will no longer
be in order.)
CONVERT TO CHARACTER SET cs
Converts all text-based columns to the
COLLATE c
specified character set and collation.
[DEFAULT] CHARACTER SET cs
Sets the default character set and collation.
COLLATE c
DISCARD TABLESPACE
Deletes the underlying tablespace file for an
InnoDB table. (See Chapter 13 for more
details on InnoDB.)
IMPORT TABLESPACE
Re-creates the underlying tablespace file for
an InnoDB table. (See Chapter 13 for more
details on InnoDB.)
table_options
Allows you to reset the table options.Uses
the same syntax as
CREATE TABLE
.
Let’s look at a few of the more common uses of
ALTER TABLE
.
You may frequently realize that you haven’t made a particular column “big enough”
for the data it has to hold.For example,previously in the
customers
table,you allowed
names to be 50 characters long.After you start getting some data,you might notice that
some of the names are too long and are being truncated.You can fix this problem by
changing the data type of the column so that it is 70 characters long instead:
alter table customers
modify name char(70) not null;
Another common occurrence is the need to add a column.Imagine that a sales tax on
books is introduced locally and that Book-O-Rama needs to add the amount of tax to
the total order but keep track of it separately.You can add a
tax
column to the
orders
table as follows:
alter table orders
add tax float(6,2) after amount;
Getting rid of a column is another case that comes up frequently.You can delete the col-
umn you just added as follows:
alter table orders
drop tax;
Table 10.5 Possible Changes with the ALTER TABLE Statement
Syntax
Description
50
262
Chapter 10 Working with Your MySQL Database
Deleting Records from the Database
Deleting rows from the database is simple.You can do this using the
DELETE
statement,
which generally looks like this:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table
[WHERE condition]
[ORDER BY order_cols]
[LIMIT number]
If you write
delete from table;
on its own,all the rows in a table will be deleted,so be careful! Usually,you want to
delete specific rows,and you can specify the ones you want to delete with a
WHERE
clause.You might do this,if,for example,a particular book were no longer available or if
a particular customer hadn’t placed any orders for a long time and you wanted to do
some housekeeping:
delete from customers
where customerid=5;
The
LIMIT
clause can be used to limit the maximum number of rows that are actually
deleted.
ORDER BY
is usually used in conjunction with
LIMIT
.
LOW_PRIORITY
and
IGNORE
work as they do elsewhere.
QUICK
may be faster on
MyISAM tables.
Dropping Tables
At times,you may want to get rid of an entire table.You can do this with the
DROP
TABLE
statement.This process is very simple,and it looks like this:
DROP TABLE table;
This query deletes all the rows in the table and the table itself,so be careful using it.
Dropping a Whole Database
You can go even further and eliminate an entire database with the
DROP DATABASE
statement, which looks like this:
DROP DATABASE database;
This query deletes all the rows,all the tables,all the indexes,and the database itself,so it
goes without saying that you should be somewhat careful using this statement.
16
263
Next
Further Reading
In this chapter, we provided an overview of the day-to-day SQL you will use when
interacting with a MySQL database.In the next two chapters,we describe how to con-
nect MySQL and PHP so that you can access your database from the Web.We also
explore some advanced MySQL techniques.
If you want to know more about SQL,you can always fall back on the ANSI SQL
standard for a little light reading.It’s available from
http://www.ansi.org/
For more details on the MySQL extensions to ANSI SQL,you can look at the MySQL
website:
http://www.mysql.com
Next
In Chapter 11,“Accessing Your MySQL Database from the Web with PHP,”we cover
how to make the Book-O-Rama database available over the Web.
42
11
Accessing Your MySQL Database
from the Web with PHP
P
REVIOUSLY
,
IN OUR WORK WITH
PHP,
WE
used a flat file to store and retrieve data.
When we looked at this file in Chapter 2,“Storing and Retrieving Data,”we mentioned
that relational database systems make a lot of these storage and retrieval tasks easier,safer,
and more efficient in a web application.Now, having worked with MySQL to create a
database,we can begin connecting this database to a web-based front end.
In this chapter, we explain how to access the Book-O-Rama database from the Web
using PHP.You learn how to read from and write to the database and how to filter
potentially troublesome input data.
Key topics covered in this chapter include
n
How web database architectures work
n
Querying a database from the Web using the basic steps
n
Setting up a connection
n
Getting information about available databases
n
Choosing a database to use
n
Querying the database
n
Retrieving the query results
n
Disconnecting from the database
n
Putting new information in the database
n
Using prepared statements
n
Using other PHP-database interfaces
n
Using a generic database interface: PEAR DB
43
266
Chapter 11 Accessing Your MySQL Database from the Web with PHP
How Web Database Architectures Work
In Chapter 8,“Designing Your Web Database,”we outlined how web database architec-
tures work.Just to remind you,here are the steps again:
1. A user’s web browser issues an HTTP request for a particular web page.For exam-
ple,the user might have requested a search for all the books written by Michael
Morgan at Book-O-Rama,using an HTML form.The search results page is called
results.php
.
2. The web server receives the request for
results.php
,retrieves the file,and passes
it to the PHP engine for processing.
3. The PHP engine begins parsing the script.Inside the script is a command to con-
nect to the database and execute a query (perform the search for books).PHP
opens a connection to the MySQL server and sends on the appropriate query.
4. The MySQL server receives the database query,processes it, and sends the results—
a list of books—back to the PHP engine.
5. The PHP engine finishes running the script.This usually involves formatting the
query results nicely in HTML.It then returns the resulting HTML to the web
server.
6. The web server passes the HTML back to the browser,where the user can see the
list of books she requested.
Now you have an existing MySQL database,so you can write the PHP code to perform
the preceding steps.Begin with the search form.The code for this plain HTML form is
shown in Listing 11.1.
Listing 11.1 search.html— Book-O-Rama’s Database Search Page
<html>
<head>
<title>Book-O-Rama Catalog Search</title>
</head>
<body>
<h1>Book-O-Rama Catalog Search</h1>
<form action=”results.php” method=”post”>
Choose Search Type:<br />
<select name=”searchtype”>
<option value=”author”>Author</option>
<option value=”title”>Title</option>
<option value=”isbn”>ISBN</option>
</select>
<br />
Enter Search Term:<br />
Documents you may be interested
Documents you may be interested
