PHP

[yehlu]

http://developer.apple.com/mac/articles ... syway.html


PHP the Easy Way: Enabling PHP and SQLite on Mac OS X Leopard

PHP, the popular web scripting language, has long run on Mac OS X, but in Mac OS X Leopard it is included right out of the box. It adds improved object-oriented programming support, better error handling, and several features for web services and Ajax, such as XML, SOAP, and JSON support. In addition, PHP now supports use of the SQLite database, which is an entirely self-contained, server-less database that supports advanced SQL functions such as transactions.

In this article, we'll learn how to configure PHP on Leopard, creating a script to help us through this process. We'll then see how to integrate SQLite into a PHP application and build a sample script that queries the SQLite database.
Mac OS X Leopard LAMP Components

Mac OS X has always had the ability to be used as a LAMP server. LAMP is a generic acronym standing for the individual components used to make up the server: Linux, Apache, MySQL and PHP. We will be substituting Mac OS X Leopard for Linux and SQLite for MySQL, but the overall concept of how the components interact are the same.

Let's first take a look at the individual components that make up our server, beginning with PHP itself.
PHP 5 in Leopard

While PHP 4 has been included with Mac OS X for some time now, Leopard is the first release to include PHP 5 by default (PHP 5.2.6 is the version installed with the latest security updates applied to Leopard as of this writing). This is a quantum leap for web developers, who can now take advantage of several new features with PHP 5. These include much better OOP (Object Oriented Programming) constructs, improved error handling, and improved performance. PHP 5 also provides improved XML functions through the XML module, and modern web services support with built-in functions for JSON through the JSON module, both of which are enabled by default in PHP 5.

This version of PHP also supports the latest version of the Apache Web Server, which we'll highlight next.
Apache Web Server

The Apache Web Server version 2.2 is a substantial upgrade from version 1.3 included in previous versions of Mac OS X. In addition to dramatic improvements in performance and core architecture to the web server itself, it also includes new modules for authentication, improved logging, better proxy and filtering support, and simpler configuration. In addition, control of the Apache Web Server is built in to the "Sharing" System Preference, which allows you to stop, start, and monitor the state of the web server from a simple GUI frontend, which we'll see in the next section as we enable the web server.
SQLite Database

PHP 5 also comes with built-in support for the SQLite database. SQLite, an easy-to-use software library, allows you to use SQL queries to access databases stored in local files. Because of its small footprint and impressive SQL support, it is the most widely deployed database in the world. It is used in embedded devices as well as several desktop applications, including Safari, to implement the HTML 5 client-side database. Because it's simple to configure and install and it stores the database in user files, it is a popular choice as a database for small to medium-sized websites.

Now let's look at how to get PHP up and running under Leopard.
Configuring PHP under Leopard

Although all of the components we'll be using are already installed in Leopard, we still need to do a few things ourselves. We'll begin by learning how to enable the default web server, and then learn how to host a website in a user directory. We'll then turn on PHP support and test it to make sure everything is ready to use.
Enabling the Built-in Apache Web Server

As mentioned earlier, Apache is installed by default. In Server versions of Leopard, it can be enabled using the Web Service Preference panel in the Server Preferences to control the Apache Web Server, as seen in Figure 1.
Web Service Server Preference panel

Figure 1: Web Service Server Preference panel

In client versions of Leopard, it can be enabled in the Sharing System Preference panel, as shown in Figure 2.
Sharing System Preference Panel

Figure 2: Sharing System Preference panel

By checking the box next to "Web Sharing," we have enabled the built-in Apache Web Server. You can test your website by clicking the link underneath the label, which opens the default webpage in a new web browser window.

You'll notice another link in Figure 1 called "Your personal website." This isn't completely set up yet; we'll see how to get a user website working in the next section.
Hosting User Websites

One of the nice features of the Apache Web Server is the ability to host websites for individual users on the computer. Each of these websites can be accessed by appending the username preceded by the "~" character to the host name. So entering http://localhost/~bob would access the website for Bob on this computer. You can go to the personal homepage for the logged in user by simply clicking the link underneath "Your personal website" shown above in Figure 2.

