Advertisment

Publish your Database on the Web

author-image
PCQ Bureau
New Update

PHP is a server-side scripting language like CGI, ASP, and

Java Servlets. It’s easy to use and borrows its syntax from popular

programming languages like C, C++, and Java. It’s available for a number of

platforms and Web Servers like Apache on Linux and IIS (Internet Information

Server) on Win NT and 2000. It can be used in conjunction with many RDBMS. In

this article we’ve used it with MySQL. MySQL is a robust RDBMS database that’s

available for both Linux and Windows. In this article we’ll set up PHP 4 on

Apache Web Server and MySQL on a PCQ Red Hat system. We’ll then use PHP to

publish the data in a MySQL database on the Web. So let’s get started.

Advertisment

Setting up MySQL and PHP 4

Log in as root. Ensure that you have apache installed on your

machine with the apache development libraries. Check this by using the following

command:

rpm -qa | grep apache

Advertisment

If you see two RPMs named apache and apache-devel, then we

are ready to sprint, else change to the RPMs directory of the PCQ Red Hat CD and

install the two RPMs as:

rpm -ivh apache*

Next mount this month’s PCQ CD and change to the directory

/mnt/cdrom/cdrom/linux/mysql and install MySQL RPMs:

Advertisment

rpm -ivh MySQL*

Then as recommended during installation, change the password

of the root user:

mysqladmin -u root password pcq

Advertisment

Here we have changed the password to ‘pcq’.

Now we’ll set up PHP 4. Change to the directory /mnt/cdrom/cdrom/linux/php4

and copy the file php-4.0.3pl1.tar.gz to any directory say /opt. Change to /opt

directory and type the following to uncompress and extract PHP files:

tar -zxvf php-4.0.3pl1.tar.gz

Advertisment

This would create a directory named php-4.0.3pl1 in /opt.

Change to this directory and issue the following commands:

./configure –with-mysql –with-apxs

make

Advertisment

make install

This would install the PHP 4 module for apache. Now edit the

file httpd.conf in /etc/httpd/conf directory and add the following line in the

end:

AddType application/x-httpd-php .php

Advertisment

This defines the MIME type of PHP script files to Apache. In

other words, it tells Apache that any file with a PHP extension must be treated

as a PHP script and given to the PHP parser, which would analyze and execute the

PHP scripts.

Now restart Apache Web Server:

/etc/rc.d/init.d/httpd restart

At this point if you get an error message saying, ‘cannot

load /etc/httpd/lib/apache/libphp4.so’, then edit the file httpd.conf again

and look for a line which says:

LoadModule php4_module



lib/apache/libphp4.so

Replace lib/apache/libphp4.so to modules/libphp4.so, so that

the line looks like:

LoadModule php4_module modules/libphp4.so

Restart Apache once again.

Testing the setup

We’ll now write a simple script to test if everything is up

and running. This php script just tries to connect to the MySQL server.

In the directory /home/httpd/html, create a file test.php and

enter the following:

PHP and MySQL test

$connection=mysql_connect ("localhost","root","pcq");

echo "A simple test"

?>

Note that this file seems to be a normal HTML file due to the

presence of familiar HTML tags. But the extension of the file (PHP) instructs

Apache to invoke the PHP parser. The PHP parser sends all lines, except the

lines between . The lines between these tags are processed by

the PHP parser. The first line between these connects to the MySQL server

running on the same machine (specified by local host), using the username ‘root’

and password ‘pcq’.

Next fire up your browser, say Netscape Navigator or Lynx and

feed in the URL http://localhost/test.php. If you see the message ‘A simple

test’ and no error messages then everything is setup properly. However, if you

do see an error message, like ‘MySQL connection failed’ then make sure that

the MySQL server is running by using the following command:

/etc/rc.d/init.d/mysql start

Publishing a database

From

here, we’ll assume that you’re familiar with SQL queries. Suppose we are

running an e-commerce site, which maintains a list of products along with their

product ID and price in a table named ‘info’, which is in a database named

‘products’. We’ll first have to create the database by typing the

following at the shell prompt:

mysqladmin create products -p

You’ll be prompted for the password (pcq) and subsequently

the database would be created.

Now type:

mysql -p

Enter the password again. You’ll get a mysql prompt. Next

type the following at the mysql prompt:

use products;

create table info (id char(10) NOT NULL, product char(20),

price float, PRIMARY KEY id);

This creates a table named info with fields: id, product (the

products name), and its price (say in Rs). The next step is to populate this

table with information. For example, to enter information about an Apple iMAC

computer issue the following while still in the mysql prompt:

insert into info values ("0123456789","Apple iMac",80000.00);

Repeat this line for inserting some more products, each time

replacing the information in the brackets ( ). Now exit from MySQL by typing

quit and create a file named product.php in the /home/httpd/html directory and

add the following linest:

Product Information

Product Information

$connection = mysql_connect("localhost","root","pcq");

mysql_select_db("products",$connection);

$query_result = mysql_query("select * from info");

while ($row = mysql_fetch_array($query_result))

{

echo "

";

echo "

echo $row<"id">;

echo "

";

echo "

echo $row<"product">;

echo "

";

echo "

echo $row<"price">;

echo "

";

echo "

";

}

?>

Product ID Product Price (in Rs)
"; "; ";

Let’s throw some light on the lines between the and ?> tags. First, a connection to the server is made. Then in the second

line, the MySQL database named ‘product’ is selected. Now we are ready to

query the database. This is done by the PHP function mysql_query ( ), where we

select all the information stored in the table ‘info’. The mysql_query( )

can execute any SQL query like we could also create tables as well as insert,

delete, or modify the information in them.

The result of the query is stored in a variable named

query_result. This variable contains a two dimensional array of row by row

information extracted from the query. Thus we iterate through this variable

using a while loop. At each iteration one row is fetched and stored in the

variable $row. The individual elements are then extracted using the name of the

field, which are ‘id’, ‘product’, and ‘price’.

Now open the URL, http://127.0.0.1/product.php. You will see

the product information in a tabular format and this page has been created

dynamically by PHP by looking into the MySQL database. PHP can also connect and

query other databases like Oracle, MS SQL Server, or any other ODBC database

like Access, etc. Only the corresponding PHP functions differ like mssql_query(

), odbc_connect. PHP has extensive documentation, which is also given on this

month’s CD for your reference.

Shekhar Govindarajan

Advertisment