Serve It Up (in OS X 10.1)
Sports scores displayed in real time, auctions that run themselves, catalog prices updating instantaneously across an entire Web site--none of this dynamic interactivity is possible with plain-vanilla HTML. Today's best Web sites are full-blown applications built on top of sophisticated databases.
But until recently, Mac users were at a disadvantage when it came to building Web applications such as these. The most popular dynamic Web tools didn't run on the Mac, so we had to set up a second computer to use them. Meanwhile, the tools that did run on the Mac were expensive and not as widely supported as those that ran on powerful Unix servers.
Well, that's no longer the case. With the release of OS X, Mac users can at last build, test, and deploy dynamic Web sites--all from a single machine. Now, the same database tools used to build world-class Internet sites such as those from NASA, MP3.com, and Yahoo Finance are available for free to OS Xusers. As a result, Mac users no longer have to take a backseat to Unix folks when it comes to developing dynamic Web sites.
In this article, we'll show you how to get up and running with one of the Web-publishing world's favorite combinations: the Apache Web server, MySQL database server, and PHP scripting language. These three tools work in concert to let Mac-based Web developers create dynamic sites with -the same tools that Unix pros use. In fact, with a world-class server OSat its foundation, the Mac can be more than just a development machine--it's ready to be a live hosting platform.
To follow these instructions, you'll need OS X 10.1.x. You should note that servers set up under OS X 10.1 may not work when converted to OS X 10.2--known more commonly as Jaguar. (For details on setting up this environment with OS X 10.2, see in November 2002 issue of Macworld .)
Whether you're building the next eBay or creating a simple contact database that employees can access over the Web, you'll need to enable three major components on your OS X machine before you can add dynamic content to your pages.
Apache To deploy any Web site, you need a Web server to process page requests from users and return the appropriate files. And it just so happens that the world's most popular Web server is built into OS X. The Apache Web server is available for nearly every platform under the sun. Its fast performance, flexibil-ity, and low price (it's free) make it a strong choice for serving sites of any size.
MySQL You'll also need a database server, which hosts data sets and responds to queries from other programs (such as scripted Web pages).
MySQL is one of the most popular open-source database servers available. A MySQL server can host a collection of databases--each of which contains a set of related tables filled with data. Because it's compact, very fast, and compatible with the industry-standard Structured Query Language, MySQL is particularly well suited to Web use--and like Apache, MySQL is free.
PHP Your Apache and MySQL servers alone won't generate dynamic content. Because HTML is a markup language, not a programming language, it has no direct way of communicating with databases. This means you need a "diplomat"--a scripting language that can communicate with both servers to fuse static HTML templates and data from your database records. For this component, we'll turn to PHP, a scripting language included with OS X.
PHP lets developers add interactivity to Web pages by interspersing programming instructions with HTML. When a user requests a Web page that includes PHP code, the Web server's PHP module executes that code and inserts the results into the HTML page that's returned to the user. As a result, if you view the source of any PHP page on the Internet, you won't see any PHP code--it was all executed and converted into plain text or HTML before it was sent to you.
And you no longer have to be a programming wizard to add PHP scripts to your Web pages. Both Macromedia Dreamweaver MX ($399; 800/470-7211, www.macromedia.com) and Adobe GoLive 6 ($399; 800/833-6687, www.adobe.com) now come with PHP support built in.
Putting It Together Apache, MySQL, and PHP (often referred to as AMP when used in combination) are each independent packages--Apache can work with other scripting languages, just as PHPcan work with other databases. However, there are advantages to using them together. Because each is open source, their development teams work closely to make sure the three are tightly integrated. For example, PHP has more built-in functions for communicating with MySQL than with any other type of database.
Used in conjunction, Apache, MySQL, and PHP seamlessly collaborate to create dynamically generated Web pages. When Apache receives a request for a new Web page, it sends any included PHP code to the PHP interpreter for processing. If the PHP code includes database requests, the PHP module passes those on to the MySQL server (which typically runs on the same computer). Once the PHPmodule gets the results back from MySQL, it inserts them into the waiting HTML template and returns the finished document to Apache, which in turn hands it back to the client.
What You'll Need Building a basic system like the one we just described isn't difficult. Since OS X comes with the Apache Web server and PHP support, you're already a portion of the way there. And although MySQL isn't included with your Mac, it can be installed in minutes (though you will need some technical know-how).
For the sake of this article, we'll assume that you already have OS X 10.1, basic HTML skills, and a text-based editor such as Bare Bones Software's BBEdit ($119; 781/687-0700, www.barebones.com). You can also use the code mode built into Dream-weaver or GoLive. You'll use OS X's Terminal application to initially set up and configure the software, but don't worry--you won't need it to build or manage your databases.
If you've purchased Adobe GoLive 6, you can skip our instructions for installing your AMP component. GoLive 6 comes with a precon-figured AMP setup--just run the installers (located on the CD pasted to the back of the program's Web Workgroup Server User Guide) and follow the Read Me file's instructions. Of course, if you don't have GoLive 6, you can have the exact same setup for free--you'll just have to install a bit of the plumbing yourself.
Step 1: Set Up a Web Server
Although Apple builds Apache and support for PHP into OS X, it doesn't actually turn these features on. You'll have to do that part yourself. Here's how:
Turn PHP Support On To activate Apache's PHP module, you'll first need to modify the Apache configuration file. Since the configuration file is hidden from the Finder, you'll have to make a trip to the command line.
Open Terminal (Applications: Utilities: Terminal). Before you make any changes, you'll want to create a backup of your Apache configuration file--just in case something goes wrong. To do this, type the following commands into the Terminal window:
Enter your administrator password when prompted, and then type
This command opens your Apache configuration file in pico, a Unix text editor. In pico, press control-W to open the Find command, and then type php. When you hit the return key, your cursor should land on the line #LoadModule php4_module libexec/httpd/libphp4.so. Using the left-arrow key to move your cursor, remove the # character at the beginning of this line.
You need to find and change two additional instances of that string. Press control-W and then the return key repeatedly until you find the lines: AddModule mod_php4.c and then AddType application/x httpd-php .php. Remove the # character from the beginning of these lines as well.
Press control-W a third time and search for the string x-tar. When you land on it, move your cursor to the end of the line, press return, and then add the line AddType application/x-httpd-php .php.
Once you've made these three changes, save the file by pressing control-O and then the return key. Press control-X to exit pico.
Apache should now automatically load the PHP module on start-up. When the Web server encounters pages ending in .php, it'll send them to the PHP module for interpretation.
Start Apache With PHP enabled, you're ready to flip the switch on your Web server. Open your System Preferences and select Sharing. Click on the File & Web tab, and under the Web Sharing section, click on Start. (If Web sharing was already on, you'll need to turn it off and then back on to activate the changes you've made.)
Testing To find out whether the PHP module has been correctly activated, open your favorite text editor and create a new document. (If you're using Apple's TextEdit, make sure you've turned off the program's Rich Text features, in Preferences, first.) In your blank document, type <? phpinfo(); ?>. Save the file as test.php in the Sites folder in your user's folder.
Open your Web browser, and type in the address http://localhost/~username/test.php, where username is your short name on the OS Xmachine. (Because PHP is a server-parsed language, nothing will happen if you open a PHP file in your Web browser via the File menu or by double-clicking. To execute PHPpages, you must access them through a PHP-enabled Web server.)
Your browser should display tables packed with arcane data about your new PHP environment. If you see only one line of raw PHP code, repeat this step.
Step 2: Set Up a Database Server
With your PHP-enabled Web server up and running, you're ready to install a MySQL database server. There are a number of MySQL installers for OS X floating around on the Web; Marc Liyanage offers the easiest one (www.entropy.ch/software/macOS X/mysql/). Download the 3.2x version for OS X 10.1 (unless 4.0 is out of beta by the time you read this). While you're at Liyanage's site, download the mysql-startupitem installer as well--you'll need it later.
Install MySQL To use MySQL, you must first create an account on your system for the program to use. Go to System Preferences, select Users, and click on New User. In the Identity pane, name your new user MySQL. Click on the Password tab and enter a password for your MySQL user. Make sure the Allow User To Administer This Computer option is disabled.
To install the database server, unpack the mysql-3.2X-jaguar.pkg.tar.gz on your desktop, and then double-click on the resulting .pkg file. Follow the installation instructions.
Once installed, the database needs to be initialized and launched. This requires another trip to Terminal. Open a Terminal window and type:
Enter your system admin password. (If Terminal complains that it can't resolve your host name--this can happen behind some firewall configurations--retype the line above like so: sudo ./scripts/mysql_install_db --force. If you're building a production server, you may need to contact your system administrator for assistance in resolving the host-name issue.) Then type:
Your MySQL server should now be activated.
Security and Start-Up Before moving on to building your database, there are a few server details you'll need to address. The most important is security.
By default, MySQL is not password-protected, so you should add a root password with any MySQL installation. To set your password, type the following command into the Terminal window (replacing newpassword with a suitably difficult password):
Leave Terminal open when you're done.
Next, you'll want to set your MySQL server to launch automatically when the system boots (so you don't have to manually start it from Terminal each time). You can do this with mysql-startupitem, which you downloaded earlier. To install it, simply double-click on the mysql-startupitem.pkg file on your desktop and follow the directions.
Test To try out your MySQL installation, return to the Terminal window and type mysql -u root -p. Enter the MySQL password. At the mysql> prompt, type show databases;.
Under the Database heading, you should see two listings: mysql and test. (These databases were created automatically during the MySQL initialization process.)
Congratulations! Your OS X Mac is now officially a MySQL server. Type exit to leave the MySQL monitor.
Step 3: Create a MYSQL Database
With the three major components now in place (Apache, PHP, and a MySQL database server), your OS X machine has everything it needs to serve up dynamic content--everything except the content, that is. Your next task, then, is to build a MySQL database (and its related tables) for your data.
Install phpMyAdmin While MySQL databases can be administered completely from the command line, most of us would prefer to work in a more user-friendly environment. There are many commercial products on the market that help you create and administer databases visually, but none is as economical and widely supported as phpMyAdmin, a free Web-based interface for your MySQL server.
In addition to reflecting the structure of any MySQL database running on your system, phpMyAdmin lets you create new databases, manipulate tables within those databases, and manipulate fields within those tables. You can also use it to add, change, and delete the data in your databases.
To install phpMyAdmin, go to the phpMyAdmin Project Web site (www.phpmyadmin.net) and download the latest stable version of the software in PHP (not PHP3) form. Let StuffIt unpack the archive on your desktop, locate the new phpMyAdmin folder, and rename it simply phpmyadmin. This will make accessing its URL later much easier. Move this folder into your Sites folder.
Because phpMyAdmin gives total control over your databases to anyone who can access them, it's critical that you password-protect this part of your site as well. Open the config.inc.php file (located in your phpmy-admin folder) in a text editor. Scan down the page for the line starting with \$cfg['PmaAbsoluteUri;'], and enter http://localhost/~username/phpmyadmin/ between the apostrophes to the right of this line.
Continue scanning until you find the line that reads \$cfg['Servers'][$i]['auth_type'] = 'config';, and replace config with http. In the next line, make sure the user string is set to root. Move one more line down to the password string, and enter your database's root password between the apostrophes. Save and close the file.
Create a Database and Table Once phpMyAdmin is installed, you can use it to begin building a new MySQL database. To access phpMyAdmin, open your Web browser and type in the URL http://localhost/ ~username/phpmyadmin/index.php.
When prompted, enter the user name and the password you added to the config.inc.php file. You'll find yourself in the database administrator. (If you receive an error message about a problem with charset conversion, return to the config file and locate the line
Change TRUE to FALSE, and try again.)
To start a new database, click on Home (in the upper left corner of your screen). Enter a name for your database ("sample," for example) in the text field below the Create New Database heading, and then click on the Create button. In the resulting window, create a new table called Contact, with five fields. Then click on Go.
The next phpMyAdmin screen lets you define the names and characteristics of the fields in your new Contact table. While you don't have to fill out every field right now, a few are crucial.
Every MySQL table must have at least one column (usually the first) in which every row has a unique value. This numeric identifier (ID) field eliminates the possibility of ambiguous query results. To create this ID column, click in the first row of your empty table and enter contacts_ID under the Field heading. Because this field is going to store only ID numbers, change the Type pop-up menu to INT, for Integer. Next, choose Auto_Increment from the Extra column's pop-up menu. This assigns the next available number to each newly added contact record. Then, because your ID field will be the primary link--or key--between this table and others, select Primary. Any of the settings not mentioned here can be left at their default values.
Now enter field titles for the remaining four rows of your Contact database: fname (for First name), lname (for Last Name), email, and phone. Set the type for each to VARCHAR, to signal that these rows will be filled with character strings of variable data. Because it's unlikely that any of these character strings will be longer than 32 bytes--or characters--long, enter this number in the Length/Values column. When you're done, click on Save to commit your new columns to the database (see "Setting the Table"). Your database and its tables will appear in a list on the left side of your window. To add additional tables, select the database name from the list and repeat this process.
Entering Data Now that you have a MySQL database with at least one table, you're ready to start populating it with data. But while phpMyAdmin excels at building the structure of your databases, its one-size-fits-all approach makes it less useful for actual data entry. Instead, most database developers will probably want to use PHP and HTML to build a Web interface that provides some custom means of entering and editing data.
Nevertheless, phpMyAdmin does let you enter records directly, which is especially useful for testing purposes. Here's how:
From the list on the left of your screen, select the table you want to populate. Click on the Insert link at the top of the page, and then enter the values for your first record in the empty text fields. (Don't enter anything into the ID field. Since it's set to Auto_Increment, it should take care of itself.) Select the Insert Another New Row option, and click on Go to add another record. After you've entered a few records, click on the Browse tab to peruse your record set.
Creating a Secure User Until now, we've been working as the root user with our database. But in practice, it's safer to create multiple users, each with limited database privileges. For example, you may want employees to be able to enter data into existing tables but not to alter the structure of those tables or build new ones.
To create a separate MySQL user from within phpMyAdmin, click on Home and then select Users. Under the Add A New User section, select Host option and type localhost. Enter a user name and password. Under Privileges, select the appropriate options. To let users alter data but not your database's structure, for example, activate the Select, Update, Insert, and Delete options. (For a full explanation of privileges, see the MySQLdocumentation.) Click on Go to execute your changes.
Click on Home and then on Reload MySQL to make your user changes take hold. Now you can tell any PHP script to connect as this user, and that script's scope of behaviors will be limited to the actions you specified. For even tighter control, you can restrict a user's permissions to a specific database by clicking on the Grants section in the Users table.
This is only a brief introduction to the powerful and broad capabilities of MySQL. With some scripting know-how, you can create impressively complex and specialized database systems to answer almost any demand. To help you quickly get started with MySQL, we've created a sample database and a set of accompanying PHP scripts that you can download and modify for your own purposes (see "A Jump-Start").
Step 4: Add Data to Your Web Site
You now have everything you need to build a powerful, highly sophisticated database. But this data won't flow into and out of your Web pages by itself--that's where PHP comes in.
The last stage in creating dynamic content is building PHP scripts that interact with your MySQL server to extract or insert the data you want and format it appropriately. These PHP scripts seamlessly insert their collected data into an HTML layout and send back a finished Web page.
Like any scripting language, PHP is not something you're going to learn overnight. In case you don't have the time or the desire to learn the ins and outs of a new scripting language, Macromedia and Adobe offer an easier solution. Dreamweaver MX and GoLive 6 both have tools that help you automatically generate PHP scripts. These tools won't help you build your database--you still need phpMyAdmin or something similar for that--but they do offer a more intuitive, user-friendly interface for building dynamic Web sites on top of your preexisting databases.
Like WYSIWYG HTML tools, these applications can create code that is bulkier or less elegant than what you might write by hand. But you may find their ease of use worth the added bulk. (For instructions on setting up GoLive 6 to interact with a MySQL database, see "Go Dynamic with GoLive," Macworld November, 2002.)
Transferring Your Database
If you're using your OS X machine as a development and testing environment, and you plan to hand your finished database project over to a client or ISP for hosting, you'll eventually need to transfer your MySQL database to the client's Web server. This is where phpMyAdmin's Export features come in handy. Here you can choose to export the structure of your tables, your data, or both. You can also choose an export format--such as comma-delimited, XML, or Excel-ready data.
To transfer an entire database from your Mac to another MySQL host, select your database from the list on the left-hand side of the screen, and then click on the Export tab. Select the Structure And Data option and then the Save As File option. Click on Go. Save the resulting file to your hard drive with a .sql extension. To load your data on the other MySQL host, bring up its copy of phpMyAdmin and go to the SQL tab. Click on Browse and then navigate to your .sql file. The database should be re-created on the new host. Make sure that any users and grants you set up on the development machine match those on the live server (these may need to be tweaked both in phpMyAdmin and in your PHP scripts). If all users and permissions are identical on both machines, your PHP scripts should work perfectly when uploaded.
The Last Word
Whether you use your Mac as a live database server or just as a development environment before you transfer your dynamic site to a big Unix server, this introduction to using Apache, MySQL, and PHP is just the beginning--we've barely scratched the surface of what you can do. The sheer number of live demos, downloadable scripts, and free documentation out there is nothing short of awe inspiring. And thanks to OS X, Mac users are once again in the thick of it.
Setting the Table phpMyAdmin gives you total administrative control over your MySQL databases and tables. The table-creation interface lets you specify the types of information your table will include and how the table will relate to others in the database.
SCOT HACKER is the Webmaster at U.C. Berkeley's Graduate School of Journalism, where he teaches classes on PHP/MySQL Web development. He is also the author of The Be OS Bible (Peachpit Press, 1999).