Lecture Notes Ten: Preparing for E-Commerce

We have a number of books in our bookstore.


dek001.jpg

dek002.jpg

dek003.jpg

mun001.jpg

fri001.jpg

fri002.jpg

fey001.jpg

kob001.jpg

con001.jpg

con002.jpg

hpb001.jpg

dek001.jpg

pes001.jpg

gkp001.jpg

fri003.jpg

sus001.jpg

We sell books.

Do we have a database?

We will come up with the following structure:

Let's focus on getting the BOOKS in, first.

burrowww.cs.indiana.edu% mysql -ua348 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 591 to server version: 3.23.27-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer

mysql> use a348
Database changed
mysql> show tables like "dgerman_BOOKS";
Empty set (0.01 sec)

mysql> create table dgerman_BOOKS (
    ->   isbn varchar(10) primary key, 
    ->   price float
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> describe dgerman_BOOKS;
+-------+-------------+------+-----+---------+-------+---------------------------------+
| Field | Type        | Null | Key | Default | Extra | Privileges                      |
+-------+-------------+------+-----+---------+-------+---------------------------------+
| isbn  | varchar(10) |      | PRI |         |       | select,insert,update,references |
| price | float       | YES  |     | NULL    |       | select,insert,update,references |
+-------+-------------+------+-----+---------+-------+---------------------------------+
2 rows in set (0.00 sec)

mysql> select * from dgerman_BOOKS;
Empty set (0.04 sec)

mysql> insert into dgerman_BOOKS values ('dek001', 50.00);
Query OK, 1 row affected (0.00 sec)

mysql> select * from dgerman_BOOKS;
+--------+-------+
| isbn   | price |
+--------+-------+
| dek001 |    50 |
+--------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye
burrowww.cs.indiana.edu% 
Let's see how we can access this.

burrowww.cs.indiana.edu% pwd
/nfs/paca/home/user1/dgerman
burrowww.cs.indiana.edu% mkdir e-commerce
burrowww.cs.indiana.edu% cd e-commerce
burrowww.cs.indiana.edu% pwd
/nfs/paca/home/user1/dgerman/e-commerce
burrowww.cs.indiana.edu% pico prog
burrowww.cs.indiana.edu% ls -ld prog
-rw-r--r--   1 dgerman  faculty       645 Sep 27 12:27 prog
burrowww.cs.indiana.edu% chmod 700 prog
burrowww.cs.indiana.edu% ./prog
dek001  50
burrowww.cs.indiana.edu% cat prog
#!/usr/bin/perl

use DBI; 

my ($dsn) = "DBI:mysql:a348"; # data source name 
my ($username) = "a348";      # username
my ($password) = "a348AG";    # password
my ($dbh, $sth);              # database and statement handles
my (@ary);                    # array for rows returned by query

# connect to the database 
$dbh = DBI->connect($dsn, $username, $password, { RaiseError => 1 }); 

#issue query
$sth = $dbh->prepare("select * from dgerman_BOOKS");  
$sth->execute(); 

# read results of query, then clean up
while (@ary = $sth->fetchrow_array()) {
  print join ("\t", @ary), "\n"; 
}

$sth->finish(); 

$dbh->disconnect();
exit(0); 
burrowww.cs.indiana.edu%
How does it go over the web?

burrowww.cs.indiana.edu% pwd
/nfs/paca/home/user1/dgerman/e-commerce
burrowww.cs.indiana.edu% cd ../apa*/apa*20/cg*
burrowww.cs.indiana.edu% pwd
/nfs/paca/home/user1/dgerman/apache/apache_1.3.20/cgi-bin
burrowww.cs.indiana.edu% cp ~/e-com*/prog .
burrowww.cs.indiana.edu% ls -ld prog
-rwx------   1 dgerman  faculty       639 Sep 27 12:31 prog
burrowww.cs.indiana.edu% pico prog
burrowww.cs.indiana.edu% cat prog
#!/usr/bin/perl

use CGI;

$q = new CGI; 

print $q->header, $q->start_html; 

use DBI; 

my ($dsn) = "DBI:mysql:a348"; # data source name 
my ($username) = "a348";      # username
my ($password) = "a348AG";    # password
my ($dbh, $sth);              # database and statement handles
my (@ary);                    # array for rows returned by query

# connect to the database 
$dbh = DBI->connect($dsn, $username, $password, { RaiseError => 1 }); 

#issue query
$sth = $dbh->prepare("select * from dgerman_BOOKS");  
$sth->execute(); 

# read results of query, then clean up
while (@ary = $sth->fetchrow_array()) {
  # print join ("\t", @ary), "\n"; 
  $pic = $ary[0]; 
  print qq{
    <img src="http://www.cs.indiana.edu/classes/a114-dger/fall2001/lectures/nine/$pic.jpg">
  }; 
}

$sth->finish(); 

$dbh->disconnect();

print $q->end_html; 

exit(0); 
burrowww.cs.indiana.edu% 

Interesting, I suppose.

Can we initialize the entire table from a text file?

Let's see...

burrowww.cs.indiana.edu% pwd
/nfs/paca/home/user1/dgerman/apache/apache_1.3.20/cgi-bin
burrowww.cs.indiana.edu% cd
burrowww.cs.indiana.edu% cd e-comm*
burrowww.cs.indiana.edu% pico data
burrowww.cs.indiana.edu% cat data
dek001:50.00
dek002:55.50
dek003:30.75
mun001:14.25
fri001:34.95
fri002:20.00
fey001:99.99
kob001:40.00
con001:12.00
con002:48.68
hpb001:11.12
dek001:28.34
pes001:34.56
gkp001:80.01
fri003:99.99
sus001:46.75
burrowww.cs.indiana.edu% pico read
burrowww.cs.indiana.edu% cat read
#!/usr/bin/perl

open (AB, "data"); 
while ($line = <AB>) {
  ($isbn, $price) = split(/:/, $line); 
  
}
close(AB); 
burrowww.cs.indiana.edu% chmod 700 read
burrowww.cs.indiana.edu% ./read
burrowww.cs.indiana.edu% 
We're getting closer.

Let's now load the data.

burrowww.cs.indiana.edu% ./prog
dek001  50
burrowww.cs.indiana.edu% ./upl
burrowww.cs.indiana.edu% ./prog
dek001  50
dek002  55.5
dek003  30.75
mun001  14.25
fri001  34.95
fri002  20
fey001  99.99
kob001  40
con001  12
con002  48.68
hpb001  11.12
pes001  34.56
gkp001  80.01
fri003  99.99
sus001  46.75
burrowww.cs.indiana.edu% cat upl
#!/usr/bin/perl

use DBI; 

my ($dsn) = "DBI:mysql:a348"; # data source name 
my ($username) = "a348";      # username
my ($password) = "a348AG";    # password
my ($dbh, $sth);              # database and statement handles
my (@ary);                    # array for rows returned by query

# connect to the database 
$dbh = DBI->connect($dsn, $username, $password, { RaiseError => 1 }); 

open (AB, "data"); 
while ($line = <AB>) {
  ($isbn, $price) = split(/:/, $line); 

  if ($isbn ne "dek001") {
    $dbh->do("insert into dgerman_BOOKS values ('$isbn', '$price')"); 
  }

}
close(AB); 

$dbh->disconnect();
exit(0); 

burrowww.cs.indiana.edu% 
Now, what does prog return?

How do you build a shopping cart?


Last updated: Sep 27, 2001 by Adrian German for A348/A548