PHP 5 Database Form Example

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 Main Form

Contact Management Edit 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 

?>

About henry416
I am a computer technology explorer and an university student based on Toronto. If you have any question, please feel free to discuss and comment here

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s