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 Red Hat 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 Red Hat VM so you can revert back to a known good state if you run into problems.

For most of these steps, you have to run them as root. So use sudo -s to switch to a root shell:

sudo -s

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=disabled". Make sure to get this exactly right, some students have had their Red Hat fail to boot properly if they messed this up.

Now you have to restart your VM to pick up the changes. You can do that via the upper-right-corner menu Power button, or via the command line with "shutdown -r now".

Apache Web Server

To install servers via package management, you first have to become root. Use sudo -s

sudo -s

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

yum install httpd

If you get an error about "no enabled repositories" or "Status code 403 ... failed to download metadata", it may be an issue with your Red Hat subscription. run the Subscription tool (Activities, type Subscription) and use the Auto-attach button, then retry your yum command. If that doesn't work, go back into the Subscription tool and Unregister then re-register.

Start the web server, and 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 a Red Hat 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 Red Hat virtual machine from your host OS, we need to 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 of the Red Hat desktop 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 Red Hat is seeing the Host Only Network properly internally - to the right of the date & time in Red Hat'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".
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 usually don't want to edit web pages as root. That's why users create homepages in /home/user/public_html instead. 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 by adding a comment character "#" at the beginning of its line, and then remove the comment character "#" 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.

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. For my login name "dmoul", it is located in http://localhost/~dmoul. Substitute your own login name after the tilde. 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 an Apache module, you must restart the 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

Red Hat 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 |
+--------------------+

Three databases. mysql and the *_schema tables contain internal data, so don't mess with them. Let's create one for ourselves:

mysql -e 'create database test'

Look with mysqlshow to make sure it's there:

mysqlshow test

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

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.

In order for our app to be able to connect to the database, we need to go through the initial security procedure. As root, run

mariadb-secure-installation

Answer the questions accordingly:
Enter current password for root: (just press enter)
Switch to unix_socket authentication? N
Change the root password? Y (then set whatever database root password you want)
Remove anonymous users? N
Disallow root login remotely? N
Remove test database and access to it? N
Reload privilege tables now? Y

Now we are ready to connect PHP to the database.

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-mysqlnd 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. Replace the string dbrootpassword with the database root password you just changed in the mariadb-secure-installation step.



PHP database example<br>
<?php
    /* database.php - Use mysql database from php */

    /* Connect to database */
    $conn = new mysqli("localhost", "root", "dbrootpassword", "test")
        or die("Could not connect : " . $conn->connect_error);
    print "Connected successfully";

    /* Perform SQL query */
    $result= $conn->query("SELECT * FROM persons")
        or die("Query error : " . $conn->error);
    /* Print results in HTML */
    print "<table>\n";
    while ($line = mysqli_fetch_array($result, MYSQLI_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";

  /* Clean up */
  $result->close();
  $conn->close();

?>

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

PHP database example
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.

If you get the "PHP database example" line but not the two lines of names/email addresses, double-check that you changed the string dbrootpassword to the database root password you set in the mariadb-secure-installation step.

<<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 Red Hat by Dennis Moul.