Building a Database-Driven Web Site Using PHP and MySQL

by Kevin Yank of SitePoint.com

Part 4: Publishing MySQL Data on the Web

This is it -- the stuff you signed up for! This is the week we take information stored in a database and display it on a Web page for all to see. So far we've installed and learned the basics of MySQL, a relational database engine, and PHP, a server-side scripting language. In this week's installment, we see how to use these two new tools together to create a true database-driven Web site!

A Look Back at First Principles

Before we leap forward, it's worth a brief look back to remind ourselves of the goal we are working toward. We have two powerful, new tools at our disposal: the PHP scripting language, and the MySQL database engine. It's important to understand how these two are going to fit together.

The whole idea of a database-driven Web site is to allow the content of the site to reside in a database, and for that content to be dynamically pulled from the database to create Web pages featuring it for people using a regular Web browser to view. So on one end of the system you have a visitor to your site using a Web browser, loading http://www.yoursite.com/, and expecting to view a standard HTML Web page. On the other end you have the content of your site sitting in one or more tables in a MySQL database that only understands how to respond to SQL queries (commands).

The PHP scripting language is the go-between that speaks both languages. Using PHP, you can write the presentation aspects of your site (the fancy graphics and page layouts) as "templates" in regular HTML. Where the content belongs in those templates, you use some PHP code to connect to the MySQL database and -- using SQL queries just like those you used to create a table of jokes in Part Two -- retrieve and display some content in its place.

Just so it's clear and fresh in your mind, this is what will happen when someone visits a page on our database-driven Web site:

• The visitor's Web browser asks for the Web page using a standard URL.
  
• The Web server software (Apache, IIS, or whatever) recognizes that the requested file is a PHP script, and so interprets it using its PHP plug-in before responding to the page request.
  
• Some PHP commands (which we have yet to learn) connect to the MySQL database and request the content that belongs in the Web page.
  
• The MySQL database responds by sending the requested content to the PHP script.
  
• The PHP script stores the content into one or more PHP variables, then uses the now-familiar echo function to output it as part of the Web page.
  
• The PHP plug-in finishes up by handing a copy of the HTML it has created to the Web server.
  
• The Web server sends the HTML to the Web browser as it would a plain HTML file, except instead of coming directly from an HTML file, the page is the output provided by the PHP plug-in.

Article Index Introduction and Contents Part 1: Installation Part 2: Getting Started with MySQL Part 3: Getting Started with PHP Part 4: Publishing MySQL Data on the Web   Challenge Solution Buy this book in print format now

Connecting to MySQL with PHP

SitePoint.com is a fast growing Web Developer Community. Kevin Yank is the Editor of the SitePoint TechTimes, a fresh, technically oriented newsletter for the serious Webmaster.