MySQL – MariaDB – Application Programming Interfaces

How to install web server (IIS) on Windows Server 2019

An API allows a programming language to interface easily with a computer
software system. The advantage of an API is that you can customize user
interfaces to MySQL and MariaDB to suit your needs. Huge websites use APIs
to allow the public to interact with their MySQL and MariaDB databases,
without the user needing to know anything about the databases they’re using
or SQL statements.

This chapter covers several APIs that may be used to interface with
MySQL and MariaDB, so that you may write customized applications to
interface with databases. There are sections for the C API, the Perl DBI,
the PHP API, the Connector/Python, and the Ruby API. Many other programming
languages have APIs for connecting to MySQL; these are just some of the more
popular ones. The section on each API and related libraries includes a basic
tutorial on how to connect to MySQL and MariaDB, and how to query a database
with the API.

It’s unlikely you will need to know more than one API. Instead, you
may want to read the section for the programming language you know and use.
My preference is the Perl language and the Perl DBI. It’s most in line with
natural languages such as English and Italian. If you have no preference and
would like to learn a MySQL API, though, the PHP API is very popular and has
many functions for interacting with MySQL. Plus, PHP is a fairly easy
language to learn and you can use snippets of code within web pages and
content management systems like WordPress and Drupal.

It’s beyond the scope of this book to include a tutorial on any
programming language. I assume you can learn the basics of the language you
choose among the many books and online resources available. These examples
use basic features of the languages to show you how database access

Before skipping ahead to a section about a particular API, you should
create a couple of API user accounts that you may use in the examples and in
the exercises. The exercises at the end of the chapter are suited to
whichever API you prefer, not to one in particular.

Creating API User Accounts

Assuming that the programs that we’ll write may be executed by the public,
let’s create a user account specifically for them (creating users was
covered in Chapter 13). We’ll call this user
account public_api and give it
only the SELECT privilege for the
rookery and birdwatchers databases. Execute the
following on your server:

CREATE USER 'public_api'@'localhost'
IDENTIFIED BY 'pwd_123';

ON rookery.*
TO 'public_api'@'localhost';

ON birdwatchers.*
TO 'public_api'@'localhost';

This creates the public_api@localhost user account with the
password pwd_123. You can give it a
more secure and different password. It has access just to our two
databases from the localhost. It can only execute SELECT
statements and can’t change or delete data or do anything else. We’ll use
this user account for the API programs that we’ll create, which retrieve
data through a public web page.

For some of the API programs we will write, we’ll need another
administrative user account, admin_members. It will be designated for
administering information on members of our site. Create that user account
by executing the following SQL statements:

CREATE USER 'admin_members'@'localhost'
IDENTIFIED BY 'doc_killdeer_123';

ON birdwatchers.*
TO 'admin_members'@'localhost';

This administrative user account can select, update, and delete data
only on the birdwatchers database. It mostly needs access to
the humans table, but may sometimes need access to the other
tables in the database. It won’t use the rookery database, so
we’re not giving it access to that database.


The C language isn’t as popular as it once was, but it’s still a standard. In
fact, the core software of MySQL is written in C. The C API is provided by
MySQL. This section provides a basic tutorial on how to connect to a
database and how to query it with C and the C API, the basic components
and tasks you need to know to use this API.

Connecting to MySQL

When writing a C program to interact with a database, first we need to prepare
variables that will store data on the database connection and the
results of a query we intend to execute. Then we will need to establish
a connection to the server. To do this easily, we’ll include a couple of
C header files: stdio.h for basic C
functions and variables, and mysql.h for special MySQL functions and
definitions (these two files come with C and MySQL, as well as MariaDB;
you shouldn’t have to download them if C and MySQL were installed
properly on your server):

#include <stdio.h>
#include "/usr/include/mysql/mysql.h"
int main(int argc, char *argv[  ])
   MYSQL *mysql;
   MYSQL_RES *result;
   MYSQL_ROW row;

The < and > symbols surrounding
stdio.h tells C to look for the
file in the default location for C header files (e.g., /usr/include), or in the user’s path. Because
mysql.h may not be in the default
locations, the absolute path is given within double quotes. An
alternative here would have been <mysql/mysql.h>,
because the header file is in a subdirectory of the default C header
file directory.

The standard main function begins by preparing
variables needed for the connection to MySQL. The first line creates a
pointer to the MYSQL structure stored in the
mysql variable. The next line defines and names a results
set based on the definitions for MYSQL_RES in mysql.h. The results are to be stored in the
result array, which will be an array of rows from MySQL.
The third line of main uses the definition for
MYSQL_ROW to establish the row variable, which will be used
later to contain an array of columns from MySQL.

Having included the header files and set the initial variables, we
can now set up an object in memory for interacting with the MySQL server
using the mysql_init() function:

