Chapter 11: Web Database Programming Using PHP
CHAPTER 11: WEB DATABASE PROGRAMMING USING PHP
Answers to Selected Exercises
11.10
<?php
require ‘DB.php’;
$d = DB::connect(‘oci8://acct1:pass12@www.host.com/dbname’);
if(DB::isError($d)){die(“cannot connect – “.$d->getMessage());}
…
$q=$d->query(”
CREATE TABLE BORROWER(
Card_no INT NOT NULL,
Name VARCHAR,
Address VARCHAR,
Phone VARCHAR,
PRIMARY KEY(Book_id,Author_name),
FOREIGN KEY(Book_id) REFERENCES BOOK(Book_id))
CREATE TABLE BOOK_COPIES(
Book_id INT NOT NULL,
Branch_id INT NOT NULL,
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
Due_date DATE,
PRIMARY KEY(Book_id,Branch_id,Card_no),
FOREIGN KEY(Book_id) REFERENCES BOOK(Book_id),
FOREIGN KEY(Branch_id) REFERENCES LIBRARY_BRANCH(Branch_id),
FOREIGN KEY(Card_no) REFERENCES BORROWER(Card_no))
“);
…
?>
14.11
14.11.1) For a new BORROWER entity
<?php
require ‘DB.php’;
$d = DB::connect(‘oci8://acct1:pass12@www.host.com/dbname’);
print ”
<HTML>
<HEAD>
?>
14.11.2) For a new Book entity
<?php
require ‘DB.php’;
$d = DB::connect(‘oci8://acct1:pass12@www.host.com/db1’);
print ”
<HTML>
<HEAD>
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
?>
14.12
<?php
require ‘DB.php’;
$d = DB::connect(‘oci8://acct1:pass12@www.host.com/dbname’);
if(DB::isError($d)){die(“cannot connect – “.$d->getMessage());}
$d->setErrorHandling(PEAR_ERROR_DIE);
…
$q=$d->query(”
SELECT LB.Branch_name,BC.No_of_copies,B.Title
FROM BOOK B,BOOK_COPIES BC,LIBRARY_BRANCH LB
WHERE B.Book_id=BC.Book_id AND LB.Branch_id=BC.Branch_id AND
B.Title=’The Last Tribe'”);
}
//d.
Chapter 11: Web Database Programming Using PHP
$q=$d->query(”
SELECT B.Book_id,B.Title,BR.Name,BR.Address
FROM BOOK B,BORROWER BR,LIBRARY_BRANCH LB,BOOK_LOANS BL
FROM LIBRARY_BRANCH LB,BOOK_LOANS BL
WHERE LB.Branch_id=BL.Branch_id
GROUP BY LB.Branch_id”);
while ($r = $q->fetchRow())
{
print ” Branch ID. $r[0] ($r[1]) has the total of $r[2] books loaned
total of $r[4] books checked out.”;
}
//g.
$q=$d->query(”
SELECT B.Book_id,B.Title,BC.No_of_copies
FROM BOOK B,BOOK_COPIES BC,LIBRARY_BRANCH LB,BOOK_AUTHORS BA
}
?>