PHP and MySQL Library Tutorial

General
Site Root
Database Home
Conect to MySQL
MySQL_Login.php
nav.php
dump.php

Clean Up resets the
database to contain
a few sensible
records.

Download Tutorial

Database
Create
Drop

Person Table
Create
Drop

Insert
Delete
Update
Search
Date-Search

Row Dump
Better View 
Table View 
Form View

Book Table
Create
Drop

Insert
Delete
Update
Search




Table View 
Form View

Loan Table
Create
Drop

Insert
Delete

Search




Table View

Documentation
Data Requirements
User Interface Design
Entity Relationship Diagrams
Data Flow Diagrams
Data Dictionary
Implementation Notes
Table Designs
Goals, Testing and Appraisal

Note - Create, Drop and Clean Up would be hidden on a real life system.
Contact - nbauers at samphire dot demon dot co dot uk
Copyright © - You may use and reproduce this material in a "not for profit" setting. 

SELECT 
  book.book_title, 
  book.accession_num,
  person.title,
  person.first_name, 
  person.last_name,
  person.person_id,
  loan.person_id,
  loan.accession_num,
  loan.return_date
FROM 
  person, book, loan
WHERE
  person.person_id = loan.person_id
AND
  book.accession_num = loan.accession_num
AND
  (book.book_title LIKE '%%' OR person.last_name LIKE '%%')
Enter all or part of the book title or borrower's last name
RESULTS
Ms Mary Jane borrowed Jack Holborn due for return on 2005-01-30.
Mr Fred Bloggs borrowed Chapter House Dune due for return on 2005-01-28.
Mr John Doe borrowed The Hollow Hills due for return on 2005-01-05.

Code Listing : loanSearch.php

 
<?php
  $search_text = htmlentities($_POST['search_text']);
  
  require "../MySQL_Login.php";    // Contains host, username, password, and database
  $link   = mysql_connect($host, $user, $password) or die(mysql_error());
  mysql_select_db ($database) or die(mysql_error());
  $sql    = 
"SELECT 
  book.book_title, 
  book.accession_num,
  person.title,
  person.first_name, 
  person.last_name,
  person.person_id,
  loan.person_id,
  loan.accession_num,
  loan.return_date
FROM 
  person, book, loan
WHERE
  person.person_id = loan.person_id
AND
  book.accession_num = loan.accession_num
AND
  (book.book_title LIKE '%$search_text%' OR person.last_name LIKE '%$search_text%')";
	       
  $result = mysql_query($sql, $link) or die(mysql_error());
  
  mysql_close($link);
?>

<html>
<head>
<title>Search for a Loan</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<?php
  include "nav.php";
  echo"<font size=\"+2\"><b><pre>$sql</pre></b></font>";
?>

<form name="form1" method="post" action="">
  <table border="1" cellpadding="4" cellspacing="0">
    <tr> 
      <td><div align="right">
        Enter all or part of the book title or borrower's last name</div>
      </td>
      <td>
        <input name="search_text" type="text" id="search_text" 
	value="<?php echo $search_text;  ?>">
      </td>
    </tr>
    <tr> 
      <td colspan="2"><div align="right"> 
          <input type="submit" name="Submit" value="Submit">
        </div></td>
    </tr>
  </table>
</form>
  <table border="1" cellspacing="0" cellpadding="4">
    <tr>
      <td><b>RESULTS</b></td>
    </tr>
<?php 
  while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
  {
    echo "    <tr>\n";
    echo "      <td>";
    echo $row['title']       . " ";
    echo $row['first_name']  . " ";
    echo $row['last_name']   . " borrowed ";
    echo $row['book_title']  . " due for return on ";
    echo $row['return_date'] . ".";
    echo "</td>\n";
    echo "    </tr>\n";
  }
?>

  </table>

<?php
  include "dump.php";
  dump_page(basename($_SERVER[SCRIPT_FILENAME]));  // Display the code of this page
?>

</body>
</html>