PHP 5 Database Form Example
February 27, 2012 Leave a comment
The following example shows how to integrate all the database operation (insert, update, delete and select) in one PHP script. It is in PHP5 and MySQL, and can be easily modified to any DBMS.
Contact Management Main Form
Contact Management Edit Form
ContactManagement.php
<?
/*
CREATE TABLE contacts (
`id` int(10) NOT NULL auto_increment,
`first_name` varchar(30) NOT NULL default '',
`last_name` varchar(50) NOT NULL default '',
`email` varchar(75) default NULL,
`contact_status` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM ;
*/
?>
<html>
<head>
<title>Manage Contact's data</title>
</head>
<body>
<?php
/* control code for application */
//submit button was pressed so call the process form function
if (isset($_POST['submit']))
{
process_form();
die();
}//end if
//call the get_data function
if (isset($_GET['id']))
{
get_data();
}//endif
//nothing chosen so list the contacts
if ((empty($_POST))&&(empty($_GET)))
{
list_users();
die();
}//end if
//request to add a new contact so call the show_form function
if ((isset($_GET['action']))&&($_GET['action']=='add'))
{
show_form();
}//endif
//request to add a new contact so call the show_form function
if ((isset($_GET['action']))&&($_GET['action']=='delete')&&(isset($_GET['myid'])))
{
/*
if (isset($_GET['myid'])){
$msg = "to be delete:id=".$_GET['myid'];
}else {
$msg = "to be delete";
}
*
*/
$id=$_GET['myid'];
$sql = "delete from contacts where id = $id";
$msg = "Record successfully deleted.";
execute_sql($sql,$msg);
// list_users();
die();
}//endif
/* get the data for an individual contact */
function get_data()
{
//validate the id has been passed at that it is a number
if ((empty($_GET['id']))||(is_nan($_GET['id'])))
{
//there was a problem so list the users again
list_users();
//kill the script
die();
}else{
//all is ok and assign the data to a local variable
$id = $_GET['id'];
}//end if
$sql = "select * from contacts where id = $id";
$result = conn($sql);
if (mysql_num_rows($result)==1){
//call the form and pass it the handle to the resultset
show_form($result);
}else{
$msg = "No data found for selected contact";
confirm($msg);
//call the list users function
list_users();
}//end if
}//end function
/* show the input / edit form*/
function show_form($handle='',$data='')
{
//$handle is the link to the resultset, the ='' means that the handle can be empty / null so if nothing is picked it won't blow up
//set default values
$first_name = '';
$last_name = '';
$email = '';
$status = '';
$id = '';
$value = 'Add'; //submit button value
$action = 'add'; //default form action is to add a new kid to db
//set the action based on what the user wants to do
if ($handle)
{
$action = "edit";
$value = "Update";
//get the values from the db resultset
$row = mysql_fetch_array($handle);
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$email = $row['email'];
$status = $row['contact_status'];
$id = $row['id'];
}
//error handling from the processing form function
if($data != '')
{
$elements = explode("|",$data);
$first_name = $elements[0];
$last_name = $elements[1];
$email = $elements[2];
$id = $elements[3];
}
?>
<body>
<form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>?action=<?php echo $action?>">
<table width="400" align="center" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2" align="center" style="font-size:18px; font-weight:bold;">Manage Contact's Data Form</td>
<input type="hidden" value="<?php echo $id?>" name="id">
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td align="right">First Name: </td>
<td><input name="first_name" type="text" value="<?php echo $first_name?>"> </td>
</tr>
<tr>
<td align="right">Last Name: </td>
<td><input name="last_name" type="text" value="<?php echo $last_name?>"> </td>
</tr>
<tr>
<td align="right">Email Address: </td>
<td><input name="email" type="text" value="<?php echo $email?>"> </td>
</tr>
<tr>
<td align="right">Stop Contact? </td>
<td><input name="status" type="checkbox" value="1" <?php if ($status==1){ echo " checked=CHECKED "; } ?>> </td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
<tr>
<td colspan="2" align="center"><input name="submit" type="submit" value="<?php echo $value?>"> <input name="reset" type="reset" value="Clear Form"></td>
</tr>
</table>
</form>
</body>
<?
}//end function
/* list all the contacts in the db */
function list_users()
{
$y = 0; //counter
$sql = "select * from contacts "; //may want to add the option where clause to only take kids with an active status
$result = conn($sql);
echo "<table width='400' align='center' cellpadding='0' cellspacing='0'>
<tr><td colspan='2' align='center' style='font-size:18px; font-weight:bold;'>Manage Contacts Data Form</td></tr>
<tr><td colspan='2'> </td></tr>
<tr><td colspan='2'><a href='".$_SERVER['PHP_SELF']."?action=add'>Add a new contact</a></td></tr>
<tr><td colspan='2'> </td></tr>";
if (mysql_num_rows($result)){
//show a list of kids with name as a link to the prepopulated form with their data in it
while($rows = mysql_fetch_array($result)){
//change row background color
(($y % 2) == 0) ? $bgcolor = "#8FBC8F" : $bgcolor=" #9ACD32";
//build strings to make life easier
$name = $rows['first_name'].' '.$rows['last_name'];
$status = $rows['contact_status'];
$id = $rows['id'];
//convert status to readable string from 1 or 0
($status == 0) ? $status = "Available to contact" : $status = "Do not contact at present.";
//echo out the row
echo "<tr style='background-color:$bgcolor;'><td><a href='".$_SERVER['PHP_SELF']."?id=$id'>$name</a></td><td>$status</td><td><a href='".$_SERVER['PHP_SELF']."?action=delete&myid=$id'>delete</a></td><tr>";
$y++; //increment the counter
}//end while
echo "</table>";
}else{
//handle no results
echo "<tr><td colspan='2' align='center'><b>No data found.</b></td></tr>";
}//endif
}
/* add / update the contact's data*/
function process_form()
{
$fname = '';
$lname = '';
$email = '';
$id = '';
$action = '';
$status = 0; //default value
$fname = @$_POST['first_name'];
$lname = @$_POST['last_name'];
$email = @$_POST['email'];
$id = @$_POST['id'];
$action = @$_GET['action'];
$status = @$_POST['status'];
//if no status is set, defaults to 0 (allow contact)
if ($status == ''){$status = 0; }
if (($fname=='')||($lname=='')||($email==''))
{
$msg = "Some data from the form was forgotten. Please fill in the entire form.";
confirm($msg);
$data = "$fname|$lname|$email|$id";
show_form('',$data);
die();
}//end if
//You could add some validation of the data ( I recommend it and its a great way to get your feet wet with php )
if ($action == "add")
{
$sql = "insert into contacts (first_name, last_name, email, contact_status) values('$fname','$lname','$email',$status)";
$msg = "Record successfully added.";
}elseif($action=="edit"){
$sql = "update contacts set first_name = '$fname', last_name = '$lname', email = '$email', contact_status = '$status' where id = $id";
$msg = "Record successfully updated.";
}
execute_sql($sql,$msg);
}
function execute_sql($sql, $msg)
{
$result = conn($sql);
if (mysql_errno()==0)
{
confirm($msg);
list_users();
}else{
$msg = "There was a problem adding the user to the database. Error is:".mysql_error();
confirm($msg);
}//end if
}
/* db connection function */
function conn($sql)
{
$host = "localhost";
$user = "testuser";
$pass = "testgo";
$db = "test";
//echo "commnecing connection to local db<br>";
if (!($conn=mysql_connect($host, $user, $pass))) {
printf("error connecting to DB by user = $user and pwd=$pass");
exit;
}
$db3=mysql_select_db($db,$conn) or die("Unable to connect to local database");
$result = mysql_query($sql) or die ("Can't run query because ". mysql_error());
return $result;
}//end function
/* alert box popup confimation message function */
function confirm($msg)
{
echo "<script langauge=\"javascript\">alert(\"".$msg."\");</script>";
}//end function
?>
Henry Chen 