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
Welcome To The Contact Database
"; echo "You have the following choices:"; echo "
"; echo " List Contacts.
"; echo "Create a new Contact.

"; echo ""; 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 ""; while($counter < $total_rows); $uid = mysql_result($result,$counter,"uid"); echo "\n"; $counter = $counter + 1; endwhile; echo "
Last NameFirst NameWork Phone
\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 "
"; ); /* 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"; echo " \n"; echo " \n"; echo " First Name:

\n"; echo " Middle Initial:

\n"; echo " Last Name:

\n"; echo " Phone:

\n"; echo " Fax:

\n"; echo " Email:

\n"; echo "

\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 ""; echo ""; $result = mysql("ContactDB","SELECT * FROM contact WHERE uid = $uid"); echo ""; echo "\n"; echo ""; echo "\n"; $email = mysql_result($result,0,"email"); echo ""; echo "\n"; echo "
Name$name
Phone"; echo mysql_result($result,0,"phone"); echo "
Fax"; echo mysql_result($result,0,"fax"); echo "
Email Address $email"; echo "
"; echo "
"; echo " \n"; echo " \n"; echo "
"; echo "


"; echo "

Comments For This Contact

"; echo "
"; $result = mysql("ContactDB","SELECT * FROM comment WHERE uid = $uid ORDER BY contact_date"); $total_rows = mysql_numrows($result); $counter = 0; echo ""; echo ""; while($counter < $total_rows); echo ""; $counter = $counter + 1; endwhile; echo "
DateComment
"; echo mysql_result($result,$counter,"contact_date"); echo ""; echo mysql_result($result,$counter,"contact_comment"); echo "
"; echo "

Add new comment below."; echo "

"; echo "


"; echo "

"; echo "

"; echo "

"; echo "

"; echo "

"; 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; >