Creating dynamic Web sites with PHP and MySQL
How to serve database content on the fly
Skill Level: Intermediate Md. Ashraful Anam (russell@bangla.net) Web developer #####

15 May 2001 This tutorial shows you how to create a dynamic Web site using PHP and MySQL. You learn how dynamic sites work and how they serve the content. After reading this tutorial, you will be ready to serve your own dynamic content from your own site.

Section 1. Before you start
About this tutorial
This tutorial shows you how to use two open source, cross-platform tools for creating a dynamic Web site: PHP and MySQL.

Prerequisites
This tutorial is targeted to developers who are new to PHP and MySQL, and it has no prerequisites.

Section 2. Introduction and installation
Creating dynamic Web sites with PHP and MySQL © Copyright IBM Corporation 2001. All rights reserved. Trademarks Page 1 of 20

developerWorks®

ibm.com/developerWorks

The need for dynamic content
The Web is no longer static; it's dynamic. As the information content of the Web grows, so does the need to make Web sites more dynamic. Think of an e-shop that has 1,000 products. The owner has to create 1,000 Web pages (one for each product), and whenever anything changes, the owner has to change all those pages. Ouch! Wouldn't it be easier to have only one page that created and served the content on the fly from the information about the products stored in a database, depending on the client request? Nowadays sites have to change constantly and provide up-to-date news, information, stock prices, and customized pages. PHP and SQL are two ways to make your site dynamic. PHP PHP is a robust, server-side, open source scripting language that is extremely flexible and actually fun to learn. PHP is also cross platform, which means your PHP scripts will run on a UNIX®, Linux®, or Windows® server. MySQL SQL is the standard query language for interacting with databases. MySQL is an open source, SQL database server that is more or less free and extremely fast. MySQL is also cross platform.

Installing Apache server routines
First we will install the Apache server routines in the Linux environment. To install these packages you need root access to your server. If someone else is hosting your site, ask the administrator to install them for you. Installing Apache is relatively simple. First download the Apache archive, apache_x.x.xx.tar.gz (the latest I downloaded was apache_1.3.14.tar.gz) from the Apache site and save it in /tmp/src directory. Go to that directory: # cd /tmp/src/ Extract the files with the command: # gunzip -dc apache_x.x.xx.tar.gz | tar xv replacing those xs with your version number. Change to the directory that has been created: # cd apache_x.x.xx

Creating dynamic Web sites with PHP and MySQL © Copyright IBM Corporation 2001. All rights reserved.

Trademarks Page 2 of 20

ibm.com/developerWorks

developerWorks®

Now to configure and install apache, type the commands:
# ./configure --prefix=/usr/local/apache --enable-module=so # make # make install

This will install Apache in the directory /usr/local/apache. If you want to install Apache to a different directory, replace /usr/local/apache with your directory in the prefix. That's it! Apache is installed. You might want to change the default server name to something of real value. To do this, open the httpd.conf file (located at /usr/local/apache/conf) and find the line starting with ServerName. Change it to ServerName localhost. To test your install, start up your Apache HTTP server by running: # /usr/local/apache/bin/apachectl start You should see a message like "httpd started". Open your Web browser and type "http://localhost/" in the location bar (replace localhost with your ServerName if you set it differently). You should see a nice welcome page.

Installing MySQL
Next comes MySQL. We will follow the same procedure (replacing those xs again with our version number). Download the source from the MySQL site and save it in /tmp/src. The latest version I found was mysql-3.22.32.tar.gz.
# # # # # # cd /tmp/src/ gunzip -dc mysql-x.xx.xx.tar.gz | tar xv cd mysql-x.xx.xx ./configure --prefix=/usr/local/mysql make make install

MySQL is installed. Now you need to create the grant tables: # scripts/mysql_install_db Then start the MySQL server: # /usr/local/bin/safe_mysqld & And test your installation by typing:

Creating dynamic Web sites with PHP and MySQL © Copyright IBM Corporation 2001. All rights reserved.

Trademarks Page 3 of 20

developerWorks®

ibm.com/developerWorks

mysql -uroot -p At the password prompt, just press Enter. You should see something like:
Welcome to MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version 3.22.34 Type 'help' for help. mysql>

If you see this, you have MySQL running properly. If you don't, try installing MySQL again. Type status to see the MySQL server status. Type quit to exit the prompt.

Installing PHP
We will follow a similar procedure to install PHP. Download and save the source from the PHP site to /tmp/src:
# # # # # # cd /tmp/src/ gunzip -dc php-x.x.xx.tar.gz | tar xv cd php-x.x.xx ./configure --with-mysql=/usr/local/mysql --with-apxs=/usr/local/apache/bin/apxs make make install

Copy the ini file to the proper directory: # cp php.ini-dist /usr/local/lib/php.ini Open httpd.conf in your text editor (probably located in /usr/local/apache/conf directory), and find a section that looks like the following:
# And for PHP 4.x, use: # #AddType application/x-httpd-php .php #AddType application/x-httpd-php-source .phps

Just remove those #s before the AddType line so that it looks like:
# And for PHP 4.x, use: # AddType application/x-httpd-php .php .phtml AddType application/x-httpd-php-source .phps

Creating dynamic Web sites with PHP and MySQL © Copyright IBM Corporation 2001. All rights reserved.

Trademarks Page 4 of 20

ibm.com/developerWorks

