Please refer to online reading 53 for a complete list

Please refer to Online Reading 5.3 for a complete list of SQL commands
available in MySQL.
As a summary, here are the SQL statements discussed in this activity:
1 Data definition (DDL)
•
CREATE TABLE
; and
•
ALTER TABLE
(changing field type and length, adding an index).

Unit 5 25
2 Data manipulation (DML)
•
INSERT
;
•
UPDATE
;
•
DELETE
;
•
SELECT (single table)
; and
•
SELECT (join)
.
You’ve gone through a lot of database operations in this section! You’ve
also built some of the data structures and loaded all the necessary data
into the
ABCBooks
database. In the next section, you will learn how to
issue requests to the database from within an application program. Please
complete the following self-test to assess your understanding of SQL.
Self-test 5.3
1 What will happen if you issue the
DELETE
statement without
specifying any criteria? Example:
DELETE FROM AUTHORS;
2 Write an SQL statement that will create an index on the
email
field of
the
Customer
table.
3 Write an SQL statement which retrieves all records from the Book
table where the title contains the string
Earth
.
4 Identify two column types that may be used when defining date/time
fields in a table. You can use this URL as a reference:
.

26 COMP S834 Web Server Technology
PHP and MySQL
Previously, you were issuing commands directly to the database server
from a command-line client. In this section, we will incorporate the SQL
commands within a PHP program that acts as a client to the database
server. The PHP program runs as a server-side script on our Web server.
It will process inputs from our Web users and generate dynamic pages
from the database in response to their requests.
We will start the section by discussing the native PHP functions for
accessing MySQL databases. Later on, we will also talk about the Open
Database Connectivity (ODBC) programming interface and how this can
allow the same program to work with many different databases (not just
MySQL). We will end the section with a brief introduction to a
programming approach that uses a database abstraction layer to
centralize all the database access code within your server-side scripts.
Here’s a breakdown of the functions that we will implement for ABC
Books.
1 Searching for a book using a variety of methods:
• by title — entire title or part of the title;
• by author — various combinations of first name, last name and
middle name (if any); and
• by keywords — full-text search on book titles, book descriptions,
author names and author descriptions.
2 Maintaining a shopping cart:
Users can add a book to their shopping cart by clicking on an Add to
Cart button. The quantity for each book is set to 1 by default but can
be updated. Setting the quantity to 0 will remove the book from the
cart.