Build Web Interface to Database - LAMP Linux Apache MySQL PHP

In this howto, we program a simple database application. It reads data from a database, then prints it on a web page.

Techniques teached here are used for building guestbooks, web shops, web forums and group calendars.

You only need to have CentOS installed. During the tutorial, we install Apache web server, MySQL database and PHP scripting. Finally, we write a simple php database application.

(c) Tero Karvinen
Updated for CMU 95-799 Linux class by Dennis Moul

System Preparation

First, take snapshot of the CentOS VM so you can revert back to a known good state if you run into problems.

For these steps, you first have to become root. Use su - (with the dash) to "switch user" to root:

su -

chkconfig iptables off

vi /etc/sysconfig/selinux

Now you have to reboot your virtual machine to pick up the changes. You can do that via the menu with System->Shut Down->Restart, or via the command line with "shutdown -r now".

Apache Web Server

To install servers, you first have to become root. Use su - (with the dash):

su -

Install the actual web server program (may be preinstalled, depending on OS version)

yum install httpd


Start web server, make it start automatically on boot

service httpd start
chkconfig httpd on


Now your web server should be running. When starting Apache, you may get an error like "apr_sockaddr_info_get() failed, could not reliably determine the server's fully qualified domain name". This happens because we never set up a proper real domain name for our VM, and is nothing to worry about.

Now start Firefox in your VM and access http://localhost. You should see an Apache test page. If you do not see your test page right away, click shift-reload (ctrl-shift-R or shift-F5) to bypass the cache.

Create a simple initial page instead of the system default:

echo "powered by linux" > /var/www/html/index.html

access  http://localhost with Firefox again, If you still get the old page, try clicking shift-reload. If you get a permissions error, examine the file permissions on the index.html file you created.

Now, to access the web server running in your CentOS virtual machine from another computer (your host OS)... now look up your virtual machine's IP address on the virtual network it shares with VirtualBox:

/sbin/ifconfig

Look for the "inet addr" value for the eth1 network interface (which you created during the install as Network Adapter 2, type Host-Only) and note the IP address, like 192.168.56.101 (yours may be slightly different). In your host OS, open your browser and enter that address, for example http://192.168.56.101. If you see your test page, congratulations, you just remotely accessed  your web server!

If it's working locally in the virtual machine but not remotely from your host OS, check the following:

• Make sure you turned off SElinux. You can check whether it's active by typing getenforce; it should return "permissive" or "disabled".
• Make sure you turned off the system firewall. service iptables status should return "Firewall is not running".
• If it's still not working, you may need to reboot your host OS to reset Virtualbox's networking. Do a clean shutdown of the guest or take a snapshot of it, reboot your host OS, resume/restart your guest OS, then try the host OS browser again.

User homepages

Web server content directory /var/www/html/ is not writable for normal users, and you don't want to edit web pages as root. That's why users create homepages in /home/user/public_html. Homepages are shown in http://localhost/~user

Allow users to create homepages

Edit web server configuration with vi.

vi /etc/httpd/conf/httpd.conf

Find the section about homepages by searching  for public_html with the "/" search function in vi. If text seems mixed up, refresh the screen with ctrl-L. Comment out the line with UserDir disable and remove the comment char "#" from the line

UserDir public_html

Activate your changes by restarting the web server.

service httpd restart

Your users can now create homepages.

Create a homepage as a user

Creating the homepage should be done as a normal non-root user. The easiest way is to just open a 2nd Terminal window, and run that as your normal user, then you can switch back and forth between the root Terminal window and the normal user Terminal as needed. You can tell them apart by the prompt, "#" for root and "$" for normal user.

Create a directory for homepages. Home directory, public_html and all directories under public_html must be executable (x) by all, so that web server can access files under them if it knows their name. Files must be readable by the web server.

cd ~
mkdir public_html
echo "my homepage" > public_html/index.html
chmod a+x ~ ~/public_html
chmod a+r ~/public_html/index.html


PHP scripting

PHP is a powerful scripting language with a C++ like syntax, many readymade classes, good examples and great documentation.

Install PHP scripting (switch back to root terminal first)

yum install php

Because php is a module, you must restart a web server to load it.

service httpd restart

Hello PHP World

As a normal user, write a sample php page

cd ~/public_html
vi hello.php


Write this sample code to hello.php

