Chapter 5: Programming with MySQL

Overview

This chapter is not going to teach you how to program. Too many books try to teach both MySQL and a programming language and instead end up doing incomplete jobs of both. This book assumes you're already a competent programmer, you're going to learn, or you're going to focus only on the database administrator (DBA) role, and are not interested in programming.

You can begin to tap into the real power of a database when it becomes part of a larger information system, with fully functional applications adding their own value to the system. A news website, for example, needs tools to add and sort the news articles, to display them on a website, and to track the most popular stories. Most journalists have no interest in learning Structured Query Language (SQL), however, so they need a well-designed interface to link them to the database. This interface could be a web page with a Hypertext Markup Language (HTML) form, with a submit button that calls a script to run an INSERT statement. Or the interface could be a news feed that takes articles from the newspaper's QuarkXPress system and automatically adds them to the database.

Another information system could involve an application for financial advisors, where the backend is fed with the latest stock and currency prices, so the advisors can access and analyze the information to monitor trends for their clients.

The possibilities for information systems are endless. What these scenarios have in common is that they include a developed application to add extra levels of logic that MySQL cannot supply. In theory, you can use any programming language to develop applications. Languages commonly used are Java, C, PHP, Perl, C++, Visual Basic, Python, and Tcl, which mostly have well-developed Application Programming Interfaces (APIs) for interfacing with MySQL. The appendixes in this book contain APIs for most of these programming languages.

Throughout this chapter, all examples are in PHP—not because you should all be using PHP, but simply because so many of you already do, because its syntax is familiar to anyone with a C-like background (C, Perl, or C++), and because it's simple enough for other programmers to follow. It's the programming principles that are important, however—not the syntax. All code is extensively commented in this chapter so that you can follow it no matter what language you are familiar with or your level of competency.

Featured in this chapter:

• Using persistent connections

• Making your code portable and easier to maintain

• Assessing the database's vs. the application's workload

• Exploring the application development process