Databases in the Internet

Electronic Commerce – Exercises
David Billard – University of Geneva, Switzerland

Thema: Using the MySQL Database Management System, the Apache Web Server and the PHP language to build a (very) basic Electronic Commerce Server.
Note: for your convenience, the CEENet staff has already downloaded and compiled all the necessary software. If you want to repeat these exercises in your country, you will have to install the software before beginning the exercises, and to adapt the exercises to your own configuration.

Using MySQL

We will use the MySQL Database Management System (DBMS) (http://www.mysql.com/). This DBMS is free of use under the Linux operating system. It is not very powerful, compared to Oracle, DB2 (IBM) or even PostgreSQL, but it is simple to install and administrate.

Launching the MySQL server

Open a shell as user is_serv2, password is2serv.

Go in the serv/bin directory.

Launch the server: ./safe_mysqld &

Playing with MySQL

Execute the MySQL classical client: ./mysql –u root –p

And enter the usual password.

The –u flag is to specify the user, and –p will prompt you the password.

[is_serv2@test1 bin]$ ./mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 25 to server version: 3.22.32

Type 'help' for help.

mysql>

Indicates that you want to use the administration database, whose name is always mysql: use mysql;

Note that each SQL statement has a ; at the end.

Print at the screen the names of the tables in the database mysql: show tables;

mysql> show tables;

+-----------------+

| Tables in mysql |

+-----------------+

| columns_priv |

| db |

| func |

| host |

| tables_priv |

| user |

+-----------------+

6 rows in set (0.00 sec)

Print at the screen the content of the user table: select * from user;

Print at the screen the content of the attributes host, user and password of the user table.

mysql> select host, user, password from user;

+-----------+-------+------------------+

| host | user | password |

+-----------+-------+------------------+

| localhost | root | 4fdb530c48d73826 |

| pc01 | root | |

| localhost | | |

| pc01 | | |

| localhost | admin | 6f7064001f2be3a2 |

+-----------+-------+------------------+

5 rows in set (0.00 sec)

Create a test database

To help you understand a bit of SQL, you must create a very simple database, composed of two tables:

Instructor(id, name, email, birthdate)

Lecture(id, description, type, price, id_instructor)

id is a numeric identifier, auto-incremented, and is the primary key of the tables.

name, email, description and type are strings (varchar).

price is a real number (float).

name will be indexed and is considered as unique.

We first create the database: create database test1;

Then we create the Instructor table:

CREATE TABLE instructor (

id SMALLINT not null AUTO_INCREMENT,

name VARCHAR (50) not null,

email VARCHAR (50) not null,

birthdate DATE not null,

PRIMARY KEY (id),

INDEX (name),

UNIQUE (name));

Then we create the Lecture table:

CREATE TABLE Lecture (id SMALLINT not null AUTO_INCREMENT,

description VARCHAR (50) not null,

type CHAR DEFAULT 'R' not null,

price FLOAT DEFAULT '0' not null ,

id_instructor SMALLINT,

PRIMARY KEY (id));

Then we record an instructor:

INSERT INTO instructor (id, name, email, birthdate) VALUES ('', 'David Billard', 'David.Billard@adm.unige.ch', NOW(''));

Then we record a lecture:

INSERT INTO Lecture (id, description, type, price, id_instructor) VALUES ('', 'Electronic Commerce', '', '10.5', '1');

Then we print at the screen the content of the tables (you already know how to do it).

Then we want to know the name and email of the instructor of the lecture on Electronic Commerce:

select * from Lecture, instructor where Lecture.id_instructor=instructor.id;

Oops, just the name and email please.

As you can see, it is very tiring to do a single request by hand.

Configuring the Apache web server for its use with PHP

Open a shell as user is_serv4, password is4serv.

Go in the serv/conf directory.

Replace the following line of the httpd.conf file: AddType application/x-httpd-php .php

By this one: AddType application/x-httpd-php .php3 .php

It tells the server to process all the files with the .php or .php3 extension as PHP files.

Launch the server: ../bin/apachectl start

Configuring the phpMyAdmin package

Create and go in a public_html directory (or use a directory usable by the web server) and copy the package source/phpMyAdmin in this directory (cp –R ~/source/phpMyAdmin/* .).

Edit the file config.inc.php3 (add the words in bold):

$cfgServers[1]['stduser'] = 'root'; // MySQL standard user (only needed with advanced auth)

$cfgServers[1]['stdpass'] = 'buda2000'; // MySQL standard password (only needed with advanced auth)

$cfgServers[1]['user'] = 'root'; // MySQL user (only needed with basic auth)

$cfgServers[1]['password'] = 'buda2000'; // MySQL password (only needed with basic auth)

Use Netscape and connect to the public_html/phpMyAdmin/index.php3 page of your web server. You should have something like that:

If you click on the “test1” link at the left of the window, your database structure will appear.

You can then browse your tables, add records, etc…

Exercice 1

Create a new database called ceenet2000.

Create the following set of tables and populate them at your whish:

products(id, description, price)

clients(id, name, email)

orders(id, id_client, id_product, quantity, timestamp)

Create a new directory under public_html, let say ceenet2000, and copy in it all the files which are in the phpMyAdmin directory.

Modify the index.php3 file, so it should looks like that:

In case of trouble modifying the file, there is an example in the appendix.

Exercise 2

Modify or create the necessary files so that the Internet user is able:

To browse the products

To select a product

When the product is selected, the system ask the user his name and email, and the quantity of products he wants to purchase

A record is created in the order table

An email is sent to the user (this will give you a 6^th point is you succeed). Hint: the PHP web server is at http://www.php.net/ and have a look at the exec function.

Notation

Each step is rewarded by 1 point. If you fulfill all the steps you will have 5 points (and 6 points if you manage to send the email of exercise 2).

Appendix

<?php

// Process config file to determine default server (if any)

require('lib.inc.php3');

<html>

<head>

<title>CEENet 2000 (work)SHOP</title>

</head>

<h1>Welcome to the CEENet 2000 Workshop shop</h1>

You will find there all the <a href="sql.php3?server=1&db=test1&table=Lecture&goto=db_details.php3&sql_query=SELECT+%2A+FROM+Lecture&pos=0">lecture materials</a> at virtually no costs!<p>

</html>