<?php echo "Hello PHP World, 2+2 is " . (2+2); ?> <br>
Text outside code block is printed normally to web page.<p>
<?php phpinfo(); ?>

Usually there is a normal html web page outside the <?php code ?>.

Browse to http://localhost/~user/hello.php. Put your own login name instead of user. If you see "Hello PHP World, 2+2 is 4" and a big list of PHP configuration data, you have installed PHP and written your first program.

MySQL database

 When installing mysql-server via yum you will be prompted to install some dependencies; say yes.

yum install mysql-server mysql
service mysqld start
chkconfig mysqld on


You may get some warnings when MySQL starts up. For the purposes of this lab you can ignore those, but if you were building a production system you should tighten up the security configuration.

Try your new SQL server

mysqlshow

+--------------------+
| Databases          |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

Three databases. mysql and information_schema contain MySQL internal data, so don't mess with them. test is a safe sandbox to play with.

mysqlshow test

Database: test
+--------+
| Tables |
+--------+
+--------+

If for some reason you do not have database test, then do CREATE DATABASE test; and then USE test;

No tables exist in database test yet. Let's CREATE some. In your root terminal, start the command-line mysql tool in which to run SQL commands:

mysql

On the prompt mysql>, you can type mysql commands (USE, SHOW) or sql queries (CREATE, INSERT, SELECT).

USE test;
SHOW tables;
CREATE TABLE persons( name VARCHAR(50), email VARCHAR(50) );
SHOW tables;
DESC persons;
INSERT INTO persons VALUES('Tero Karvinen', 'karvinen at-sign iki.fi');
SELECT * FROM persons;
INSERT INTO persons VALUES('Sample Person', 'recycle@nosuch.invalid');
SELECT * FROM persons;
QUIT;


After the two INSERTs, the last SELECT should return a table with two records

+---------------+---------------------------+
| name          | email                     |
+---------------+---------------------------+
| Tero Karvinen | karvinen <at-sign> iki.fi |
| Sample Person | recycle@nosuch.invalid    |
+---------------+---------------------------+
2 rows in set (0.01 sec)

You have now installed a database management system, and you also know some SQL.

PHP program using MySQL database

A database by itself is not very useful - it needs a user interface. To create a web interface to database, we use the PHP scripting language and its module (which we installed in Lab 2) to communicate to the database.

yum install php-mysql
service httpd restart


Let's write a simple program to display our database. The database.php script shown here can be cut-and-pasted, or downloaded as http://linuxclass.heinz.cmu.edu/labs/database.php to reduce the likelihood of typing errors. Use shift-click, or right click->”Save Link As” to download the file, otherwise the tags may get garbled. You may need to change the permissions on the downloaded file for it to be accessible via browser.

PHP database example - http://iki.fi/karvinen. <br>
<?php
    /* database.php - Use mysql database from php
     * (c) 200309 Tero.Karvinen <at-sign> iki.fi, adapted from php.net 
     * See http://iki.fi/karvinen Linux Apache MySQL PHP tutorial. */

    /* Connect to database */
    $link = mysql_connect("localhost", "root", "")
        or die("Could not connect : " . mysql_error());
    print "Connected successfully";
    mysql_select_db("test") or die("Could not select database");

    /* Perform SQL query */
    $query = "SELECT * FROM persons";
    $result = mysql_query($query)
	or die("Query failed : " . mysql_error());

    /* Print results in HTML */
    print "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        print "\t<tr>\n";
        foreach ($line as $col_value) {
            print "\t\t<td>$col_value</td>\n";
        }
        print "\t</tr>\n";
    }
    print "</table>\n";
    mysql_free_result($result);

    /* Close connection */
    mysql_close($link);
?>

Browse to http://localhost/~user/database.php. Use your own login name instead of user. You should see:

PHP database example- http://iki.fi/karvinen.
Connected successfully
Tero Karvinen karvinen iki.fi
Sample Person recycle@nosuch.invalid

If you see the two email addreses from the database, congratulations! You have successfully completed every part of this tutorial.

<<Tero Karvinen's homepage

Copyright

Copyright 2003-09-25, 2003-10-02, 2003-11-12 (Minor consistency edits, links), 2003-11-22 (minor copyedit), 2004-05-17 (updated links) 2005-02-11 (tested on Fedora Core 3, SELinux) Tero Karvinen. All rights reserved. 

Updated for CMU 95-799 Linux class and recent CentOS by Dennis Moul.