Chapter 5. Introduction to MySQL Programming
In this part of the book, we'll discuss what you need to know to write your own programs that access MySQL databases. MySQL comes with a set of utility programs. For example, mysqldump exports the contents and structure definitions of tables, mysqlimport loads data files into tables, mysqladmin performs administrative operations, and mysql lets you interact with the server to execute arbitrary queries. Each of the standard MySQL utilities is intended as a small, focused program with a specific, limited function. This is true even for mysql, which is more flexible than the other utilities in the sense that you can use it to execute any number of different queries: It's designed with the single purpose of allowing you to issue SQL queries directly to the server and view the results.
This limited nature of the MySQL clients is not a flaw—it's by design. The programs are general-purpose utilities; they are not intended to anticipate all possible requirements you might have. The MySQL developers do not subscribe to the philosophy of writing huge, bloated programs that try to do everything you might possibly want to do (and thus end up including lots of code for many things you don't care at all about). Nevertheless, applications sometimes have requirements that cannot be handled by the capabilities of the standard clients. To deal with these cases, MySQL provides a client-programming library. This allows you to write your own programs and provides you with the flexibility to satisfy whatever specialized requirements your applications may have. By giving you access to the MySQL server, the client library opens up possibilities limited only by your own imagination.
What specific capabilities do you gain by writing your own programs? Let's examine this question in comparison to the capabilities of the mysql client and its no-frills interface to the MySQL server:
You can customize input handling. With mysql, you enter raw SQL statements. With your own programs, you can provide input methods for the user that are more intuitive and easier to use. The program can eliminate the need for the user to know SQL—or even to be aware of the role of the database in the task being performed.
Input collection can be something as rudimentary as a prompt- and value-reading loop for a command-line style interface or something as sophisticated as a screen-based entry form implemented using a screen management package such as curses or S-Lang, an X window using Tcl/Tk, or a Web browser form.
For most people, it's a lot easier to specify search parameters by filling in a form than by issuing a SELECT statement. For example, a realtor looking for houses in a certain price range, style, or location just wants to enter search parameters into a form and get back the qualifying offerings with a minimum of fuss. For entering new records or updating existing records, similar considerations apply. A keyboard operator in a data entry department shouldn't need to know the SQL syntax for INSERT, REPLACE, or UPDATE.
An additional reason to interpose an input-collection layer between the end user and the MySQL server is that you can validate input provided by the user. For example, you can check dates to make sure they conform to the format that MySQL expects, or you can require certain fields to be filled in.
You can customize your output. mysql output is essentially unformatted; you have a choice of tab-delimited or tabular style. If you want nicer-looking output, you must format it yourself. This might range from something as simple as printing "Missing" rather than NULL to more complex report-generation requirements. Consider the following report:
State City Sales ------------------------------ AZ Mesa $94,384.24 Phoenix $17,328.28 ------------------------------ subtotal $117,712.52 ------------------------------ CA Los Angeles $118,198.18 Oakland $38,838.36 ------------------------------ Subtotal $157,036.54 ============================== TOTAL $274,749.06This report includes several specialized elements:
Customized headers.
Suppression of repeating values in the State column so that the values are printed only when they change.
Subtotal and total calculations.
Formatting of numbers, such as 94384.24, to print as dollar amounts, such as $94,384.24.
For some types of tasks, you may not even want any output. Perhaps you're simply retrieving information to calculate a result that you insert back into another database table. You may even want the output to go somewhere other than to the user running the query. For example, if you're extracting names and email addresses to feed automatically into a process that generates form letters for bulk email, your program produces output. But the output consists of the messages that go to the mail recipients, not to the person running the program.
You can work around constraints imposed by the nature of SQL itself. SQL is not a procedural language with a set of flow control structures, such as conditionals, loops, and subroutines. SQL scripts consist of a set of statements executed one at a time from beginning to end, with minimal error checking.
If you execute a file of SQL queries using mysql in batch mode, mysql either quits after the first error, or, if you specify the --force option, executes all the queries indiscriminately, no matter how many errors occur. A program can provide flow control around statements so that you can selectively adapt to the success or failure of queries. You can make execution of one query contingent on the success or failure of another or make decisions about what to do next based on the result of a previous query.
SQL has very limited persistence across statements, and this carries into mysql. It's difficult to use the results from one query and apply them to another or to tie together the results of multiple queries. LAST_INSERT_ID() can be used to get the AUTO_INCREMENT value that was most recently generated by a prior statement, but that's about it.
More generally, it can be difficult to retrieve a set of records, then use each one as the basis for a complex series of further operations. For example, retrieving a list of customers and then looking up a detailed credit history for each one may involve several queries per customer. In certain cases, you may want to produce an invoice for which you need to associate the invoice header with information on the customer and on each item ordered. mysql is unsuitable for these kinds of tasks, both because you may need several queries that depend on the results of previous queries and because the tasks exceed mysql's formatting capabilities.
In general, a tool other than mysql is needed for tasks that involve master-detail relationships and have complex output-formatting requirements. A program provides the "glue" that links queries together and allows you to use the output from one query as the input to another.
You can integrate MySQL into any application. Many programs stand to benefit by exploiting the ability of a database to provide information. An application that needs to verify a customer number or check whether or not an item is present in inventory can do so by issuing a quick query. A Web application that lets a client ask for all books by a certain author can look them up in a database and then present the results to the client's browser.
It's possible to achieve a kind of rudimentary "integration" by using a shell script that invokes mysql with an input file containing SQL statements, then postprocessing the output using other UNIX utilities. But that can become ugly, especially as your task becomes more involved. It also may produce a sense of "it-works-but-feels-wrong" as the application grows by accretion into a messy patchwork. In addition, the process-creation overhead of a shell script that runs other commands may be more than you wish to incur. It can be more effective to interact with the MySQL server directly, extracting exactly the information you want as you need it at each phase of your application's execution.
With respect to our samp_db sample database that we set up in Chapter 1, "Introduction to MySQL and SQL," we enumerated several goals there that require us to write programs to interact with the MySQL server. Some of these goals are shown in the following list:
Format the Historical League directory for printing
Allow for presentation and search of the directory online
Send membership renewal notices by email
Easily enter scores into the gradebook using a Web browser
One area that we'll consider in some detail is integrating MySQL's capabilities into a Web environment. MySQL provides no direct support for Web applications, but by combining MySQL with appropriate tools, your databases can be accessed easily over the Web. You can specify queries using your Web server and report the results to a client's browser.
There are two complementary perspectives on the marriage of MySQL and the Web:
Your main interest is your database, and you want to use the Web as a tool to gain easier access to your data. The place of a database in such a scenario is explicit and obvious because it's the focus of your interest. For example, you can write Web pages that allow you to see what tables your database contains, what each one's structure is, and what its contents are. You're using your Web server to enhance your access to MySQL. This is the point of view a MySQL administrator probably would take.
Your primary interest may be your Web site, and you may want to use MySQL as a tool for making your site's content more valuable to the people who visit it. For example, if you run a message board or discussion list for visitors to the site, you can use MySQL to keep track of the messages. In this case, the role of the database is more subtle, and visitors may not even be aware that it plays a part in the services you have to offer. You're using MySQL to enhance the capabilities of your Web server. This is the point of view a Web site developer probably would take.
These perspectives are not mutually exclusive. For example, in the Historical League scenario, we want to use the Web as a means for members to gain easy access to the contents of the membership directory by making entries available online. That is a use of the Web to provide access to the database. At the same time, the League's Web site is somewhat underdeveloped, so adding directory content to the site increases the site's value to members. That is a use of the database to enhance the services provided at the site.
No matter how you view the integration of MySQL with the Web, the implementation is similar. You connect your Web site front end to your MySQL back end, using the Web server as an intermediary. The Web server sends a query from the user to the MySQL server, retrieves the query results, and then conveys them to the client for viewing in a browser.
You don't have to put your data online, of course, but often there are benefits to doing so, particularly in comparison with accessing your data via the standard MySQL client programs:
People accessing your data through the Web can use the browser they prefer, on the type of platform they prefer. They're not limited to systems that the MySQL client programs run on. No matter how widespread the MySQL clients are, Web browsers are more so.
A Web interface can be made simpler to use than a standalone command-line MySQL client.
A Web interface can be customized to the requirements of a particular application. The MySQL clients are general-purpose tools with a fixed interface.
Dynamic Web pages extend MySQL's capabilities to do things that are difficult or impossible to do using the MySQL clients. For example, you can't really put together an application that incorporates a shopping cart using just MySQL clients.
Any programming language can be used to write Web-based applications, but some are more suitable than others. We'll see this in "Choosing an API."