If you go to this URL, you'll see a webpage similar to what you see in Figure 3.
Default Personal Home Page

Figure 3: Default Personal Home Page

You can replace the default index.html file with a file of your choosing. Later in we do just that to demonstrate using SQLite with PHP. But first let's enable PHP under Leopard.
Enabling PHP in Apache

PHP is already installed with Leopard, so we'll just need to enable it for use with the Apache Web Server. From here on out, we'll be using a command script to enable PHP and create the same files for it to use.

As shipped, the line to enable PHP is commented out in that file, so we'll use sed to uncomment the line in the file. We'll also backup the original httpd.conf file, and then restart the web server, as illustrated below:

set admin_email to (do shell script "defaults read AddressBookMe ExistingEmailAddress")
user_www=$HOME/Sites
filename=php-test
user_index=${user_www}/${filename}.php
user_db=${user_www}/${filename}-db.sqlite3
# NOTE: Having a writeable database in your home directory can be a security risk!

conf=`apachectl -V | awk -F= '/SERVER_CONFIG/ {print \$2}'| sed 's/"//g'`
conf_old=$conf.$$
conf_new=/tmp/php_conf.new

touch $user_db
chmod a+r $user_index
chmod a+w $user_db
chmod a+w $user_www

echo "Enabling PHP in $conf ..."
sed '/#LoadModule php5_module/s/#LoadModule/LoadModule/' $conf | sed
"s^you@example.com^<b>\$admin_email</b>^" > $conf_new

echo "(Re)Starting Apache ..."
osascript <<EOF
do shell script "/bin/mv -f $conf $conf_old; /bin/mv $conf_new $conf;
/usr/sbin/apachectl restart" with administrator privileges

EOF

Note how we've now made a copy of the existing http.conf file, which is where we will make our changes. Using sed, a stream editor, we've uncommented the LoadModule line in the http.conf file, and we've correctly set the Administrator Email address setting. Finally, we've replaced the existing http.conf with the one we just altered (backing up the old one in the process), and restarted the web server.

PHP should now be working with the Apache Web Server. In the next section, we'll see how we can verify that the above script is working.
Testing Your Installation

To test PHP on a web server, we can create a simple text file with the following contents: <?php phpinfo(); ?>

We'll name this file phpinfo.php, and put it in our Sites directory.

Now, when you go to the URL http://localhost/~user/phpinfo.php, you should see the following in Figure 4.

phpinfo.php

Figure 4: phpinfo.php

For the next section, we want to make sure that SQLite support is enabled. You see both a listing for SQLite and pdo_sqlite as you scroll down the page shown in Figure 5, which lets us know both are enabled by default. PDO stands for PHP Data Objects, and it is a library designed to provide a consistent API for all of the major databases, including MySQL, PostgreSQL, Oracle, and many others.

phpinfo.php showing pdo_sqlite enabled

Figure 5: phpinfo.php showing pdo_sqlite enabled

Now that PHP is properly configured for use with Apache, and we can verify that SQLite support is enabled, we can now build a page that demonstrates PHP and SQLite working together.
Using SQLite with PHP

As mentioned earlier, since SQLite is a zero-configuration database, we don't need to do much to get a sample database up and running. We'll take a look on how to do this with a script.

The script we'll build in this section will emulate a typical page counter. The script will begin by inserting the current page view information into the page counter table. Then, we'll query the page counter table for a total count of page views to display back to the user.

But before we can do this, we'll need to create the page counter table.
Creating an SQLite Database

You can create a new SQLite database directly from PHP. From the point of view of the Model-View-Controller (MVC) design pattern, this is both a good and a bad thing about PHP: the fact that you can interact directly with the database (Model) from inside the HTML (View). The simplicity makes it great for small projects, but the violation of MVC separation makes it extremely fragile for large ones.

In the following, we'll see the section of our script that handles creating the database:

<?php

