HTML, XHTML and CSS All-In-One for Dummies - Andy Harris [235]
$filesArray = preg_grep(“/txt$|dat$/”, $filesArray);
For more on regular expressions, check Book IV, Chapter 6 as well as Chapter 4 of this book.
Chapter 7: Connecting to a MySQL Database
In This Chapter
Building the connection string
Sending queries to a database
Retrieving data results
Formatting data output
Allowing user queries
Cleaning user-submitted data requests
Data has become the prominent feature of the Web. As you build more sophisticated sites using XHTML and CSS, you will eventually feel the need to incorporate data into your Web sites. You can do a certain amount of data work with the basic data structures built into PHP. Increasingly, Web sites turn to relational database management systems (RDBMSs) to handle their data needs. A RDBMS is a special program which accepts requests, processes data, and returns results.
This chapter assumes you have a database available and also that you have some basic knowledge of how SQL (Structured Query Language; the language of databases) works. If you’re unfamiliar with these topics, please look over Book VI, which describes using data in detail.
Retrieving Data from a Database
PHP programmers frequently use MySQL as their preferred data back end for a number of good reasons:
♦ MySQL is open source and free. Like PHP, MySQL is open source, so PHP and MySQL can be used together (with Apache) to build a very powerful low-cost data solution.
♦ MySQL is very powerful. MySQL’s capability as a data program has improved steadily, and it is now nearly as capable as commercial tools costing thousands of dollars. (And it is better than many that cost hundreds of dollars.)
♦ PHP has built-in support for MySQL. PHP includes a number of functions specifically designed to help programmers maintain MySQL databases.
♦ You probably already have MySQL. If you installed XAMPP, you probably already have an installation of MySQL ready to go. Check Book VIII, Chapter 1 for installation details.
♦ MySQL was designed with remote control in mind. MySQL is meant to be managed from some other program (like the code you write in PHP). It’s not designed with a user interface (like Access has), but it’s designed from the beginning to be controlled through a programming language like PHP.
Before diving into details, here’s an overview of how you get information to and from a MySQL database:
1. Establish a connection.
Before you can work with a database, you must establish a relationship between your PHP program and the database. This process involves identifying where the database is and passing it a username and password.
2. Formulate a query.
Most of the time, you’ll have some sort of query or request you want to pass to the database. For example, you may want to see all the data in a particular table, or you may want to update a record. In either case, you use SQL to prepare a request to pass to the database.
3. Submit the query.
After you build the query, you pass it (through the connection) to the database. Assuming that the query is properly formatted, the database processes the request and returns a result.
4. Process the result.
The database returns a special variable containing the results of your query. You’ll generally need to pick through this complex variable to find all the data it contains. For example, it can contain hundreds of records. (For more on records, see the upcoming section “Processing the results.”)
5. Display output to the user.
Most of the time, you’ll process the query results and convert them to some sort of XHTML display that the user can view.
As an example, take a look at contact.php in Figure 7-1.
Figure 7-1: This program gets all the contact data from a database.
The contact.php program contains none of the actual contact information. All the data was extracted from a database. Here’s an overview of the code:
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>