Application Programming Interfaces - Learning MySQL and MariaDB - page 439

Exercises
For the exercises in this chapter, use the API for whichever language you prefer. If you
have no preference, use PHP for the exercises. It's the most popular and probably the easi-
est to learn.
1. Write an API program that connects to MySQL and queries the rookery data-
base. Have the program execute a SELECT statement to get a list of birds. Use a
JOIN to access the birds , bird_families , and bird_orders tables to se-
lect the bird_id , common_name , and scientific_name from the birds
table, as well as the scientific_name from both the bird_families and
bird_orders tables. Joins were covered in Joining Tables . Use the LIMIT
clause to limit the results to 100 birds. When you're finished, execute the program
from the command line, or a web browser if using the PHP API.
2. Write an API program that accepts data from the user of the program. It may be
from the command line or from a web browser, if using the PHP API. Design the
program to connect to MySQL and the birdwatchers database. Have it execute
an INSERT statement to add data given by the user to the humans table, just data
for the formal_title , name_first , and name_last columns. Set the
value for join_date by using the CURDATE() function, and set the member-
ship_type column to basic .
After you write this program, use it to enter the names of a few fictitious people.
Then log into MySQL with the mysql client to verify that it worked.
3. Log into MySQL and use the CREATE TABLE statement to create a table named
backup_logs in the server_admin database (the CREATE TABLE state-
ment was covered in Creating Tables ). We created the server_admin database
at the beginning of this chapter. Design the backup_logs table however you
want, but be sure to include columns to record the date and time, and the name of a
backup file.
Use the GRANTS statement to give the admin_backup user account the
INSERT and SELECT privileges (at a minimum) for this new table (this was
covered extensively in SQL Privileges ) .
An example of a backup shell script was included in Creating Backup Scripts . Try
writing an API program that can be executed from the command line, not from a
web browser, to perform the same tasks as the shell script shown in that section.
Have it call the mysqldump utility — don't try to develop your own backup utility.
When you're finished, test the program to see whether it makes a backup file and
gives it the correct name based on the data. This exercise may be beyond your abil-