MySQL Section
In this and the following exercises, we will be using a variant of the 'Book-O-Rama' database that is used in the "PHP and MySQL Web Development" book. In order to create the database and populate it with a few starting records, you will need to do the following:
1. Log into grace.
2. Create the tables in your database. You will be using a file called bookdb.sql, which is located in my Grace directory (
/usr/users3/grovesr/web/books/bookdb.sql). You can copy this file to your own directory or use it in place. To use it in place, do the following:mysql database < /usr/users3/grovesr/web/books/bookdb.sql
where
database is the name of your database (this should just be your Grace userid). So, for example, the way that I created the database in my account was to enter:mysql grovesr < /usr/users3/grovesr/web/books/bookdb.sql
3. Insert the first few records. You will be using a file called book_insert.sql, which is located in my Grace directory (
/usr/users3/grovesr/web/books/bookdb.sql). You can copy this file to your own directory or use it in place. . To use it in place, do the following:mysql database < /usr/users3/grovesr/web/books/book_insert.sql
where
database is the name of your database (this should just be your Grace userid). So, for example, the way that I created the database in my account was to enter:mysql grovesr < /usr/users3/grovesr/web/books/book_insert.sql
Insert Exercises
Now we're ready to play with the database. We'll do that in the MySQL command line program - so start that up and point it to your database. First you might want to just take a look at all the 'book' records in the database:
select * from book_books;
Now try inserting some new book records. You can take a look at the listing of the book_insert.sql file to get an idea of how to insert with the first form that doesn't specify the columns. This insert is of the form:
insert into book_books values ("value1", "value2", ... , "valuen");
Insert the following three books using this method (and show the insert command you used):
MySQL
Paul Dubois
0-7357-0921-1
49.99
SQL in a Nutshell
Kevin Kline
1-56592-744-3
29.95
PHP Pocket Reference
Rasmus Lerdorf
1-56592-769-9
9.95
Insert the next three using the form of insert that specifies the exact columns to be used. This insert is of the form (and show the insert command you used):
insert into book_books ("column1", "column2", ... , "columnn") values ("value1", "value2", ..., "valuen");
Programming PHP
Rasmus Lerdorf & Kevin Tatroe
0-7357-0921-1
49.99
PERL in a Nutshell
Ellen Siever
1-56592-286-1
29.95
TCL/TK Tools
Mark Harrison
1-56592-218-2
49.95
Update Exercises
Now do some updates to the records in the database. Show your update commands to do the following:
1. Update the price of the book with the ISBN of 0-672-31697-8 to $75.
2. Increase the price of all the 'Nutshell' books by 10% (Hint use the 'like' operator).
3. There is an error in the database - there are really two authors for the "PERL in a Nutshell" book. The second author is Stephen Spainhour. Correct this error.
4. They've just come out with a Second Edition of the "SQL in a Nutshell" book. You only want to carry the latest editions, so update the record to reflect the new information:
SQL in a Nutshell, 2nd Edition
Kevin
1-56592-744-5
34.95
PHP/MySQL Exercises
1. Create a new set of search HTML and PHP files to search the 'book' database for customers, returning all the information on each.
2. Create a new set of insert HTML and PHP files to insert new customers into the database.
3. Challenge: Modify the results.php file to display the review of the book, if it exists.
4. Challenge: Create a page that will display the existing orders, with the customer's name, total amount of the order, book ordered, and the quantity displayed.