//, PHP/Putting PHP & MySQL To Work

Putting PHP & MySQL To Work

Putting PHP and MySQL to work

The days of static Web sites are gone. Most major Web sites now offer the ability for each visitor to customize the site to their liking. Sites can now save information personalized to the needs of each visitor such as how they view the pages, their profile information, billing and shipping instructions, a list of favorite pages or products, and so on. When the visitor returns the next time, the site generates Web pages dynamically that take these preferences into account.

How do these sites work such magic? The answer lies in server-side scripting languages, such as PHP, ASP, and JSP. In this article, I show how you can create a Web site that features a sidebar with news links to stories from other sources. This layout works nicely with any type of Web site, because it leaves the rest of the page free for any type of content. Site visitors who create profiles can select the news sources they want to display and the colors they want to use for site structural elements. Visitors who do not create their own profiles see a generic layout.

In this example, a link at the top of the Web page brings visitors to the login page, where they can sign in or create a new account. To create a new account, the visitor enters an e-mail address and a password. The system sends a confirmation to the e-mail address that includes a link containing the confirmation code. When the visitor uses the link, he or she can confirm the e-mail address and password entered previously. If the information entered is valid, the visitor is sent to the user profile page where to set preferences. Once the visitor has edited a profile, he or she can return to the homepage to view it using those preferences.

This article assumes that you are already familiar with basic PHP syntax and function. If you need an introduction, read Craig Knudsen’s introductory article on PHP, which I’ve included in the Resources section. I use the new session management features that are only available with PHP version 4. I also assume that you are familiar with MySQL user and database management. If not, I recommend the MySQL Administration tutorial offered by DevShed.com (also in the Resources section). Keep in mind that MySQL has recently been relicensed under the GNU Public License, which means that it is now free on all platforms, including Microsoft Windows.

To store visitor information and preferences, you need to create a database and design the tables that will hold the information. All information will be entered into the database solely through a Web interface. When visitors fill out forms to sign up for an account or change their account, the PHP scripts will perform the appropriate SQL queries on the database.

All the pages on the Web site will be generated by PHP code and will vary if the visitor is logged in. Even though the initial page might look quite different for each user, you will see that a single program calls the external functions to create the personalized appearance.

Reusable modules are key

When you create dynamic Web sites, you should use reusable modules, usually done by functions and include files. By forcing your code into modules, you can decrease the complexity of your code. Most experienced programmers already know this, but many overlook the importance of modularity when it comes to Web sites. Any time you find yourself using the same technique more than once, consider making a function for it, as in the following PHP example:

