Thursday, September 6, 2012

insert data into one table and update another

Insert data into one table and update another 

 Create two table called "order1" and "order2" in my_database

CREATE TABLE IF NOT EXISTS `order1` (
  `id` int(30) NOT NULL AUTO_INCREMENT,
  `req_id` varchar(30) NOT NULL,
  `Fname` varchar(40) NOT NULL,
  `Type` varchar(40) NOT NULL,
  `subdate` varchar(40) NOT NULL,
  `startdate` varchar(40) NOT NULL,
  `status` varchar(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `req_id` (`req_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;		CREATE TABLE IF NOT EXISTS `order2` (
  `id` int(30) NOT NULL AUTO_INCREMENT,
  `req_id` varchar(30) NOT NULL,
  `Fname` varchar(40) NOT NULL,
  `Type` varchar(40) NOT NULL,
  `subdate` varchar(40) NOT NULL,
  `startdate` varchar(40) NOT NULL,
  `status` varchar(40) NOT NULL,
  `res_officer` varchar(40) NOT NULL,
  `findate` varchar(40) NOT NULL,
  `dildate` varchar(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `req_id` (`req_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Create table for entering data in to table order1

Reception Counter
Registration Number
Name
Request Type
Submitted Date
Started Date
<table bgcolor=#dec09e align="center">
<tr bgcolor=#f1ede8><th colspan=2>Reception Counter</th></tr>
<form action="enter.php" method="post">
<tr bgcolor=#f1ede8><td>Registration Number</td><td><input type="text" name="req_id"></td></tr>
<tr bgcolor=#f1ede8><td>Name</td><td><input type="text" name="Fname"></td></tr>
<tr bgcolor=#f1ede8><td>Request Type</td><td><input type="text" name="Type"></td></tr>
<tr bgcolor=#f1ede8><td>Submitted Date</td><td><input type="text" name="subdate"></td></tr>
<tr bgcolor=#f1ede8><td>Started  Date</td><td><input type="text" name="startdate"></td></tr>
<tr bgcolor=#f1ede8><td colspan=2 align="right"><input type="submit" value="ENTER"/></td></tr>
</form>
</table>

Then create data enter php called "enter.php"

<?php
$req_id=$_POST['req_id'];
$Fname=$_POST['Fname'];
$Type=$_POST['Type'];
$subdate=$_POST['subdate'];

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

$sql="INSERT INTO order1 (id,req_id, Fname, Type,subdate)
VALUES
('','$req_id','$Fname','$Type','$subdate')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con);
?>

Now create file to post Registration Number(req_id) into table order1 to get entered data from the Reception Counter Form

Enter Registration Number
<html>
<head>
<title></title>
</head>

<div align="center">
<table boder="0">
<tr><th colspan="2"BGCOLOR="#91a9db">
Enter Registration Number</th></tr>
<form action="edit.php" method="post">
 <tr><td BGCOLOR="#e3e7f0"> 
<center><input type="text" name="req_id" />
</center></td></tr>
  <tr><td BGCOLOR="#91a9db" colspan="2">  
<center>
<input type="submit" name="submit" value="Submit" />
</center></td></tr>
 </form>
</table> 
</div>

Next create edit.php
 
<?php
$dbHost = 'localhost';
$dbUser = '';
$dbPass = '';
$dbName = 'my_database';
$dbRoot='root';
$dbConn = mysql_connect ($dbHost,$dbRoot, $dbUser, $dbPass) or die ('MySQL connect failed. ' . mysql_error());
mysql_select_db($dbName) or die('Cannot select database. ' . mysql_error());
?>
<html>
<head>
<title></title>
</head>
<body>

<table align="center"  cellpadding=10 cellspacing=3 width="600" border="0" bgcolor=#91a9db>
<tr bgcolor=#91a9db><th colspan=6>VIC-Service Request External</th></tr>
<?php

$req_id=$_POST['req_id'];

$query = "SELECT * FROM order1 where req_id='$req_id'";
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)){
?>
<form action="enter2.php" method="post">



<tr  bgcolor="#ffffff"><th align="right"> Reg. Number <br> </th>
<td ><input readonly name="req_id" value="<?php echo $row["req_id"];?>" /></td>
<th align="right"> Name  </th>
<td><input readonly name="Fname" value="<?php echo $row["Fname"];?>" /></td>
<th align="right">Request Type  </th>
<td><input readonly name="Type" value="<?php echo $row["Type"];?>" /></td></tr>
<tr  bgcolor="#ffffff">
<th align="right">Request date  </th>
<td><input readonly name="subdate" value="<?php echo $row["subdate"];?>" /></td>
<th align="right">Date of Commence  </th>
<td><input  name="startdate" value="<?php echo $row["startdate"];?>" /></td>
<th align="right">Status </th>
<td><input  name="status" value="<?php echo $row["status"];?>" /></td>
</tr>
<tr  bgcolor="#ffffff">
<th align="right">Responsible Officer</th>
<td><input  name="res_officer"/></td>
<th align="right">Date of Finished </th>
<td><input  name="findate"/></td>
<th align="right">Date of Dilivery</th>
<td><input  name="dildate"/></td>
</tr>
<tr><td BGCOLOR="#91a9db" colspan="6">
<center><input type="submit" name="submit" value="Submit" /></center></td></tr>
<?php }?>
</table>
</body>
</html>

Then create enter2.php which update table order1 and data in to table order2

<?php
$req_id=$_POST['req_id'];
$Fname=$_POST['Fname'];
$Type=$_POST['Type'];
$subdate=$_POST['subdate'];
$startdate=$_POST['startdate'];
$status=$_POST['status'];
$res_officer=$_POST['res_officer'];
$findate=$_POST['findate'];
$dildate=$_POST['dildate'];

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

$sql="INSERT INTO order2 (id,req_id, Fname,Type,subdate,startdate,status,res_officer,findate,dildate)
VALUES
('','$req_id','$Fname','$Type','$subdate','$startdate','$status','$res_officer','$findate','$dildate')";

$sql2="UPDATE order1 Set startdate='$startdate',status='$status' WHERE req_id='$req_id'";
mysql_query($sql);
mysql_query($sql2);
mysql_close($con);
echo "Records Added";
?>
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)