if(mysql_init(mysql) == NULL) {
   fprintf(stderr, "Cannot Initialize MySQL");
   return 1;

The if statement here is testing whether a MySQL
object can be initialized. If the initialization fails, a message is
printed and the program ends. The mysql_init() function
initializes the MySQL object using the MYSQL structure
declared at the beginning of the main function, which is
called by convention, mysql . If C is successful in
initializing the object, it will go on to attempt to establish a
connection to the MySQL server:

   fprintf(stderr, "%d: %s \n", mysql_errno(mysql), mysql_error(mysql));
   return 1;

The elements of the mysql_real_connect() function here are
fairly obvious: first the MySQL object is referenced; next the hostname
or IP address; then the username and password; and finally the database
to use. For this example, we’re using the public_api@localhost user account we created in
the beginning of this chapter. The three remaining items are the port
number, the socket filename, and a client flag, if any. Passing 0 and
NULL values tells the function to use the defaults for these.

If the program cannot connect, it prints the error message
generated by the server to the standard error stream, along with the
MySQL error number ( %d), and finally a string
( %s) containing the MySQL error message and then a newline
( \n). It will get the error number from the
mysql_errno() function and the error message from the mysql_error() function. If the program
can connect without an error, though, it will return 1 to indicate
success and continue with the program.

Querying MySQL

The program so far only makes a connection to MySQL. Now let’s look at how
you can add code to the program to run an SQL statement with the C

If the API program has connected to MySQL, it can query the MySQL
server with a query function such as mysql_query(). Let’s use
SELECT to get a list of birds from the birds
table. The code for doing this and displaying the results is as


if(mysql_query(mysql,"SELECT common_name, scientific_name FROM birds")) {
   fprintf(stderr, "%d:  %s\n",
   mysql_errno(mysql), mysql_error(mysql));
else {
   result = mysql_store_result(mysql);
   while(row = mysql_fetch_row(result)){
      printf("\%s - \%s \n", row[0], row[1]);
return 0;

Within the if statement here, we’re using
mysql_query() to query MySQL. You could use the
mysql_real_query() function instead. It allows the retrieval of binary data, which can
be safer, but isn’t necessary for this simple example. The
mysql_query() function returns 0 if it’s successful and
nonzero if it’s not. So if the SQL statement contained within it doesn’t
succeed in selecting data from MySQL, an error message will be printed.
However, if the query is successful, the else statement
will then be executed, because the if statement will have
received a value of 0.

In the else statement block, the first line uses
the mysql_store_result() function to store
the results of the query in the result variable.

Before letting go of the data, using while, the code
loops through each row of the results set. We’re using the mysql_fetch_row() function to fetch each
row and store it temporarily in the row variable. Because
we know how the data is organized from the SELECT
statement, we can use printf with its formatting codes to
display each column. Notice that each column is extracted with standard
array syntax (i.e., array
[ n]

Once C has gone through each row of the results, it will stop
processing and use mysql_free_result() to free the memory for result, concluding the
else statement.

We end this brief program with the mysql_close() function to end the
MySQL session and to disconnect from MySQL. The final closing curly
brace ends the main function.

Complete Minimal C API Program

It’s easier to explain the components of a program step by step as I have done
here, but seeing even a small program in pieces can be confusing. So
here it is again in its entirety:

#include <stdio.h>
#include "/usr/include/mysql/mysql.h"
int main(int argc, char *argv[  ])
   MYSQL *mysql;
   MYSQL_RES *result;
   MYSQL_ROW row;

   if(mysql_init(mysql) == NULL) {
        fprintf(stderr, "Cannot Initialize MySQL");
        return 1;

   if(!mysql_real_connect(mysql, "localhost", "public_api",
      "pwd_123", "rookery", 0, NULL, 0)) {
      fprintf(stderr, "%d: %s \n", mysql_errno(mysql), mysql_error(mysql));
      return 1;

   if(mysql_query(mysql,"SELECT common_name, scientific_name FROM birds")) {
      fprintf(stderr, "%d:  %s\n",
      mysql_errno(mysql), mysql_error(mysql));
   else {
      result = mysql_store_result(mysql);

      while(row = mysql_fetch_row(result)) {
            printf("\%s - \%s \n", row[0], row[1]);
   return 0;

Compiling with C Includes

You can use any compiler to compile the program we wrote, but I’ll
show the GNU C Compiler ( gcc) here
because it’s free software and automatically loaded on
some systems. To compile and link the program, enter something like the
following from the command line:

gcc -c `mysql_config --cflags` mysql_c_prog.c
gcc -o mysql_c_prog mysql_c_prog.o `mysql_config --libs`

When the compiler attempts to compile the program ( mysql_c_prog.c), it will check for syntax
errors in the code. If it finds any, it will fail to compile and will
display error messages. If it’s successful, the resulting compiled
program ( mysql_c_prog) will be
ready to be executed.

Perl DBI

The easiest method of connecting to MySQL with the Perl programming language is to
use the Perl DBI module. This section assumes that you have a basic
knowledge of the Perl language. We’ll focus on how to connect to MySQL,
run SQL statements, and retrieve data with Perl, rather than the
idiosyncrasies of Perl itself. This is meant to be a simple tutorial for a
Perl programmer to get started with the Perl DBI.

For the example in this section, suppose we want to write a program
for one of the administrators to get a list of members and to optionally
change the expiration of their membership. For this, we’ll use the
admin_members user account that’s designated for
administering information on members. We created that user account at the
start of this chapter.


The Perl DBI module is part of the core Perl installation. You can download both
Perl and the DBI module from CPAN.

If your server already has Perl installed on it, which most do,
you can execute the following from the command line to install the DBI

perl -MCPAN -e 'install DBI'

If you don’t have Perl installed already on your server, you can
use an installation utility like yum to install the
DBI module. If you have yum on your
server, enter the following from the command line while logged in as
root or an administrative filesystem user:

yum install perl perl-mysql

Connecting to MySQL

To interface with MySQL, you must first call the DBI module and then connect
to MySQL. To make a connection to a database using the Perl DBI, only
the following lines are needed in a Perl program to connect to the

#!/usr/bin/perl -w
use strict;

use DBI;

my $user = 'admin_members';
my $password = 'doc_killdeer_123';
my $host =  'localhost';
my $database = 'birdwatchers';

my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $password)
        || die "Could not connect to database: " . DBI->errstr;

The first two lines start Perl and set a useful condition for
reducing programming errors (i.e., use strict). The next
line calls the DBI module. Then we create a set of variables containing
values for logging into MySQL. The next statement, which is spread over
two lines, sets up a database handle ( $dbh) that specifies
the database engine ( mysql). We give it the login
variables. The rest of the statement relates to what to do if the
program is unable to connect to MySQL. If the connection is successful,
though, the program will continue on.

Querying MySQL

Making a connection to MySQL does little good unless an SQL
statement is executed. Any SQL statement can be executed through an API.
The only restrictions are those imposed by the MySQL server on the user
account executing the SQL statements within the application. If the user
account can execute only SELECT statements, that’s all that
the application may execute. Let’s look at some examples here of how to
select and insert data in MySQL through an application.

Selecting data

Continuing the previous example, let’s execute a
SELECT to get a list of birds from the birds
table. Let’s allow the user of the Perl program to specify a common
name of birds to select, when executing it from the command line. For
instance, the user might enter Avocet to get a
list of Avocet birds. We’ll use a LIKE operator in the
WHERE clause to allow for some flexibility. Here’s how
the code for that would look:

my $search_parameter = shift;

my $sql_stmnt = "SELECT human_id,
                 CONCAT(name_first, SPACE(1), name_last) AS full_name,
                 FROM humans
                 WHERE name_last LIKE ?";

my $sth = $dbh->prepare($sql_stmnt);


The first line here sets up a variable,
$search_parameter, to store a value from
shift, which loads into that variable the value given by
the user when executing the program. On the next line of code, we
create the $sql_stmnt variable to store the SQL
statement. Notice that where we would specify the last name of the
member in the WHERE clause, we entered instead a question
mark. This is known as a placeholder. We will
replace the placeholder with
$search_parameter two lines later. Placeholders are a
good security precaution. For more information on this, see SQL Injection.

After creating the $sql_stmnt variable, we use
the prepare() function of the database
handle in order to prepare the SQL statement to form an SQL statement
handle ( $sth). Then we use the execute() function to execute the
statement handle, with the $search_parameter to replace
the placeholder. To replace multiple placeholders, you would list them
in a comma-separated list within the parentheses of

Having connected to MySQL and invoked an SQL statement, what
remains is to capture the data results and to display them to the
administrator. The fetchrow_array() function can be used
to fetch the data one row at a time. We’ll use that with a
while statement. Here’s how that would look:

while(my($human_id,$full_name,$membership_expiration) = $sth->fetchrow_array())
   print "$full_name ($human_id) - $membership_expiration \n";


The while statement executes its block of code
repeatedly so long as there are rows to process. The value of each
element of each array (i.e., each row) is stored in the two variables
$common_name and $scientific_name—and
overwritten by each loop of while. Then the variables are
printed to the screen with a newline character after each pair.

The second to last line uses finish() to end the SQL statement
handle. The last line disconnects the database handle with disconnect(). Alternatively,
you can leave open the connection to MySQL so that you can create and
execute more statement handles to interface with MySQL.

A better method of retrieving data from MySQL perhaps would be
to capture all of the data in memory for later use in the Perl
program, thus allowing the connection to MySQL to end before
processing the results. Putting MySQL on hold while processing each
row as shown earlier can slow down a program, especially when dealing
with large amounts of data. It’s sometimes better to create a complex
data structure (i.e., an array of arrays) and then leave the data
structure in memory until needed. To do this, you’d use the fetchall_arrayref() method. It
will return the starting location in memory of the array. Here’s an
example of this:

my $members = $sth->fetchall_arrayref();


foreach my $member (@$members){
   my ($human_id, $full_name, $membership_expiration) = @$member;
   print "$full_name ($human_id) - $membership_expiration \n";


The fetchall_arrayref() fetches all of the rows,
stores them in an array in memory, and returns a reference to its
location. This is stored in $members. Using a
foreach, we extract each array within the
@$members array and store it in $member.
With the block of the foreach, we extract each element of
the $member array and store those values in
$human_id, $full_name, and
$membership_expiration. We then display them using

Notice that we executed the finish() to end the
statement handle and to free MySQL resources. We could have also put
disconnect() immediately after it if we didn’t intend to
create and execute more SQL statement handles. This would have had no
effect on the foreach processing the results fetched by

Updating data

In the previous examples, we saw how to select data from a
table. Let’s now look at an example that updates data in a table.
We’ll change the $sql_statement to include an
UPDATE statement that will update the date of
membership_expiration for a member in the
humans table. We can do that like this:

my ($human_id, $membership_expiration) = (shift, shift);

$sql_stmnt = "UPDATE humans
              SET membership_expiration = ?
              WHERE human_id = ?";

$sth = $dbh->prepare($sql_stmnt);

Here we’re using shift twice to capture two values
entered by the user and store them in the $human_id and
$membership_expiration variables. The
$sql_statement is given two placeholders. We replace
those placeholders with the two variables, in the proper order, when
we execute the SQL statement through the statement handle
( $sth) using execute().

The end result of this bit of code is to update the row related
to the given $human_id in the humans table.
Because this UPDATE privilege is one to which you might not want the public to have
access, it would be best to use this program just internally from a
known IP address, and to require a password.

A Full Example with Perl DBI

It’s easier to explain the components of a program step by step as I have done here,
but seeing a program in pieces can be confusing. Combinig these Perl
program snippets, we’ll create a program and call it member_adjust_expiration.plx. Here’s how it

#!/usr/bin/perl -w use strict;

use DBI;

my $search_parameter = shift || '';
my $human_id = shift || '';
my $membership_expiration = shift || '';

my $user = 'admin_members';
my $password = 'doc_killdeer_123';
my $host =  'localhost';
my $database = 'birdwatchers';

my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $password)
        || die "Could not connect to database: " . DBI->errstr;

if($search_parameter && !$membership_expiration) {
   my $sql_stmnt = "SELECT human_id,
                    CONCAT(name_first, SPACE(1), name_last) AS full_name,
                    FROM humans
                    WHERE name_last LIKE ?";

   my $sth = $dbh->prepare($sql_stmnt);

   my $members = $sth->fetchall_arrayref();


   print "List of Members - '$search_parameter' \n";

   foreach my $member (@$members){
      my ($human_id, $full_name, $membership_expiration) = @$member;
      print "$full_name ($human_id) - $membership_expiration \n";


if($human_id && $membership_expiration) {
   $sth = $dbh->prepare($sql_stmnt);
   $sql_stmnt = "UPDATE humans
                 SET membership_expiration = ?
                 WHERE human_id = ?";

   $sth = $dbh->prepare($sql_stmnt);
   my ($rc) = $sth->execute($email_address,$human_id);


   if($rc) {
     print "Membership Expiration Changed. \n";
   else {
     print "Unable to change Membership Expiration. \n";

If this program is executed from the command line, adding the last
name of the Hollar after the name of the program,
it will return the name of Lexi Hollar with her human_id in
parentheses, along with the date her membership expires. The following
example shows how a user might execute the program, and the results
returned from running it with this user value:

member_adjust_expiration.plx Hollar

List of Members - 'Hollar'
Lexi Hollar (4) - 2013-09-22

The program can be run again with a new expiration date for the
member like so:

member_adjust_expiration.plx Hollar 4 2015-06-30

Notice that the program is expecting three values. If it receives
only one value, the member’s last name, it executes the
SELECT statement and displays the user information. If it
receives three values, it will execute the UPDATE
statement. Values must be in the correct order and format. The program
will display a message indicating whether it’s successful in changing
the membership expiration date.

You could write this program in more elaborate ways. You could
allow the user to select a date, or the number of months or years to add
to the expiration date using date functions. You could change it to run
through a web interface using the CGI Perl module so that the user can
click choices instead of typing them at the command line. However, this
simple program gives you a good idea of how to get started writing a
Perl API to interface with MySQL.

More Information

To learn about Perl, see Learning
(O’Reilly) by Randal Schwartz, brian d foy, and
Tom Phoenix. For more details on using the Perl DBI with MySQL, see
Alligator Descartes and Tim Bunce’s Programming
the Perl DBI
(O’Reilly). To learn more about Perl
references and other advanced Perl topics, see Intermediate
(O’Reilly) by Randal Schwartz.


One of the most popular programming language and database engine
combinations for the Web is PHP with MySQL. This combination works well
for many reasons, but primarily the speed, stability, and simplicity that
both offer. In addition, PHP scripts can be used easily with HTML to
generate web pages. This section provides a basic tutorial on how to
connect to MySQL and how to query MySQL with PHP using the PHP API, all
within a web page.

Installing and Configuring

There are actually three popular APIs that may be used to connect to MySQL with PHP. It’s
recommended that you use the mysqli ( MySQL
) extension, which replaces the older
mysql extension. We’ll use the mysqli API for
the programming examples in this section.

On many Linux systems, PHP is already installed. However, you can
use an installation utility like yum
to install PHP, as well as the PHP API, mysqli. You would
do that like this:

yum install php php-mysql

If you’ll be executing PHP code within web pages, which is a very
nice feature, you may have to make an adjustment to your web server
configuration. If you’re using Apache, you may have to add the
AddType directive to your Apache configuration to tell the web server to
execute code snippets with PHP. You can either put the following line in
the web server’s configuration file ( httpd.conf) to make it global, or add it to a
.htaccess file in the directory
where the HTML pages containing the PHP code snippets are

AddType application/x-httpd-php .html

If you add this directive to the httpd.conf configuration file, you’ll have to
restart the Apache web service for it to take effect. You won’t have to
do that with the .htaccess

To use PHP with MySQL, you may also have to enable MySQL with PHP
by configuring PHP with the
--with-mysql= /path_to_mysql
option. That won’t be necessary, though, if you installed the PHP API
using yum.

Connecting to MySQL

For PHP code to interface with MySQL, it must first make a connection to
MySQL to establish a MySQL client session. This bit of code will do


  $host = 'localhost';
  $user = 'public_api';
  $pw = 'pwd_123';
  $db = 'rookery';

  $connect = new mysqli($host, $user, $pw, $db);

  if (mysqli_connect_errno()) {
       printf("Connect failed: %s\n", mysqli_connect_error());


We’ve enclosed the code within <?php...?> tags
so that it may be embedded within an HTML web page. If you wanted to
create a program that is executed from the command line and not by a web
browser, it would have to start with #!/usr/bin/php. For
our examples, though, we’ll stay with writing code for use in a web

The PHP code contained within the <?php...?>
tags creates variables containing information the application will need
to connect to MySQL and select the default database. After those
variables, we’re using the mysqli() function to connect to MySQL
with those variables. We’ll refer to that connection with the variable
we named $connect. If it’s unsuccessful, the script dies
with an error message. If the connection is successful, though, we can
then query the database. The connection will stay open until we close

Querying MySQL

Let’s continue with our script by retrieving a list of birds from the
birds table. The following snippet would be placed after
the previous snippet that connects to MySQL, but within the same web
page. It will query the database, fetch rows from the birds
table, and display them to the user:

  $sql_stmnt = "SELECT common_name, scientific_name
                FROM birds
                WHERE LOWER(common_name) LIKE LOWER(?)";
  $sth = $connect->prepare($sql_stmnt);

  $search_parameter = $_REQUEST['birdname'];
  $search_parameter = "%" . $search_parameter . "%";

  $sth->bind_param('s', $search_parameter);

  $sth->bind_result($common_name, $scientific_name);

while( $sth->fetch() ) {
  print "$common_name - <i>$scientific_name</i> <br/>";


The first piece of this creates a variable
( $sql_stmnt) containing the SQL statement we want to
execute. We then prepare that statement with the prepare() function in relation to
$connect, thus creating a statement handle
( $sth).

A user would execute the program we’re creating through a query at
the end of the web address. For instance, they would add ?birdname=Avocet to the web address to query
for a list of Avocet birds.

A Web Form

A web user wouldn’t normally enter a variable name and a search value at the
end of a web address in a web browser. Instead, this web page we’re
building would be preceded by another web page containing an HTML form
for the user to enter a search parameter. Here’s how that web form
would look:

<h3>Search Birds Database</h3>
<form action="birds.html" method="post">
<p>Enter a parameter by which to search
the common names of birds in our database:</p>
<input type="text" name="birdname" />
<input type="submit" />

This form on the preceding page calls the web page we’re
writing, passing the search parameter to it in the proper

In the next pair of lines in the example, we’re capturing the
query request value in a variable we named
$search_parameter. Because we intend to use this variable
with a LIKE operator, we need to put the %
wildcard before and after the variable.

The next line uses bind_param() to bind the prepared
statement to the $search_parameter, specifying first that
it’s a string value with the 's'. Then we use the
execute() function to execute the completed statement handle.

The bind_result() prepares the variables that will be used to parse the array elements,
or fields of the results. Calling on the statement handle again, a
while statement loops through the results using the
fetch() function to fetch data one row at a time from the
results. Within the while statement block, we’re printing
the values with HTML tags. When it’s finished, we close the statement
handle and the connection.

The output of this script is a line for each bird based on the
search criteria in the birds table. In this simple example,
only a few of the many PHP functions for MySQL are used to get and
display data. These snippets are shown here together within a very basic
web page:

  $search_parameter = $_REQUEST['birdname'];

  $host = 'localhost';
  $user = 'public_api';
  $pw = 'pwd_123';
  $db = 'rookery';

  $connect = new mysqli($host, $user, $pw, $db);

  if (mysqli_connect_errno()) {
       printf("Connect failed: %s\n", mysqli_connect_error());
<h3>Birds - <?php echo $search_parameter ?></h3>
<p>Below is a list of birds in our database based on your search criteria:</p>
  $sql_stmnt = "SELECT common_name, scientific_name
                FROM birds
                WHERE common_name LIKE ?";
  $sth = $connect->prepare($sql_stmnt);

  $search_parameter = "%" . $search_parameter . "%";
  $sth->bind_param('s', $search_parameter);
  $sth->bind_result($common_name, $scientific_name);

while($sth->fetch()) {
  print "$common_name - <i>$scientific_name</i> <br/>";


This example is almost the same as the two major sections shown
previously. We’ve added opening and closing HTML tags and some with text
in between the two PHP code snippets. We also positioned a couple of the
lines in different places, but it flows the same. Here’s the text
returned to the web user when searching for Avocet birds:

Birds - "Avocet"
Below is a list of birds in our database based on your search criteria:

Pied Avocet - Recurvirostra avosetta
Red-necked Avocet - Recurvirostra novaehollandiae
Andean Avocet - Recurvirostra andina
American Avocet - Recurvirostra americana
Mountain Avocetbill - Opisthoprora euryptera

More Information

If you would like to learn more about using the PHP API mysqli,
there’s an extensive manual on the PHP site, including a MySQL Improved Extension
manual. You might also like to read Robin Nixon’s book, Learning
PHP, MySQL & Javascript
(O’Reilly) to learn more
about using PHP within web pages to access MySQL.


To use Python with MySQL, you can you use the MySQL Connector/Python. It’s
written in Python and needs only the Python libraries to function. It
doesn’t require any Python modules besides what’s already part of the
Python standard library. Nor does it require the MySQL client


The first thing you will need to do is install the MySQL
Connector/Python on your server. You can do this by using an
installation utility like yum on a
Linux system. Python and its libraries are probably already installed on
your server, but you can try installing them at the same time to be
sure. Execute this from the command line:

yum install python python-libs mysql-connector-python


This section uses Version 2 of Python, which is still the most
common one installed on Linux and Mac systems at the time of this
writing. Version 3 is becoming popular, and requires minor syntax
changes, but you can read about it elsewhere. If you want to use
Version 3, and perhaps another library for connecting Python to MySQL,
you will probably need only minor changes to the code shown in this

Once you have the connector installed on your server, you can then
write and run a Python program to connect to MySQL and query databases.
For the example in this section, suppose the database administrator in
charge of managing MySQL users has asked us to write a program that
would give him a list of user accounts and privileges for each. Let’s go
through a very simple program to do this.

Connecting to MySQL

To query a database with Python, we will need to establish a connection with
MySQL. Here is the beginning part of a Python program to do this:


import mysql.connector

config = {
    'user': 'admin_granter',
    'password': 'avocet_123',
    'host': 'localhost',
    'database': 'rookery'

cnx = mysql.connector.connect(**config)
cur = cnx.cursor(buffered=True)

The first line is the required line invoking Python. Next we
import mysql.connector, the MySQL Connector/Python. We then
create a hash to store the login information we will need for connecting
to MySQL. We’re using the admin_granter@localhost user account because it
has the privileges to execute the SHOW GRANTS statement and
to query the mysql database, which contains user account
information. We created this user in User Account to Grant Privileges.

The final pair of lines of the previous code snippet establishes
the connection to MySQL. The first uses the connect() call for the MySQL
Connector/Python using the values in the config hash,
loading its results into the cnx variable. The second
creates a cursor object ( cur) to use for executing queries
on the database.

Querying MySQL

Because there is no SHOW USERS statement, we’ll have to query the mysql database to
select a list of user accounts from the user table. To do
this, we’ll first create a variable to store the SELECT
statement we want to execute. Then we’ll use the execute() call to execute it. Here’s how
this part of the program would look:

sql_stmnt =  ("SELECT DISTINCT User, Host FROM mysql.db "
              "WHERE Db IN('rookery','birdwatchers') "
              "ORDER BY User, Host")


So as to fit the SELECT statement on the page, we’ve
broken it onto multiple lines. We pass that variable to the
execute() function to execute the SQL statement. We’re now
ready to fetch the rows, parse the fields from the results, and display

for row in cur.fetchall() :
  user_name = row[0]
  host_address =  row[1]
  user_account =  "'" + user_name + "'@'" + host_address + "'"

  print "%s@%s" % (user_name, host_address)


We’re using a for statement here to loop through the
results of a fetchall() call for the cur
cursor object. It takes the values from each row fetched and stores it
in an array we named row. Within the statement block of the
for statement, we extract each array element and store the
values temporarily in string variables, in user_name and
host_address. Then we assemble them with some text for
nicer formatting and store them in a variable we named
user_account. Its contents will look like

We end this program by displaying the user_account
values to the administrator, and then closing the cursor object and the
connection to MySQL.

Sample Python Program

It’s easier to discuss a program by breaking it into its components as we’ve just
done, but it can be confusing to understand how it all comes together.
The following listing combines the preceding snippets, but with some
additions that make it a bit more elaborate:


import re
import mysql.connector

# connect to mysql
config = {
    'user': 'admin_granter',
    'password': 'avocet_123',
    'host': 'localhost',
    'database': 'rookery'

cnx = mysql.connector.connect(**config)
cur = cnx.cursor(buffered=True)

# query mysql database for list of user accounts
sql_stmnt =  "SELECT DISTINCT User, Host FROM mysql.db "
sql_stmnt += "WHERE Db IN('rookery','birdwatchers') "
sql_stmnt += "ORDER BY User, Host"


# loop through list of user accounts
for user_accounts in cur.fetchall() :
  user_name = user_accounts[0]
  host_address =  user_accounts[1]
  user_account =  "'" + user_name + "'@'" + host_address + "'"

  # display user account heading
  print "\nUser Account: %s@%s" % (user_name, host_address)
  print "------------------------------------------"

  # query mysql for grants for user account
  sql_stmnt = "SHOW GRANTS FOR " + user_account

  # loop through grant entries for user account
  for grants in cur.fetchall() :
    # skip 'usage' entry
    if'USAGE', grants[0]) :

    # extract name of database and table
    dbtb ='ON\s(.*)\.+?(.+?)\sTO', grants[0])
    db =
    tb =

    # change wildcard for tables to 'all'
    if'\*', tb) :
      tb = "all"

    # display database and table name for privileges
    print "database: %s; table: %s" % (db,tb)

    # extract and display privileges for user account
    # for database and table
    privs ='GRANT\s(.+?)\sON', grants[0])
    print "privileges: %s \n" % (


This program does much more than the previous snippets. As a
result, I’ve annotated it at various points to help you understand it.
Still, let’s go through the key points, especially the additions.

First, the program gets a list of user accounts, storing them in
an array named user_accounts. Using a for
statement, it goes through each row of user_accounts to
extract each user_account. For each, it prints a heading to
display the user account to the administrator. This part is similar to
the previous excerpts.

We then put a new SQL statement, SHOW GRANTS, in
sql_stmnt for each user_account. We execute
and then use another for statement to go through the
results of a fetchall(), which we store in a variable
we’ve named grants. If a row from grants
contains the word USAGE, we skip displaying that. We then
parse out the database and table name, store them in variables named
db and tb, and display them. The last pair of
lines extracts the list of privileges and displays them.

Some of the results of running this Python program on my system

User Account: lena_stankoska@localhost
database: `rookery`; table: all

database: `birdwatchers`; table: all

User Account: public_api@localhost
database: `birdwatchers`; table: all
privileges: SELECT

database: `rookery`; table: all
privileges: SELECT

This is a nice way for the administrator to get a list of users
and see what privileges they have for particular databases and tables,
especially because there isn’t a built-in function to do what we want.

More Information

If you would like more information on MySQL Connector/Python, there’s an
extensive manual on MySQL’s site, including a MySQL
Connector/Python Developer Guide. You might also like to read
Mark Lutz’s book, Learning

Ruby API

The Ruby language has become very popular and can be used to create programs
to access a database. There are two MySQL modules for Ruby. The MySQL/Ruby
module is built on the MySQL C API. As such, it has the same functions in
Ruby as the C API. This is a nice feature if you already know the C API.
The other module is the Ruby/MySQL module—this pairing and reverse pairing
of the names can be confusing. The Ruby/MySQL module is written in Ruby
and is included in Ruby on Rails. For the examples in this section, we
will use the former, the MySQL/Ruby module.

Installing and Preparing MySQL/Ruby

Before writing a Ruby program to interface with MySQL, let’s install the
MySQL/Ruby module, which uses the same functions as the MySQL C API. You
can do this by using an installation utility like yum on a Linux
system. Execute the following from the command line, while logged in as
the root or some other administrative
filesystem user:

yum install ruby ruby-mysql

If you can’t use yum on your
server, you can check MySQL’s website to download Ruby
modules and to find instructions on installing them.

Once you have Ruby and the MySQL/Ruby module installed on your
server, you can then write and run a Ruby program to connect to MySQL
and query the databases. Let’s go through a very simple program to do
this. For this example program, we’ll use the admin_backup@localhost user account. We created
this user account in Username and Host. We
will be selecting and inserting data in a database we’ll call
server_admin. One of the tables in this database will be
backup_policies. We’ll then insert data into this table
related to our backup policies as a reference. We’ll log information
about the backups, and other server information in that database.

To prepare for the program we’re about to write, let’s create the
server_admin database and the tables we need for it. Create
the database and the backup_policies table by executing the
following SQL statements:

CREATE DATABASE server_admin;

CREATE TABLE backup_policies
backup_name VARCHAR(100),
file_format_prefix VARCHAR(25),
frequency ENUM('daily','weekly'),
days ENUM('first','every'), start_time TIME,
location ENUM('on-site','off-site','both'),
tables_include VARCHAR(255) );

Now that we’ve created the backup_policies table,
let’s insert data in it related to our backup policies shown in Table 14-2. We’ll execute
the following INSERT statement:

INSERT INTO backup_policies
(backup_name, file_format_prefix, frequency,
 days, start_time, secure, location, tables_include)
('rookery - full back-up', 'rookery-', 2, 1, '08:00:00', 0, 2, "all tables"),
('rookery - bird classification', 'rookery-class-', 1, 2, '09:00:00', 0, 1,
 "birds, bird_families, bird_orders"),
('birdwatchers - full back-up',
 'birdwatchers-', 2, 1, '08:30:00', 1, 2, "all tables"),
('birdwatchers - people', 'birdwatchers-people-', 1, 2, '09:30:00', 1, 1,
 "humans, birder_families, birding_events_children"),
('birdwatchers - activities', 'birdwatchers-activities-', 1, 2, '10:00:00', 0, 1,
 "bird_sightings, birding_events, bird_identification_tests,
  prize_winners, surveys, survey_answers, survey_questions");

In addition, we will need another table in the
server_admin database. We’ll call it
backup_reports and store reports in it that will be
generated by the program that we’ll create. The SQL statement to create
this table is as follows:

CREATE TABLE backup_reports
 report_date DATETIME,
 admin_name VARCHAR(100),
 report TEXT);

This is a simple table containing a key, the date of the report,
the name of the administrator generating the report, and a
TEXT column to store the report, which will be generated by
the program we’ll create in this section. Because we will be using the
admin_backup user account, we will
need to give that account user privileges to access the
server_admin database. We can do that by executing this SQL

GRANT SELECT, INSERT ON server_admin.*
TO 'admin_backup'@'localhost';

We’re now ready to create the program for the backup administrator.

Connecting to MySQL

To query a database with Ruby, we will need to establish a connection
with MySQL. Here’s the beginning part of a Ruby program to do

require 'mysql'

user = 'admin_backup'
password = 'its_password_123'
host =  'localhost'
database = 'server_admin'

    con = host, user, password, database

# Database Queries Here
# ...

rescue Mysql::Error => e
    puts e.errno
    puts e.error

    con.close if con

This excerpt of a Ruby program shows how to connect and disconnect
from MySQL. The first line is the usual line to invoke Ruby. The next
line calls the MySQL module. Then there is a list of variables that
we’ll use for connecting to the server. The names of these variables are
not important.

This is followed by a begin statement that will
include all of the interactions with the database server. The first line
establishes a new connection to MySQL. It includes the variables we
created for connecting to the server. These variables, or values for
these parameters, must be in the order shown here.

Once you have successfully connected to the database server, you
can execute SQL statements. I left out the lines for querying the
database to keep this part simple. We’ll look at that in a bit.

If the program is not successful in connecting to MySQL, the
rescue block will handle the errors and display them to the
user using puts. Regardless of whether the processing of
the queries is successful, the ensure will make sure that
the connection to MySQL is closed at the end of the program.

Querying MySQL

In the previous section, we examined the process for starting a simple
Ruby program and connecting to a MySQL server, and looked at how to
disconnect from it. Let’s now see how to query a database while
connected to MySQL or MariaDB with the Ruby API.

We’ll do a very simple query to get a list of Avocet birds from
the birds table. To do this, we’ll first create a variable
to store the SELECT statement we want to execute. Then
we’ll execute it with a query() call. Here’s how that part of the program would look:

    sql = "SELECT common_name, scientific_name
           FROM birds
           WHERE common_name LIKE '%Avocet%'"

    rows = con.query(sql)

    rows.each do |row|
      common_name = row[0]
      scientific_name = row[1]
      puts common_name + ' - ' + scientific_name

After the query(), you can see that we’re using an
each statement to go through each of the rows
of the results, storing each row in an array called row.
Then we’re temporarily storing each element of the row
array in the common_name and scientific_name
variables. We’re using puts to display each variable with a
hyphen between them and a newline at the end.

Sample MySQL/Ruby Program

Although it’s easier to discuss the components of a program in separate
pieces, it can be confusing to see how they come together. A complete
Ruby program follows that uses the MySQL/Ruby module. This program has a
very different purpose from the snippets we showed earlier. It will
check the backup directory for backup files in accordance with our
backup policy (this task was discussed in Developing a Backup Policy). The program will display to
the administrator a list of backup files for the past several days. It
will also store a report of its findings in the
backup_reports table in the server_admin
database in MySQL:


require 'mysql'

# create date variables
time =
yr = time.strftime("%Y")
mn = time.strftime("%m")
mon = time.strftime("%b")
dy = time.strftime("%d")

# variables for connecting to mysql
user = 'admin_backup'
password = 'its_password_123'
host =  'localhost'
database = 'server_admin'

# create other initial variables
bu_dir = "/data/backup/rookery/"
admin_name = "Lena Stankoska"

bu_report =  "Back-Up File Report\n"
bu_report += "-----------------------------------------------------\n"
puts bu_report

it = 0
num = 7

   # connect to mysql and query database for back-up policies
   con = host, user, password, database
   sql = "SELECT policy_id, backup_name, frequency,
          tables_include, file_format_prefix
          FROM backup_policies"
   policies = con.query(sql)

   policies.each_hash do |policy|      # loop through each row, each policy

     # capture fields in variables
     bu_name = policy['backup_name']
     bu_pre = policy['file_format_prefix']
     bu_freq = policy['frequency']

     # assemble header for policy
     bu_header = "\n" + bu_name + " (performed " + bu_freq + ")\n"
     bu_header += "(" + bu_pre + "yyyy-mmm-dd.sql) \n"
     bu_header += "-----------------------------------------------------\n"
     bu_report += bu_header
     puts bu_header

     until it > num do          # iterate through 7 back-up files (i.e., days)
        bk_day = dy.to_i - it

        # assemble backup filename
        bu_file_suffix = yr + "-" + mon.downcase + "-" + bk_day.to_s + ".sql"
        bu_file = bu_pre + bu_file_suffix
        bu_path_file = bu_dir + bu_file

        # get info. on back-up file if it exists
        if File::exists?(bu_path_file)
           bu_size = File.size?(bu_path_file)
           bu_size_human = bu_size / 1024

           bu_file_entry = bu_file + " (" + bu_size_human.to_s + "k)"
           bu_report += bu_file_entry + "\n"
           puts bu_file_entry
        it +=1
     it = 0

    # insert report text accumulated in backup_reports table
    con = host, user, password, database
    sql = "INSERT INTO backup_reports
           (report_date, admin_name, report)
           VALUES (NOW(), ?, ?)"
    prep_sql = con.prepare sql

rescue Mysql::Error => e
    puts e.errno
    puts e.error

    con.close if con

This Ruby program has comments throughout it to explain the
various sections of the code. However, I’d like to summarize it and
highlight a few parts.

First, we get the current date to create variables that we’ll use
to determine the name of back-up files. These are based on the backup
policies shown in Table 14-2.

Skipping ahead, you can see that we create a variable,
bu_report, for storing text for a report. This report is
displayed on the screen for the user as it goes along and will in the
end be inserted into the backup_reports table.

Going back to the first begin block, we execute a
SELECT to get a list of backup policies from the
backup_policies table. This table includes the file format
prefix (e.g., rookery-class-) used to
make each backup file. This is followed by the date format that each
filename uses ( yyyy-mm-dd.sql). We
store these policies in a hash named policies. Using an
each statement, we go through each policy to
form a header for each and then execute an until statement
to check for the backup files on the server for the past week. For each
backup file found, the bu_report is appended with the name
of the file and its size.

The next begin block executes an INSERT
statement to save the contents of bu_report, along with the
date and the administrator’s name in the backup_reports
table. The results for one sample row in that table follow:

*************************** 62. row ***************************
  report_id: 62
report_date: 2014-10-20 14:32:37
 admin_name: Lena Stankoska
     report: Back-Up File Report

rookery - full back-up (performed weekly)
rookery-2014-oct-20.sql (7476k)
rookery-2014-oct-13.sql (7474k)

rookery - bird classification (performed daily)
rookery-class-2014-oct-20.sql (2156k)
rookery-class-2014-oct-19.sql (2156k)
rookery-class-2014-oct-18.sql (2156k)
rookery-class-2014-oct-17.sql (2154k)
rookery-class-2014-oct-16.sql (2154k)
rookery-class-2014-oct-15.sql (2154k)
rookery-class-2014-oct-14.sql (2154k)
rookery-class-2014-oct-13.sql (2154k)

birdwatchers - full back-up (performed weekly)
birdwatchers-2014-oct-20.sql (28k)
birdwatchers-2014-oct-13.sql (24k)

birdwatchers - people (performed daily)
birdwatchers-people-2014-oct-20.sql (6k)
birdwatchers-people-2014-oct-19.sql (6k)
birdwatchers-people-2014-oct-18.sql (6k)
birdwatchers-people-2014-oct-17.sql (4k)
birdwatchers-people-2014-oct-16.sql (4k)
birdwatchers-people-2014-oct-15.sql (4k)
birdwatchers-people-2014-oct-14.sql (4k)
birdwatchers-people-2014-oct-13.sql (4k)

birdwatchers - activities (performed daily)
birdwatchers-activities-2014-oct-20.sql (15k)
birdwatchers-activities-2014-oct-19.sql (15k)
birdwatchers-activities-2014-oct-18.sql (15k)
birdwatchers-activities-2014-oct-17.sql (15k)
birdwatchers-activities-2014-oct-16.sql (15k)
birdwatchers-activities-2014-oct-15.sql (13k)
birdwatchers-activities-2014-oct-14.sql (13k)
birdwatchers-activities-2014-oct-13.sql (13k)

More Information

If you would like to learn more about using Ruby with MySQL, there’s a
manual provided
by Tomita Masahiro, the creator of the MySQL Ruby module. You might also
find Learning
(O’Reilly) by Michael Fitzgerald useful.

SQL Injection

An API program that accesses MySQL or MariaDB and is available to the
public, on the Web or from some other public access point, could be used
to attack the database server. Someone could maliciously manipulate the
data given to the web page containing a script, or the application that
sends data to the server through an API. Specifically, a hacker could
embed an SQL statement in the data to be injected into the database. This
is known as SQL injection. The purpose could be to
destroy data, retrieve sensitive or valuable information, or create a user
with all privileges and then access the server to steal

The vulnerability is related to the fact that string values are
contained in quotes. To inject SQL into a string value, a hacker just
needs to close the open quote, add a semicolon, and then start a new SQL
statement. With numeric values, one can add an extra clause without a
quote and get at data.

For an example of an SQL injection, let’s look the SQL statement
used in the PHP API section, but without a placeholder. Suppose we
embedded the $search_parameter variable inside the SQL
statement like this:

$sql_stmnt = "SELECT common_name, scientific_name
              FROM birds
              WHERE common_name LIKE '%$search_parameter%'"

Instead of entering a common name of a bird, suppose that a hacker
entered the following when using the API program, including the single

'; GRANT ALL PRIVILEGES ON *.* TO 'bad_guy'@'%'; '

That will change our SQL statement to read like this:

SELECT common_name, scientific_name FROM birds
WHERE common_name LIKE '%';

GRANT ALL PRIVILEGES ON *.* TO 'bad_guy'@'%';


This results in three SQL statements instead of just the one
intended. The hacker would receive a blank list of birds for the first.
More important, based on the second SQL statement, the system might create
for him a user account with all privileges, accessible from anywhere and
without a password. If the user account within the API program has GRANT TO and ALL privileges
for all of the databases, the bad_guy user account would be
created and have unrestricted access and privileges. The last bit of the
malicious SQL statement would just return an error because it’s incomplete
and doesn’t contain an SQL statement.

One method of preventing SQL injection with a MySQL API is to use
placeholders instead of literal values. We used these in
previous examples in this chapter. This method will isolate the data that
will be added to the SQL statement. It does this by escaping single and
double quotes. It may not seem like much, but it’s fairly

The previous SQL statements intended by the hacker will look instead
as follows if placeholders are used:

SELECT common_name, scientific_name FROM birds
WHERE common_name LIKE '%\';

GRANT ALL PRIVILEGES ON *.* TO \'bad_guy\'@\'%\';


Because the quote marks the hacker entered are escaped, MySQL will
treat them as literal values and won’t see them as the end of string
values. Therefore, it won’t start a new SQL statement when it encounters
the semicolons he entered. It won’t return the names of any birds, because
the value won’t equal any rows in the table. More important, a
bad_guy user won’t be created.


An API is very useful to create programs for users who don’t know
how to use MySQL, or users for whom you don’t want to access MySQL
directly. It provides you a much higher level of security and control over
users, especially unknown users accessing your databases through the Web.
Additionally, when MySQL doesn’t have a function to get information you
want from a database, you can write an API program to accomplish what you
want and to supplement MySQL. As a result, the APIs are very powerful
tools for customizing MySQL and MariaDB.

The API programs we reviewed in this chapter select data from a
database, and some insert or update data in a database. Some were very
simple and some were much more involved. We did very little error checking
and performed only simple tasks. Despite how basic and minimal some of the
examples were, they should be sufficient to give you an idea of how to
write an API program to connect with MySQL and MariaDB and to query a
database. The rest is a matter of knowing the related programming language
and MySQL well, and using the many API functions available to make better
applications. To that end, at the end of each section, you were given
suggestions on learning more about each API.


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 easiest to learn.

  1. Write an API program that connects to MySQL and queries the
    rookery database. 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
    select 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 membership_type column to

    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

  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
    statement 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 abilities,
    though. If it is, skip this exercise and try again in the future when
    you’re much more experienced in using the API.

    After you’ve verified that this API program makes backups
    correctly, have it connect to MySQL to record that it has run
    successfully. Use the INSERT statement to insert a row
    with the date the program ran and the name of the backup file it
    created. When finished, run the program again and check the table in
    MySQL to make sure it logged the information.

    Once you’re sure the API program works properly, add a line to
    cron or another scheduling program
    to automatically execute the backup program you wrote. Set it to run
    some time soon so you can verify it works with cron. You can remove it from cron when you’re finished.

  4. Write an API program that will select a list of bird families to
    display to the user. Devise a way for the user to select a bird family
    from the results to get a list of birds in the family. If you’re using
    an API program like PHP that may be used in a web browser, create
    links for the bird families to take them to the same API program to
    list the birds in the family selected.

    If you’re writing an API program that will be executed from the
    command line, provide the user with the family_id next to
    the name of each bird family. Instruct the user to run the program
    again, but with the family_id entered after the command
    to get a list of the birds for a family chosen. Create the program in
    such a way that if no family_id is entered, the user gets
    a list of families, but if a family_id is entered, the
    user gets a list of birds in the family. Try running the program to
    make sure it works properly.

Comments are closed.