// create a SQLite3 database file with PDO and return a database handle (Object Oriented)

try {
\$dbHandle = new PDO('sqlite:'."$user_db");
}catch( PDOException \$exception ){
die(\$exception->getMessage());
}

// create page view database table

\$sqlCreateTable = 'CREATE TABLE pageView(id INTEGER PRIMARY KEY AUTOINCREMENT,
page CHAR(256), access INTEGER(10))';
\$dbHandle->exec(\$sqlCreateTable); // remove or 'comment out' this line
after first run

When we create the database handle, the file that holds the entire database is created. Then, we issue a CREATE TABLE statement to build our table, pageView. You can see from the above CREATE TABLE statement that SQLite supports primary keys and auto increment fields, which will be handy as we continue to build our PHP web application.
Using SQLite Data with Your Web Application

Now that we have created the SQLite database, we can use it in our application along with the table we created. We continue building our script as shown next:

// insert page visit in database with a prepared statement

\$sqlInsertVisit = 'INSERT INTO pageView (page, access) VALUES (:page, :access)';

\$stmt = \$dbHandle->prepare(\$sqlInsertVisit);
\$stmt->bindParam(':page', \$_SERVER['PHP_SELF'], PDO::PARAM_STR);
\$stmt->bindParam(':access', time(), PDO::PARAM_INT);
\$stmt->execute();

Our PHP script can work as a web counter. In the first part of the script, we insert information about this page visit into the pageView table, namely the page name and page access time. We'll use prepared statements to do this. Using prepared statements adds a security layer to our SQL queries by allowing the database API to properly escape the parameters to the query. This prevents SQL injection attacks from malicious data being passed to the query. Prepared statements involve a three part process: first, we create the query using the prepare() method, putting placeholder for our passed in values; next, we bind our data to the query using the bindParam() method, and finally we execute the prepared query using the execute() method.

Next, we pull data out of our SQLite database and display it in the PHP script:

// get page views from database

\$pageVisit = \$dbHandle->quote(\$_SERVER['PHP_SELF']);
\$sqlGetView = 'SELECT count(page) AS view FROM pageView WHERE page = '.\$pageVisit.'';
\$result = \$dbHandle->query(\$sqlGetView);
\$pageView = \$result->fetch(); // store result in array

// print page views and filename

echo '<blockquote>This page has been viewed <b>'.\$pageView['view'].
"</b> times, according to <em>$user_db</em>.</blockquote>";

echo '<blockquote>Edit <em>'.\$_SERVER['SCRIPT_FILENAME'].'</em>
to learn how this works.</blockquote>';

?>

Here we are retrieving the data using an SQL SELECT statement. We then fetch the first row back to us in an array. The array returned back to us is both associative as well as contains number indices starting from column 0, which is normal for PHP arrays. We'll refer to the value we need as an associative item for readability, and display it back to the user.

We have now built an entire script that will not only enable PHP under Apache with Mac OS X Leopard, but we've also created a sample PHP script that highlights the use of SQLite. In the script, we built a page counter using several best practices in the realm of database interface programming, namely by using prepared statements to help protect our query from SQL injection attack and malicious data. We began by creating the database table used to store the page counter data. Then we added the SQL query to add a new row to the page counter table, illustrating how to use prepared statements with SQLite. Then, we queried the page counter table for the current count of page views, and returned that result back to the user.
Conclusion

In this article, you have learned about the LAMP components that come preinstalled with Mac OS X Leopard, namely the Apache Web Server, SQLite database and PHP 5. We saw how easy it is to get the components up and running with Mac OS X Leopard, and we built a shell script that not only enabled PHP 5 with Apache, but also created a PHP script that is deployed in our user's web root that built a page counter that illustrated how to use SQLite.

Updated: 2008-09-24
Related Articles

* Leopard Technology Series for Developers: Server Overview
* Introduction to Open Source Scripting on Mac OS X

Resources

* Reference Library: Mac OS X Server
* Scripting & Automation Topic Page
* Manpage on SQLite
* Manpage on PHP