ISP-In-A-Box: The $500 Mac mini (Chapter IX, Building a Data-Driven Web Site: Part I)

Our project for today and tomorrow is to construct a data-driven web site (see inset) using Apache, PHP, PhpMyAdmin, and MySQL. If you’ve followed along the past couple weeks and built an ISP-In-A-Box with us using your new Mac mini or any other Mac running a current version of Mac OS X, then you’re ready to go. If you’re from the Windows XP universe, you can do much the same thing using our Webifying XP tutorial from last year. Or, if you are using any computer or web hosting service that supports Apache, PHP, PhpMyAdmin, and MySQL, then you’re all set. Otherwise, pick one of the above, install the pieces, and then come back and join the party.

Game Plan. The plan of attack for today is to download and install my sample working data-driven web site on your server. You can look at a working version of what we are going to install here. Then tomorrow we’ll customize the web site with your content. We’ll use PhpMyAdmin to edit the MySQL database which actually stores all of the content for your new web site.

Prerequisites. In order to follow along on this project, you should already have bought your Mac mini and activated the Apache Web Server. Before you can actually build the data-driven web site, you must install MySQL and PHP and PhpMyAdmin. For your computer’s sake, we’d prefer you read the entire ISP-In-A-Box series (now in PDF format), but that, of course, is up to you. Before we begin, make certain that your web server and MySQL both are running. If you can’t remember the procedures to start them up, reread the tutorials referenced above.

Overview. As the name implies, a data-driven web site is one in which the content for the site is obtained from a database. The power and real beauty of PHP is that, when someone accesses your web site, Apache directs PHP to swing into motion and actually execute all of the instructions stored in your .php web page. In our case, the PHP instructions for the web site tell your computer to access a MySQL database and pour all the contents of the database into a web page template which we have constructed. PHP, however, is sufficiently flexible to allow you to build something much more sophisticated using virtually any database you or your company already has. That includes Oracle, Sybase, Informix, filePro, InterBase, mSQL, MySQL, PostgreSQL, and Microsoft SQL Server databases of any flavor as well as any other database with an ODBC connector. And the database need not reside on your home computer although in our case it will. Unless you will have a site with incredibly high transaction-oriented database processing activity such as a busy eCommerce web site which manages inventory as well as sales, MySQL databases are not only adequate but in many cases can run circles around expensive commercial databases such as Oracle and Informix. The moral of the story is don’t assume MySQL is going to clunk along just because it happens to be free. You will be amazed by its performance. Several of our beach webcam sites (pawleys.org and gardencitybeach.org) have thousands of visitors per day and use links to MySQL databases to look up tide and sunrise/sunset data from a huge 100-year MySQL table. The performance is virtually instantaneous, and you will see similar results. In the case of our sample web site, we’re only going to be accessing a hundred records or so which wouldn’t tax any database, but the idea here was to give you a model for future projects that might include large inventories of significant size. Using MySQL, the performance results will be almost identical with an indexed 100MB table as they are with our same database today. So let’s get started.

Installing the Sample Web Site. We’ve built a tarball (nerd.tar) containing all of the pieces you will need to construct the data-driven web site on your own computer. Just click here to download the file to your desktop which should decompress into a folder called nerd. If not, double-click on the nerd.tar file on your desktop to decompress it into a folder. Now there are three simple steps to complete today’s project. First, we need to create a MySQL database in which to store our website table. Then we need to import the sample data into the MySQL database. And finally we need to move our web site objects to their final home on your computer.

Creating a MySQL Database. To create the MySQL database in which to store our website table, we are going to use PhpMyAdmin which we installed last week. Using the web browser on your server, go to the following web site: http://localhost/php/. You should have a two-panel PhpMyAdmin display. If not, click on Home to open both panels. In the right-column box under the words "Create New Database" type mundy in lower-case letters. If there is a collation option in your version of PhpMyAdmin, choose UTF8_bin. Then click the Create button. MySQL should report that the mundy database was created. [If you’re doing this through a web hosting service instead of using your own server, then access cPanel for your web site, click on the MySQL icon, find the Db: section of the page, type in mundy for the database name, and click the Add Db button. To complete part (b) below, just click on the PhpMyAdmin link at the bottom of the screen you’re in.]

Populating the MySQL Database. Press the F11 function key to temporarily hide your web browser, and then Double-Click on the nerd folder to open it. Now Ctrl-Click on the website.sql file and choose Open With Other and pick the TextEdit application. When the file opens with TextEdit, click Edit in the TextEdit menu bar and then click Select All. All of the text in website.sql should now be highlighted. If not, try again. Now click Edit in the TextEdit menu bar again and then click Copy. This puts a copy of the website.sql file on your clipboard. Now press F11 to reopen your web browser. Double-click on the mundy database in the left column of PhpMyAdmin which will open a new window in the right column. Newer version of PhpMyAdmin have a tabbed interface in the right panel. If you see a SQL tab, click on it. Now click in the box immediately below Run SQL query/queries… to highlight it. Now choose Edit from your browser’s menu bar and click Paste to paste a copy of website.sql into the highlighted box in PhpMyAdmin. You should see several of the bottom lines of text from the file ending with an entry for "Atlanta gas prices." If you don’t see this, clean out the text box and repeat the steps above. Once all the text has been pasted into the text box, click the Go button immediately below the Run SQL Query box. MySQL will whirr away for a couple seconds and then report that the website table has been imported. Now close your web browser for the time being. And close the nerd folder on your desktop.

Bringing the Site On Line. Putting our data-driven web site into production is a piece of cake now. Using Finder, click on your local hard disk and move to the /Library/Webserver/Documents directory. Now click-and-drag the nerd folder from your desktop into the Documents directory you opened with Finder. Once the folder has been moved, you should be able to access your new web site by going to the following address with your web browser: http://localhost/nerd/. Or you can access it on the Internet using your new domain name with the syntax: http://yourdomain/nerd/. If you’d prefer to access the site as your default web site at http://yourdomain, then the contents of the nerd folder need to be copied or moved into /Library/Webserver/Documents and all the other files beginning with the word "index" (except index.php) need to be moved out of the Documents folder (either into a new folder for safekeeping or to the trash). You don’t need to move the website.sql file since we only needed it to import the original data into MySQL. [If you’re doing this through a web hosting service instead of using your own server, then you’ll need to make one minor change in the index.php file for this application. Open index.php with an editor and search for mundy. Most ISP’s use a MySQL database syntax like this: youraccountname_mundy. So just append your account name and then an underscore character before the existing database name mundy, and save the file.]

Meet us back here tommorow to learn all about customizing your new web site with your very own content. In the meantime, write down your favorite 100 web sites with appropriate links. We’ll need them tomorrow.