okay, the idea of duplicating information from 1 table to another is quite simple.
I got 2 tables. 1 is job history table and the other one is current employement table.
If A change a job and wants to enter the new information of his job. We will actually update the information in the current employement table. But for job history table, it will have duplicate entries about the person(example: A) about their existing jobs. So that they could keep records about their jobs. So firstly, the codes should first retrieve the data from the current employment table and insert to job history table before the new information is update in the current employment table.
So my job history table will have multiple columns of the same person and only the start date and end date will differentiate when is the order of the job. There is no primary key of that table so to allow duplicate entires.
Code: Select all
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1-traditional.dtd">
<?php
//Connect to MySQL
$con = mysql_connect("localhost", "xx", "xxx");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("xxxx") or die("Could not select db:" . mysql_error());
$Stud_ID = $_POST['Stud_ID'];
$Occupation = $_POST['Occupation'];
$Address = $_POST['Address'];
$Stud_OContact = $_POST['Stud_OContact'];
$Stud_HContact = $_POST['Stud_HContact'];
$Stud_HPContact = $_POST['Stud_HPContact'];
$Email = $_POST['Email'];
$Work_Industry = $_POST['Work_Industry'];
$Company = $_POST['Company'];
$Job_Position = $_POST['Job_Position'];
$Department = $_POST['Department'];
$Job_Description = $_POST['Job_Description'];
$Current_Work_Industry = $_POST['Current_Work_Industry'];
$Current_Job_Position = $_POST['Current_Job_Position'];
$Current_Company = $_POST['Current_Company'];
$Current_Department = $_POST['Current_Department'];
$Current_Job_Description = $_POST['Current_Job_Description'];
$Stud_ID = mysql_real_escape_string($Stud_ID);
$workIndustry = mysql_real_escape_string($Work_Industry);
$Job_Position = mysql_real_escape_string($Job_Position);
$Company = mysql_real_escape_string($Company);
$Department = mysql_real_escape_string($Department);
$Job_Description = mysql_real_escape_string($Job_Description);
$Current_workIndustry = mysql_real_escape_string($Current_Work_Industry);
$Current_Job_Position = mysql_real_escape_string($Current_Job_Position);
$Current_Company = mysql_real_escape_string($Current_Company);
$Current_Department = mysql_real_escape_string($Current_Department);
$Current_Job_Description = mysql_real_escape_string($Current_Job_Description);
if (!$Stud_ID)
echo 'Please enter Student Number.';
if ($Current_Work_Industry | $Current_Company | $Current_Job_Position | $Current_Department | $Current_Job_Description)
{
$query = "Select * FROM currentemploymentdetail WHERE Stud_ID = '$Stud_ID'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$studID = $row['Stud_ID'];
$workIndustry = $row['Work_industry'];
$company = $row['Company'];
$jobPosition = $row['Job_Position'];
$department = $row['Department'];
$jobDescription = $row['Job_Description'];
$query2 = "INSERT INTO jobhistory (Stud_ID, Work_Industry, Company, Job_Position, Department, Job_Description) VALUES ($studid, $workIndustry, $company, $jobPosition, $department, $jobDescription)";
mysql_query($query2) or die("error....");
}
else {
die('You have to enter all details of the student current employment details');
}
if ($Occupation) {
$sql1 = "UPDATE Student SET Occupation='$Occupation' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql1))
{
die('Error: ' . mysql_error());
}
}
if ($Address) {
$sql2 = "UPDATE Student SET Address='$Address' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql2))
{
die('Error: ' . mysql_error());
}
}
if ($Stud_OContact) {
$sql3 = "UPDATE Student SET Stud_OContact='$Stud_OContact' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql3))
{
die('Error: ' . mysql_error());
}
}
if ($Stud_HContact) {
$sql4 = "UPDATE Student SET Stud_HContact='$Stud_HContact' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql4))
{
die('Error: ' . mysql_error());
}
}
if ($Stud_HPContact) {
$sql5 = "UPDATE Student SET Stud_HPContact='$Stud_HPContact' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql5))
{
die('Error: ' . mysql_error());
}
}
if ($Email) {
$sql6 = "UPDATE Student SET Email='$Email' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql6))
{
die('Error: ' . mysql_error());
}
}
if ($Current_Work_Industry) {
$sql7 = "UPDATE CurrentEmploymentDetail SET Current_Work_Industry='$Current_Work_Industry' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql7))
{
die('Error: ' . mysql_error());
}
}
if ($Current_Job_Position) {
$sql8 = "UPDATE CurrentEmploymentDetail SET Current_Job_Position='$Current_Job_Position' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql8))
{
die('Error: ' . mysql_error());
}
}
if ($Current_Job_Description) {
$sql9 = "UPDATE CurrentEmploymentDetail SET Current_Job_Description='$Current_Job_Description' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql9))
{
die('Error: ' . mysql_error());
}
}
if ($Current_Department) {
$sql10 = "UPDATE CurrentEmploymentDetail SET Current_Department='$Current_Department' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql10))
{
die('Error: ' . mysql_error());
}
}
echo "Student Updated";
mysql_close($con);
?>
I followed desmi's suggestion however it doesnt even execute, and even display "error...." which is at line 62. This means that it is unable to excute the insert statement.
But when i changed back to my previous code(haven't remove the $con) and added some if-else statement so to know which codes went wrong.
Code: Select all
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1-traditional.dtd">
<?php
//Connect to MySQL
$con = mysql_connect("localhost", "xx", "xxx");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("xxxx") or die("Could not select db:" . mysql_error());
$Stud_ID = $_POST['Stud_ID'];
$Occupation = $_POST['Occupation'];
$Address = $_POST['Address'];
$Stud_OContact = $_POST['Stud_OContact'];
$Stud_HContact = $_POST['Stud_HContact'];
$Stud_HPContact = $_POST['Stud_HPContact'];
$Email = $_POST['Email'];
$Work_Industry = $_POST['Work_Industry'];
$Company = $_POST['Company'];
$Job_Position = $_POST['Job_Position'];
$Department = $_POST['Department'];
$Job_Description = $_POST['Job_Description'];
$Current_Work_Industry = $_POST['Current_Work_Industry'];
$Current_Job_Position = $_POST['Current_Job_Position'];
$Current_Company = $_POST['Current_Company'];
$Current_Department = $_POST['Current_Department'];
$Current_Job_Description = $_POST['Current_Job_Description'];
$Stud_ID = mysql_real_escape_string($Stud_ID);
$workIndustry = mysql_real_escape_string($Work_Industry);
$Job_Position = mysql_real_escape_string($Job_Position);
$Company = mysql_real_escape_string($Company);
$Department = mysql_real_escape_string($Department);
$Job_Description = mysql_real_escape_string($Job_Description);
$Current_workIndustry = mysql_real_escape_string($Current_Work_Industry);
$Current_Job_Position = mysql_real_escape_string($Current_Job_Position);
$Current_Company = mysql_real_escape_string($Current_Company);
$Current_Department = mysql_real_escape_string($Current_Department);
$Current_Job_Description = mysql_real_escape_string($Current_Job_Description);
if (!$Stud_ID)
echo 'Please enter Student Number.';
if ($Current_Work_Industry | $Current_Company | $Current_Job_Position | $Current_Department | $Current_Job_Description)
{
$query = "Select * FROM currentemploymentdetail WHERE Stud_ID = '$Stud_ID'";
$result = mysql_query($query, $con);
if ($row = mysql_fetch_array($result)){
echo "Hello";
}
else {
echo "Oh NO!!!";
}
$studID = $row['Stud_ID'];
$workIndustry = $row['Work_industry'];
$company = $row['Company'];
$jobPosition = $row['Job_Position'];
$department = $row['Department'];
$jobDescription = $row['Job_Description'];
$query2 = "INSERT INTO jobhistory (Stud_ID, Work_Industry, Company, Job_Position, Department, Job_Description) VALUES ($studid, $workIndustry, $company, $jobPosition, $department, $jobDescription)";
$result2 = mysql_query($query2, $con);
if ($row2 = mysql_fetch_array($result2)){
echo "Oh my god";
}
else {
echo "Still doesn't work";
}
}
else {
die('You have to enter all details of the student current employment details');
}
if ($Occupation) {
$sql1 = "UPDATE Student SET Occupation='$Occupation' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql1, $con))
{
die('Error: ' . mysql_error());
}
}
if ($Address) {
$sql2 = "UPDATE Student SET Address='$Address' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql2, $con))
{
die('Error: ' . mysql_error());
}
}
if ($Stud_OContact) {
$sql3 = "UPDATE Student SET Stud_OContact='$Stud_OContact' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql3, $con))
{
die('Error: ' . mysql_error());
}
}
if ($Stud_HContact) {
$sql4 = "UPDATE Student SET Stud_HContact='$Stud_HContact' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql4, $con))
{
die('Error: ' . mysql_error());
}
}
if ($Stud_HPContact) {
$sql5 = "UPDATE Student SET Stud_HPContact='$Stud_HPContact' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql5, $con))
{
die('Error: ' . mysql_error());
}
}
if ($Email) {
$sql6 = "UPDATE Student SET Email='$Email' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql6, $con))
{
die('Error: ' . mysql_error());
}
}
if ($Current_Work_Industry) {
$sql7 = "UPDATE CurrentEmploymentDetail SET Current_Work_Industry='$Current_Work_Industry' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql7, $con))
{
die('Error: ' . mysql_error());
}
}
if ($Current_Job_Position) {
$sql8 = "UPDATE CurrentEmploymentDetail SET Current_Job_Position='$Current_Job_Position' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql8, $con))
{
die('Error: ' . mysql_error());
}
}
if ($Current_Job_Description) {
$sql9 = "UPDATE CurrentEmploymentDetail SET Current_Job_Description='$Current_Job_Description' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql9, $con))
{
die('Error: ' . mysql_error());
}
}
if ($Current_Department) {
$sql10 = "UPDATE CurrentEmploymentDetail SET Current_Department='$Current_Department' WHERE Stud_ID='$Stud_ID'";
if(!mysql_query($sql10, $con))
{
die('Error: ' . mysql_error());
}
}
echo "Student Updated";
mysql_close($con);
?>
It display the following ouput.
Hello
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xx\xxx\xxxx\xxxxx\xxxxxx.php on line 72
Still doesn't workStudent Updated
This means that my select statement works but not for my insert statement. Which will leads to new information are updated to the database but the job history table still remain the same.
What's wrong with my codes?
Can anyone help me??