Accessing Your MySQL Database from the Web with PHP
Learn how to access a MySQL database from the Web using PHP, including how to read from and write to the database and how to filter potentially troublesome input data.
This chapter is an excerpt from the new 4th Ed. of "PHP and MySQL Web Development" authored by Luke Welling and Laura Thomson, published by SAMS, ISBN 0672329166, Copyright 2009 by Pearson Education, Inc. For more info, please visit www.informit.com. Safari Books Online subscribers can read it here: http://safari.informit.com/9780672329166.
Previously, in our work with PHP, we used a flat file to store and retrieve data. When we looked at this file in Chapter 2, “Storing and Retrieving Data,†we mentioned that relational database systems make a lot of these storage and retrieval tasks easier, safer, and more efficient in a web application. Now, having worked with MySQL to create a database, we can begin connecting this database to a web-based front end.
In this chapter, we explain how to access the Book-O-Rama database from the Web using PHP. You learn how to read from and write to the database and how to filter potentially troublesome input data.
Key topics covered in this chapter include
- How web database architectures work
- Querying a database from the Web using the basic steps
- Setting up a connection
- Getting information about available databases
- Choosing a database to use
- Querying the database
- Retrieving the query results
- Disconnecting from the database
- Putting new information in the database
- Using prepared statements
- Using other PHP-database interfaces
- Using a generic database interface: PEAR MDB2
How Web Database Architectures Work
In Chapter 8, “Designing Your Web Database,†we outlined how web database architectures work. Just to remind you, here are the steps:
1. A user’s web browser issues an HTTP request for a particular web page. For example, the user might have requested a search for all the books written by Michael Morgan at Book-O-Rama, using an HTML form. The search results page is called results.php
2. The web server receives the request for results.php, retrieves the file, and passes it to the PHP engine for processing.
3. PHP engine begins parsing the script. Inside the script is a command to connect to the database and execute a query (perform the search for books). PHP opens a connection to the MySQL server and sends on the appropriate query.
4. The MySQL server receives the database query, processes it, and sends the results—a list of books—back to the PHP engine.
5. The PHP engine finishes running the script. This usually involves formatting the query results nicely in HTML. It then returns the resulting HTML to the web server.
6. The web server passes the HTML back to the browser, where the user can see the list of books she requested.
Now you have an existing MySQL database, so you can write the PHP code to perform the preceding steps. Begin with the search form. The code for this plain HTML form is shown in Listing 11.1.
Sign up for ITworld's Daily newsletter
Follow ITworld on Twitter @IT_world
On Twitter now
PHP
Powered by Twitter
Esther Schindler
If the comments are ugly, the code is ugly
claird
SVG a graphics format for 21st century
pasmith
Take Chrome OS for a test spin
Sandra Henry-Stocker
Solaris Tip: Have Your Files Changed Since Installation?
jfruh
Android fragments vs. the iPhone monolith
mikelgan
What Gizmodo missed about the Pro WX Wireless USB disk drive
Where Google Chrome security fails: the password
I heard mention that the Chrome OS will have some sort of encryption available a la bitlocker. If it's possible to encrypt personal data using another password or key, then it may have potential for very secure data.... And Ubuntu has an 'encrypt home directory' option, perhaps google should follow suit.
- Dann
Join the conversation here
Quick, practical advice for IT pros. Made fresh daily.
Want to cash in on your IT savvy? Send your tip to tips@itworld.com. If we post it, we'll send you a $25 Amazon e-gift card.














Thanks for a good article !
Its good to see pages that are concise, informative and have proper examples that allow the reader to follow on what is being said and dont require a pint of blood for access. Definitely made one happy camper here ... thanks for the article.-Someone thats slightly less confused about PHP and DB interaction