developerWorks®

Save your file and restart apache:
# /usr/local/apache/bin/apachectl stop # /usr/local/apache/bin/apachectl start

Then test whether you have PHP installed properly. Type the following code in a text editor and save it as test.php in a directory accessible by your Web server:

Set the permission of the file to executable by typing at console chmod 775 test.php, and then view it with your browser. You should see a detailed description of the environment variables in PHP as in Figure 1. If you don't, then PHP was not installed properly. Try reinstalling it. Make sure there is a section "MySQL" in the php info; if not, MySQL connectivity will not work. Figure 1. Environment variables in PHP

Creating dynamic Web sites with PHP and MySQL © Copyright IBM Corporation 2001. All rights reserved.

Trademarks Page 5 of 20

developerWorks®

ibm.com/developerWorks

Section 3. Start coding
Your first script
Following tradition, we will begin coding with a "hello world" example. Fire up your text editor and type the following code:

Save the file as first.php and view it in the browser (remember to set the permission to chmod 775 first). The page shows "Hello World". View the HTML source of this page through your browser. You will only see the text Hello World. This happened because PHP processed the code, and the code told PHP to output the string "Hello World". Notice the . These are delimiters and enclose a block of PHP code. tells PHP to stop processing. All lines beyond this scope are passed as HTML to the browser.

Your first database
Now that you have PHP running properly and have created your first script, let's create a database and see what we can do with it. Drop to console and type in the following command: mysqladmin -uroot create learndb This creates a database named "learndb" for us to use. Here we have assumed that you are root user. If you are logged in as another user, just use the command mysqladmin -uusername -pyourpassword create learndb, replacing username and yourpassword with your username and password, respectively. If you are hosting your site through a hosting company, you probably don't have permission to run mysqladmin. In this case, you have to ask your server administrator to create the database for you. Next we will create tables in this database and enter some information. Go to the console. Type:

Creating dynamic Web sites with PHP and MySQL © Copyright IBM Corporation 2001. All rights reserved.

Trademarks Page 6 of 20

ibm.com/developerWorks

developerWorks®

mysql You should see something like:
Welcome to MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version 3.22.34 Type 'help' for help.

Type:
CONNECT learndb CREATE TABLE personnel ( id int NOT NULL AUTO_INCREMENT, firstname varchar(25), lastname varchar(20), nick varchar(12), email varchar(35), salary int, PRIMARY KEY (id), UNIQUE id (id) ); INSERT INTO personnel VALUES ('1','John','Lever','John', 'john@everywhere.net','75000'); INSERT INTO personnel VALUES ('2','Camilla','Anderson','Rose', 'rose@flower.com','66000');

This creates a table with 5 fields and puts some information in it, as shown in Figure 2. Figure 2. Creating a table

Where's my view?
Now that we have a database with some information with it, let's see if we can view it with PHP. Save the following text as viewdb.php:

Run it through your browser and you will see a personnel database. But what is this code doing and how is it generated? Let's examine the code. First we declare a variable $db. In PHP we declare a variable by putting the '$' sign before it. The string after $ is the name of that variable. We assign value to it by coding: $variable_name=somevalue; (example: $count=4;) Remember to put ';' after all the lines that are executable in PHP. So we declare the variable $db and create a connection to the mysql database with the statement "mysql_connect("localhost", "root", "")". In plain English, it means connect to MySQL database in localhost server with the username root and password "". Replace them with your own username and password if they are different. Then we assign a pointer to this database to $db; in other words, $db points to our database server localhost. Next we select the database with which we want to interact with the lines "mysql_select_db("learndb",$db);" which means we wish to use the database "learndb" located by the pointer variable $db. But we want information from the database, so we query the database with the lines "$result = mysql_query("SELECT * FROM personnel",$db);" The part "SELECT * FROM personnel" is an SQL statement (in case you don't know SQL), which means select all the stuff from the database personnel, as shown in Figure 3. Figure 3. Selecting data from the database

We run this query with the PHP command mysql_query() and save the result returned by the database to the variable $result. Now we can access the different data in the different rows of the database from the $result variable. We use the function mysql_fetch_array() to extract each row from $result and assign them to variable $myrow. So $myrow contains information about each row as opposed to all the rows in $result. Then we output the data contained in each row. "echo
Creating dynamic Web sites with PHP and MySQL © Copyright IBM Corporation 2001. All rights reserved. Trademarks Page 8 of 20

ibm.com/developerWorks

developerWorks®

$myrow["firstname"];" means send to output the value contained in the field "firstname" of the row contained in $myrow; in other words, we access different fields of the row with $myrow["fieldname"]. We have used the while() loop here, which means as long as or while there are data to be extracted from $result, execute the lines within those brackets {}. Thus we get nicely formatted output in our browser. Viewing the PHP code and the HTML source from the browser side-by-side may help you easily understand the procedure. Congratulations! You have created your first dynamic page.

Section 4. Add new records
Creating an HTML form
So now you can view records stored in your MySQL database and display them in your browser using PHP. But you want to add new record. Assuming that you know about HTML forms, let's code a page that will do just that. First we'll create a static form, datain.html: First name: Last name: Nick Name: E-mail: Salary:

Now we have a form that will post the information to a page "datain.php". We must code this page so that it is able to process the posted data and send it to our MySQL database. The following listing of datain.php will do that: