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 Linux installed (Red Hat Linux 9 or Fedora Core 1, 2, 3, 4 or 5
preferred).
During the tutorial, we install Apache web server,
MySQL
database and PHP scripting. Finally, we write a
simple php database application.

(c) Tero
Karvinen

Also available in Hindi (pdf).

Apache Web Server

To install servers, you first have to become root. Using su
- (with the dash) makes all commands work without typing paths.

su -

Install the actual web server program

yum install httpd


If you don’t have yum yet, read yum
tutorial and install it.

Start web server, make it start automatically on boot


/etc/init.d/httpd start
chkconfig httpd on


Now your web server should be running. When you surf to
http://localhost, you should see a test page. (Any browser
works, such as firefox, links or konqueror.

mozilla http://localhost

Localhost
usually works even without hole in the firewall. If
you do not
see your test page right away, click shift-reload (ctrl-shift-R or
shift-F5) to bypass cache.

Hide the testpage, so that your computer does not look like a
punching bag for wannabe crackers

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

Make a hole into firewall to make your web server visible to
others. (You can use setup instead of iptables if you want)


iptables -I INPUT 1 -p tcp --dport 80 -j ACCEPT
iptables-save > /etc/sysconfig/iptables


Check your ip number (it’s not 127.0.0.1)

/sbin/ifconfig

and surf to that address, for example http://10.0.0.1.
If you see your test page, congratulations, you just installed your
web server!

Other servers in Linux are installed just like above: install the
program, start it and make it start automatically, and make a hole in the
firewall.

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/foo/public_html. Homepages are shown in
http://localhost/~foo

Allow users to create homepages

Edit web server configuration with your favourite text editor. Older
systems have pico (from pine), newer ones have
nano. For the foolishly brave, there is also vi.

nano /etc/httpd/conf/httpd.conf

Find the section about homepages by searching ctrl-W for
public_html. 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.

/etc/init.d/httpd restart

On some systems, you must explicitly allow usage of public_html directories,
or attempt to use user homepages just gives “403 Forbidden”. To fix this,
find the <Directory /home/*/public_html>, and uncomment all between that line line and </Directory>, including the directory
lines. Then restart apache as above.

Your users can now create homepages.

Create a homepage as a user

If you are still root (if you have a # in your prompt),
exit.

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 $HOME
mkdir public_html
echo "my homepage" > public_html/index.html
chmod a+x $HOME $HOME/public_html
chmod a+r $HOME/public_html/index.html


Some newer distributions, such as Fedora Core 3, have SELinux
(Security Enhanced Linux) installed by default. SELinux causes a
403 Forbidden even if other permissions are correct. To disable
SELinux, choose: Main menu (Red Hat): System Settings: Security
Level. Select SELinux tab, and uncheck “Enforce” checkbox. Reload
the page, and now it should show.

If you have many users (10+), automate this by putting
public_html and index.html to
/etc/skel and check default home dir permissions.

Browse to your home page. If your
name is “user”, it is located in http://localhost/~user. If
you can see “my homepage”, you are now a happy owner of a homepage.

PHP scripting

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

Install PHP scripting

yum install php

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

/etc/init.d/httpd restart

Hello PHP World

As a normal user, write a sample php page

cd $HOME/public_html
pico hello.php


Write this sample code to hello.php


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


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”, you have installed php and written your first program.

MySQL database

Install mysqld just like any server, but don’t make a hole for it in
the firewall. It is only used locally by Apache.


yum install mysql-server mysql
/etc/init.d/mysqld start
chkconfig mysqld on


Try your new SQL server

mysqlshow

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

Two databases. mysql contains database management system
internal data, so don’t edit it. test is a safe sandbox to
play with.

mysqlshow test

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

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

No tables in database test yet. Let’s CREATE
some.

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 usefull – it needs a user
interface. To create a web interface to database, we use PHP
scripting language.


yum install php-mysql
/etc/init.d/httpd restart


Let’s write a simple program to display our database. Use
pico $HOME/public_html/database.php to copy this
script to a file

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 "tt<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.

Links

What next? You can now read the database. Next you probably want to
learn to get user input (using html forms in php) and writing to database.

PHP programming
language manual.

MySQL database reference
manual.

Apache 2.0 web server
documentation. Howtos are a
good place to start.

PhpMyAdmin is an
administrative web interface to MySQL databases.

<<Tero Karvinen’s homepage

Notes, todo and copyright

Tested with Red Hat 9 Shrike, Fedora Core 2, Fedora Core 3 (with and without SELinux).

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.