LAB NOTE
A Contact Database with MySQL and PHP
by Mike Miller
Web Techniques, January 1998
Web Techniques grants permission to use these listings (and code) for private or
commercial use provided that credit to Web Techniques and the author is
maintained within the comments of the source. For questions, contact
editors@web-techniques.com.
There is one listing from the magazine in this file:
1. The contact Database
LISTING ONE
Contact Database
/* Script Name: contact.html
Script Purpose: Simple Contact Database Script Version: 1.1
Script Author: Michael J. Miller Jr. (mke@netcom.com)
*/
/* First let's set some variables. */
$hostname = "localhost";
$password = "grok";
$user = "contact";
/* Now to define functions. */
/* The main screen. Printed when $state is empty. */
Function Main_Menu (
echo " Welcome To The Contact Database
";
echo "You have the following choices:";
echo "";
);
/* List the first and last name of people in the contact database plus their
phone number. Make the last name clickable for the full record. */
Function List (
echo "Contact List
";
mysql_connect($hosthame,$user,$password);
$result = mysql("ContactDB","SELECT uid, last_name, first_name, phone FROM
contact ORDER BY last_name");
echo "
";
$total_rows = mysql_numrows($result); $counter = 0;
echo "Last Name | First Name | Work
Phone |
";
while($counter < $total_rows);
$uid = mysql_result($result,$counter,"uid"); echo "\n";
echo "";
echo mysql_result($result,$counter,"last_name");
echo "\n";
echo " | \n";
echo mysql_result($result,$counter,"first_name"); echo " | \n";
echo mysql_result($result,$counter,"phone"); echo " |
\n";
$counter = $counter + 1;
endwhile;
echo "
";
);
/* Save updates to database. Call Print_Contact to list new information
for updated contact entry. */
Function Commit_Update $uid $fn $mi $ln $ph $fx $em (
mysql_connect($hosthame,$user,$password);
$result = mysql("ContactDB","UPDATE contact SET first_name = '$fn',
middle_initial = '$mi', last_name = '$ln', phone = '$ph', fax = '$fx',
email = '$em' WHERE uid = $uid");
$state = "";
Print_Contact($uid);
);
/* Save new contact information to database. Call Print_Contact to display
new contact entry. */
Function Commit_Contact $fn $mi $ln $ph $fx $em (
mysql_connect($hostname,$user,$password);
$result = mysql("ContactDB","SELECT now()"); /* Get the Current Datetime */
/* Note that it would be
better to use a field
of type TIMESTAMP here,
since it would automatically
assign the current date and
time. I haven't done that
here since I wanted to
demonstrate using functions
in MySQL. */
$now = mysql_result($result,0,"now()");
$result = mysql("ContactDB","INSERT INTO contact
(first_name, middle_initial,last_name, phone, fax, email)
VALUES ('$fn', '$mi','$ln', '$ph', '$fx', '$em')");
$new_uid = mysql_insert_id();
$result = mysql("ContactDB","INSERT INTO comment (uid, contact_date,
contact_comment) VALUES ($new_uid, '$now' , 'Contact Record Created')");
$state = "";
Print_Contact($new_uid);
);
/* This function displays the contact nformation in a form. It is used
for both the create and update contact options. */
Function Contact_Form $state,$uid (
if($state == "Update");
$state = "Commit_Update";
$result = mysql("ContactDB","SELECT * FROM contact WHERE uid = $uid");
$last_name = mysql_result($result,0,"last_name");
$first_name = mysql_result($result,0,"first_name");
$middle_initial = mysql_result($result,0,"middle_initial");
$phone = mysql_result($result,0,"phone");
$fax = mysql_result($result,0,"fax"); $email = mysql_result($result,0,"email");
else;
$state = "Commit_Contact";
endif;
echo "Contact Form
\n";
echo "
\n";
);
/* Add a comment to the database. Call Print_Contact to display changes. */
Function Add_Comment $uid $contact_comment (
mysql_connect($hosthame,$user,$password);
$result = mysql("ContactDB","SELECT now()"); /* Get the Current Datetime */
$now = mysql_result($result,0,"now()");
$result = mysql("ContactDB","INSERT INTO comment (uid, contact_date,
contact_comment) VALUES ($uid, '$now', '$contact_comment')");
Print_Contact($uid);
);
/* Print address and comments for a contact. Also give option to update
or add comments. */
Function Print_Contact $uid (
mysql_connect($hosthame,$user,$password);
$result = mysql("ContactDB","SELECT * FROM contact WHERE uid = $uid");
$name = mysql_result($result,0,"first_name");
$name = $name + " " + mysql_result($result,0,"middle_initial");
$name = $name + " " + mysql_result($result,0,"last_name");
echo "Contact Information For $name"; echo "";
echo "";
echo "Contact Information For $name
";
echo "";
echo "Name | ";
echo "$name |
";
$result = mysql("ContactDB","SELECT * FROM contact WHERE uid = $uid");
echo "Phone | ";
echo "";
echo mysql_result($result,0,"phone"); echo " |
\n";
echo "Fax | ";
echo "";
echo mysql_result($result,0,"fax");
echo " |
\n";
$email = mysql_result($result,0,"email");
echo "Email Address | ";
echo " $email";
echo " |
\n";
echo "
";
echo "";
$result = mysql("ContactDB","SELECT * FROM comment WHERE uid = $uid ORDER BY
contact_date");
$total_rows = mysql_numrows($result); $counter = 0;
echo ""; echo "Date | Comment | ";
while($counter < $total_rows);
echo "
";
echo mysql_result($result,$counter,"contact_date"); echo " | ";
echo mysql_result($result,$counter,"contact_comment"); echo " |
";
$counter = $counter + 1;
endwhile;
echo "
";
echo "Add new comment below.";
echo "
";
echo "
\n";
);
/* The main loop. Call functions based on the value of $state, which
gets set via a hidden INPUT TYPE. */
switch($state) {
case "";
Main_Menu();
break;
case "List";
List();
break;
case "Create";
Contact_Form($state, $uid);
break;
case "Update";
Contact_Form($state, $uid);
break;
case "Commit_Update";
Commit_Update($uid,$first_name,$middle_initial,$last_name,$phone,$fax,$ema
il);
break;
case "Commit_Contact";
Commit_Contact($first_name,$middle_initial,$last_name,$phone,$fax,$email); break;
case "Add_Comment";
Add_Comment($uid,$contact_comment);
break;
case "Print_Contact";
Print_Contact($uid);
endswitch;
>