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% 

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%

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% 

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% 

How do you build a shopping cart?