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.

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 -

Set the system firewall to not load at boot time - it will otherwise block your access:

systemctl disable firewalld

and also disable the boot-time loading of "SElinux", an advanced security feature that will get in the way. Edit its configuration file:

vi /etc/sysconfig/selinux

change the line that says "SELINUX=enforcing" to read "SELINUX=permissive". Make sure to get this exactly right, some students have had their CentOS fail to boot properly if they messed this up.

Now you have to shut down your virtual machine and start it back up again to pick up the changes. You can do that via the upper-right-corner menu Power button, or via the command line with "shutdown -h 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

systemctl start httpd systemctl enable httpd

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)... look up your virtual machine's IP address on the virtual network it shares with VirtualBox:

/sbin/ifconfig

Look for the "inet" value for the enp0s8 network interface (which you created during the install as Network Adapter 2, type Host-Only) and note the IP address, which is usually 192.168.56.X (your X may vary). If there's no IP address listed, make sure the enp0s8 interface is active - click on the battery/sound icon in the upper right corner and look to see if enp0s8 is "connected" or "off". If it's off, select that and choose "Connect." If you still can't get it to connect and show an IP address in the /sbin/ifconfig output, see the troubleshooting steps listed in section 8.a. of Lab 3 part 2 (samba). Also consult the forums and TA(s) for assistance in getting your Virtualbox Host Only Network working properly.

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:

Check the Host Only Network (Network Adapter 2) to make sure it's connected. On the bottom status bar of the virtualbox window, hover over the "two computers" icon. It should show
"Adapter 1 (NAT) cable connected"
"Adapter 2 (Host Only Adapter) cable connected"
You can also check that CentOS is seeing the Host Only Network properly internally - to the right of the date & time in CentOS's top menu bar is a network socket icon, click that and you should see two Ethernets both listed as "Connected".
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. systemctl status firewalld should return "Active: inactive". Also check iptables -L -v | grep -v virbr0 and verify that the rule chains are all empty except for the virbr0 lines which don't affect us. If they aren't, clear them with iptables -F and check again..
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. A clean restart of the guest OS can help as well.

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.d/userdir.conf

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.

systemctl restart httpd

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.

If you logged in originally as root (usually not recommended), you can “su” (set user) to the non-root account you created during CentOS installation by using “su – userid”.

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

Browse to your home page. If your username 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 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.

systemctl restart httpd

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.

MariaDB database

RedHat/CentOS no longer includes MySQL as the default database - it's now the compatible fork "MariaDB" instead.

yum install mariadb-server systemctl start mariadb systemctl enable mariadb

You may get some warnings when MariaDB 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              |
| performance_schema |
| test               |
+--------------------+

Four databases. mysql and the *_schema tables contain 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 systemctl restart httpd

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 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. Put the file in your public_html directory and name it database.php.

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.