April 2005

Simple Linux Apache MySQL PHP

A website with a simple news page is, most likely, easier managed with some sort of organized schema. In the case of systhread, deciding what to use and what to use it for was pretty simple.

A Very Simple News Ticker

Since systhread already has common elements using Server Side Includes and interaction using simple CGI scripts, using MySQL and PHP for the current and previous news items is a logical choice.

Building the Sources

The following sources had to be compiled:

Apache Recompile

Even though apache was already installed, it required recompilation to make sure module loading is enabled.

tar xzvf httpd-2.X.X.tar.gz
cd httpd-2.X.X
./configure --prefix=/usr/local/apache2 --enable-so \
                --enable-auth --enable-ssl

In order for the phpmodule to load, only the --enable-so configure option is required. Be sure to run ./configure --help | less to see all available options.

Then make and as privileged user make install [1].

MySQL

The MySQL install is pretty straightforward, there are a lot of options to consider, so consulting the documentation might be a good idea. In the case of the use for systhread, the path and running user was added. Make sure there is either a mysql user and group, otherwise:

groupadd mysql
useradd -g mysql -d /usr/local/mysql/var -s /bin/bash -c  MySQL mysql

Next, configure mysql:

tar xzvf mysql-X.X.X.tar.gz
cd mysql-X.X.X
./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql 
make
make install

PHP

PHP does have a few key options, specifying the apxs apache binary (which aids in creating and setting up the phpmodule), the path and where mysql resides.

tar xzvf php-X.X.X.tar.gz
cd php-X.X.X
./configure --with-apxs2=/usr/local/apache2/bin/apxs \
                --with-mysql=/usr/local/mysql --prefix=/usr/local/php
make
make install

Configuration

There are several steps to getting the database up and running, configuring Apache to use the php module and setting up the pages that will be using the PHP to MySQL connection.

MySQL

Setting up and loading the initial database is the most difficult part.

First, the initial database needs to be created which will also create two accounts with no password root and anon. Run /usr/local/mysql/bin/mysql_install_db.

The next step is to secure the initial counts the non root account was simply deleted, then the root account was given a password.

First startup the db and login as root:

/usr/local/mysql/bin/mysqld_safe &
/usr/local/mysql/bin/mysql -u root -p

There is no password yet so hit enter to login. Next the anon account is deleted and a password set for root:

DELETE FROM mysql.user WHERE User = '';
DELETE FROM mysql.user WHERE Host='localhost' AND User='';
SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd');
SET PASSWORD FOR ''@'host_name' = PASSWORD('newpwd');
FLUSH PRIVILEGES;

Now it is time to create the user account to access the database. In the text, dba will be used. The dba account will have the same privileges as root for now except GRANT:

GRANT ALL PRIVILEGES ON *.* TO 'dba'@'localhost' IDENTIFIED BY 'newpwd';

Now it is time to create the database and the first table. The NEWS table will only have the following columns for each row:

  1. ID: an interger used as the key and placeholder.
  2. Date which will actually be a charachter string.
  3. The tagline for item as well as a link to it. URLs are absolute to wwwroot.
  4. A short paragraph which is only shown in the current news.

Now time to create it:

/usr/local/mysql/bin/mysql -u dba -p
create database systhread;
use systhread
create table NEWS (
        ID int pri key, 
        date varchar(16), 
        tagline varchar(512), 
        info varchar(2048
);

Now the db is ready for initial use. The date field for the table was made freeform to make changing the format and dates (in case they needed to be) simpler. The NEWS table items are put in reverse to make getting them and updating simpler. At the moment, it is empty, but select * from NEWS; will display the contents of the entire db.

In the following lines, 2 items are inserted by hand:

insert into NEWS values(0, 
        '11/12/2004', 
        'Updated BSDbench tools in the <a href=/coding/</a> section','');

Note that the last field is empty, that is on purpose since it will never be under the current section. A full insert would be the same, simply with the last field filled in.

The rest of the data was loaded the same way because there was so little. It can, however, be loaded from a text file. See the MySQL Reference Manual for more information.

Last and not least, MySQL for systhread runs on the same system and is not accessible from the internet. Additionally, apache and mysql run as different users. I recommend doing the same.

Apache

Apache requires only a few items to be added to the httpd.conf file. For systhread, the addition of using index.php for directory indexes and allowing outputfilter so server side includes still work [2].

Enable the php module
LoadModule php4_module        modules/libphp4.so
Add a Directory Index
DirectoryIndex index.html index.html.var index.php
Add the PHP Type
AddType application/x-httpd-php .php
Server Side Includes
AddType text/html .shtml
AddOutputFilter INCLUDES .shtml .php
...

        Options Includes
        XBitHack on
        AddOutputFilter Includes html

PHP

For PHP a utils file was used for a set of routines that access the systhread database. A copy of the file can be found here. Just modify the username, password and database name plus rename it to utils.php or something similar.

Now it is time to setup the portion of the index.php file that will access the NEWS table and print out the appropiate rows.

<-- begin PHP section -->

<?
echo (<h2>Current Items</h2>\n);
require(/path/to/systhread_utils.php);
$cursql = new MySQL_class;
$cursql->Create(systhread);
$cursql->Query(Select date, tagline, info from NEWS);

Initially a new connection class is created and the NEWS table is queried. Now it is time to order the retrieved data:

echo (<dl>\n);
for ($i = ($cursql->rows - 1); $i >= 0; $i--) {
        $cursql->Fetch($i);
        $date   =  $cursql->data[0];
        $title  =  $cursql->data[1];
        $info   =  $cursql->data[2];

        if ($i >= $cursql->rows - 4) {
                echo(<dt><b>$date: $title</b></dt>);
                echo(<dd>$info</dd>\n);
        }
}

In the first loop, the 4 most recent entries have the entire row formatted for use in a definition list. Note that the counter is set with the (number of rows - 1) because the most recent record inserted was 0. Now for the rest:

echo(</dl>\n);
echo(<h2>Previous Items</h2>\n);
echo(<ul>\n);
$prvsql = new MySQL_class;
$prvsql->Create(systhread);
$prvsql->Query(Select date, tagline, info from NEWS);
for ($i = ($prvsql->rows - 1); $i >= 0; $i--) {
        $prvsql->Fetch($i);
        $date   =  $prvsql->data[0];
        $title  =  $prvsql->data[1];
        if ($i < $prvsql->rows - 4) {
                echo(<li><b>$date:</b> $title.);
        }
}
echo(</ul>\n);
?>

Essentially, the previous section uses the same method but simply prints out different rows, any less than the most recent four.

Summary

The use of mysql and PHP at systhread is marginal. It is relatively small and simple, however, it is a good starting point for those who wish to learn the basics of the LAMP stack, that is Linux, Apache, MySQL and PHP.

Footnotes

  1. Previous installs will only have the required files replaced such as the apache binaries.
  2. The php installation may diff patch the httpd.conf file for some settings.