function name ($var) {

// code goes here


The scope of the $var variable is the name function. Before you can reference global variables in a function in PHP, you need to use the global keyword to define them. If a function will only be used on one page, you can simply include it in the same script that will reference the function. If more than one script will use this function, consider putting it in a separate file that can be imported by any script.

Create your database

Your database is crucial to the performance of your Web site. It should be normalized (the process of assigning attributes to an entity, such as a customer or a product, which helps to avoid data redundancy), and designed with future growth in mind. Database design is beyond the scope of this article so I recommend that someone on your team be familiar with proper design.

I have included the table creation output from mysqldump in the file example_db.sql (included with the code zip file), so you can set up the database as in the example code. I have also included some sample data in the file example_db_data.sql, which you can use to seed the database. Create a database and a MySQL user to access the database. This user should be given select, insert, update, and delete privileges on this database. Be sure that you assign these privileges in the db table, rather than the user table, for security reasons. The db table can assign these privileges to a single database but the user table assigns privileges to all databases.

Import the tables and data using the command:

mysql -u username -p db_name < file.sql

where username is the name of any MySQL user who has access to this database, db_name is the name of your database, and file.sql is the file you are importing.

To run the example PHP code, you need to edit the database.php script and fill in these values. This script includes two function calls that connect to the MySQL server and select the database, respectively. By including these variables and function calls in a separate file, we can now access the database in any PHP script simply by including the database.php file.

Let’s customize

Each visitor to your site who wants to customize the view will need a unique account. Visitors register by filling out a simple form. You should not activate visitors’ accounts until they verify their account information by responding to an e-mail from the site. This should help to minimize bogus accounts, because you can run nightly queries and delete accounts that that have not been activated after a period of time.

I have included some sample code in login.php that handles both regular logins and new user registration. By passing additional variables to a PHP script you can determine which action to perform and keep the number of scripts to a minimum.

After visitors register for an account, they are sent an e-mail with a link that contains a confirmation code. By following this link and entering an e-mail address and password, visitors verify their accounts and are taken to the user preferences page (edit_user.php). On this page, visitors can enter their first and last names, the news sources they do not want to see when they visit your site (the default is all sources), and the colors used to display site structural elements. The First Name field is used to greet the user when they visit your site. Figure 1 shows an example of the preferences page.

Fig 1. Preferences Page

Now that visitors can log in and set their preferences, you need a way to retrieve the values. The function in Listing 1 does exactly that.

Listing 1. Getting user preferences from the database

user_funcs.php (get_user_data)

function get_user_data($user_id) {

global $s_first_name, $s_last_name, $s_preferences, $s_color;

$query = “SELECT * FROM user WHERE user_id=’$user_id’ AND active=’Y'”;

$result = mysql_query($query) or die (“Query failed”);

if (mysql_num_rows($result) > 0) {





$row = mysql_fetch_array($result);

$s_first_name = $row[‘first_name’];

$s_last_name = $row[‘last_name’];

$length = strlen($row[‘preferences’]);

for ($i = 0; $i < $length; $i++) { $s_preferences[$i] = $row['preferences'][$i]; }
$s_color = $row[‘color’];



The get_user_data function is called with a parameter that contains the visitor’s user ID. The function then defines a set of global variables so that they can be imported into the scope of the function. Next, it queries the database to retrieve all the information about the given user ID, while checking to make sure the user ID is active. If the user ID is found, the code calls the session_register function to register the visitor’s session variables.

Session management is a new feature of PHP version 4. To use this feature, you have to activate it by calling the session_start function in every script that needs session management. To start session management automatically on every script, set the session.auto_start variable to 1 in your php.ini file. When visitors come to your site, they are assigned a session identifier, a semirandom hash value that uniquely identifies each visitor. The session ID is stored either as a cookie in the user’s browser or as a URL parameter. When you register session variables for a user, the variables are passed along in the global $HTTP_STATE_VARS array. Your scripts can now access the global variables for each session without having to pass them to each other. The final section of code in this function pulls the data from the query result (the $row variable), and stores it in the session variables as separate preferences.

Show ’em what you got

Many sites have some sort of a consistent style, which you can think of as a template. The template is often used to display headers, ads, and navigational menus that change as users visit different sections of the site. The easiest way to maintain this template is to create a function that outputs most of the HTML used to structure the site. You can pass parameters to this function to customize the site appearance. Listing 2, shown below, is the function used to generate the page header for my example site.

Listing 2. Defining the page header

site.php (site_header function)

function site_header($params) {

global $s_first_name;

print ‘<HTML><HEAD><TITLE>My Site Inc.’;

if ($params[‘title’]) { print ” – ” . $params[‘title’]; }

print ‘</TITLE><LINK rel=”stylesheet” href=”example.css” type=”text/css”></HEAD>

<BODY topmargin=”0″ bottommargin=”0″ leftmargin=”0″ rightmargin=”0″ marginheight=”0″ marginwidth=”0″>

<TABLE cellpadding=”5″ cellspacing=”0″ border=”0″ width=”100%” bgcolor=”‘.color().'”>

<TR><TD class=”page_title” width=”50%”>’;

if (logged_in()) {

print “Welcome back”;

if ($s_first_name != “”) { print “, “.$s_first_name; }


else { print “Welcome to My Site Inc.”; }

print ‘</TD><TD class=”page_title” width=”50%” align=”right”>’;

if (logged_in()) {

print ‘<a href=”example.php” class=”page_title”>Home</a> | ‘

. ‘<a href=”edit_user.php” class=”page_title”>Preferences</a> | ‘

. ‘<a href=”example.php?action=logout” class=”page_title”>Logout</a> ‘;


else {

print ‘<a href=”example.php” class=”page_title”>Home</a> | ‘

. ‘<a href=”login.php” class=”page_title”>Login</a>’;


print ‘</TD></TR></TABLE><BR>’;

The $params array lets each script that calls this function pass in data to use in Web page customization. In this example, it is only used to add navigation text to the <TITLE> tag.

Next in this example is a check to see whether the visitor is logged in. If so, he or she is greeted with a warm “Welcome back” message that includes the visitor’s first name, if it was provided in the user preferences. Visitors who are logged in are also given menu selections to change their site preferences or log out. If visitors are not logged in, they see a generic greeting and the menu contains only one option, which leads to the login page.

After the visitor has been properly greeted, it is time to show some news (which is, of course, why someone would visit our site). Listing 3 contains the function used to display news headlines from a single news source.

Listing 3. Displaying news items from a single news source

example.php (show_news function)

function show_news ($news_source) {

$data_query = “SELECT headline, link FROM news WHERE news_source=’$news_source’ ORDER BY timestamp DESC LIMIT 5”;

$data_result = mysql_query($data_query) or die (“Could not get news data”);

if (mysql_num_rows($data_result) > 0) {

while ($data_row = mysql_fetch_array($data_result)) {

print ‘<LI><SMALL><A href=”‘.$data_row[‘link’].'” class=”news”>’.$data_row[‘headline’]

. ‘</A></SMALL></LI>’;




The show_news function is passed an integer that represents the source_id of the news source. Next, the headline text and URL link are fetched from the database for the five most recent news items. The item limit would make a good configurable parameter if you wanted to extend this example. If the news items are found, it simply prints out an HTML list item for each item as a link to the story.

Tie it together

The preceding functions provide a nice facility to design any site layout. This example creates a rather simple layout where the news links are shown in a left column and the main site content takes up the rest of the space, as shown in Figure 2.

Figure 2. Site layout

Take a look at the code that generates this layout. I think you’ll be surprised at how little code is needed, once most of the features are available as functions. Listing 4 contains the details.

Listing 4. Defining the main site



if ($action == ‘logout’) { logout(); }



<TABLE cellpadding=”6″ cellspacing=”0″ border=”0″ width=”100%”>

<TR valign=”top”>

<TD width=”180″>


if (logged_in()) {

foreach ($s_preferences as $index => $pref) {

if ($pref == “1”) {

$query = “SELECT * FROM news_sources WHERE source_id='”.($index+1).”‘”;

$result = mysql_query($query) or die (“Could not select news sources”);

if (mysql_num_rows($result) > 0) {

$row = mysql_fetch_array($result);

box_top($row[‘source_name’], $row[‘source_home’]);







else {

$query = “SELECT * FROM news_sources”;

$result = mysql_query($query) or die (“Could not select news sources”);

$i = 1;

while ($row = mysql_fetch_array($result)) {

box_top($row[‘source_name’], $row[‘source_home’]);








<TD width=”100%”>

<TABLE cellpadding=”0″ cellspacing=”0″ border=”0″ width=”100%”>




<DT><SPAN class=”heading”>Latest News</SPAN></DT>

<DD><BR>You can now register for an account on our site! With an account you will be able to select

which news items you would like to see and hide those you don’t. You also get the nifty feature of being

personally greated every time you visit our site. How much would you expect to pay for this? Nothing!

<a href=”http://www.php.net”>PHP</a> and <a href=”http://www.mysql.net”>MySQL</a>

make this so easy anyone can do it.</DD>










The code in Listing 4 checks to see whether the $action variable is set to ‘logout’. If so, the logout function is called, which unregisters all session variables so that the site will consider this user logged out. Next, the code calls the site_header function to print out the site template and pass it the string ‘Home’ to be displayed in the title.

Now it’s time to select the news items that the visitor will see. The code calls the logged_in function to parse the visitor’s preferences string. For each character in the string set to 1, news from one source is displayed. First, the code calls the box_top function (defined in site.php) to print out the necessary table elements to display a box around the source, with the news source’s name and home page URL already passed into the function to be shown in the box header. Next, the code calls the show_news function to display the news items for that news source. Last, it calls box_bottom to close off the box boundary. If a site visitor is not logged in, the code will simply query the database for all news sources and display boxes for all of them.

After this point, the PHP script is basically done, and you can add the actual page content. More than likely, a site would have a separate database for its own content and you could write more PHP code to grab the content from that database.

Breaking code down into functions lets you create structures that are reusable anywhere on a Web site. You can then create any page layout easily. It should be very simple to add a right menu containing more news links. Alternatively, the entire page could be a large table of news boxes and with added code so that each visitor could select the position of each box within a row or column. You could add other boxes to show information such as the weather forecast, horoscopes, television schedules, or event reminders. Any of these would simply be an addition to the preferences page. Just remember to encapsulate each new feature so it can be used elsewhere. Most of all, be creative and have fun doing it.


Craig Knudsen gives introduction

to PHP
on developerWorks.


project homepage is a good starting point for information and lots

of documentation


has some pretty good PHP tutorials. PHP Builder

is a good resource for PHP developers, and even has job listings and resumes.

PHPwizard.net is another

site for additional help on PHP programming.


is a free SQL database that runs on most operating systems, including

most flavors of UNIX, as well as Windows and OS/2. You should read through the

MySQL documentation

if you aren’t familiar with it.


has articles and tutorials on programming MySQL, as well as a handy MySQL


In case you need them, here’s the Netscape

HTML Tag Reference
and Dynamic HTML Manual

2010-05-26T17:02:32+00:00 May 11th, 2004|MySQL, PHP|0 Comments

About the Author:

Approximately two years ago Steve Fox was abducted by aliens from the planet UNIX. Since then, he has become a Free Software advocate and a supporter of the GNOME desktop environment. He now spends all of his time tinkering with Linux and tormenting his wife and stepchildren. On August 18th, 2000, he became the father of a baby boy, Rilyn Taitt. He can be reached at stevefx@us.ibm.com

Leave A Comment