<?php
require_once '../mainfunctions.php';
$conn = createConn();
printHead("3", "Lab 3.6", "Update and delete data from a PHP script");
if(isset($_POST['confirm']))
// if CONFIRM BUTTON CLICKED... (which will send you to 3.3 after updating datbase)
{
$valid=true;
/////////////////////////////////////START GATHERING DATA FROM UPDATE FORM////////////////////////////////////////
$eid=$_POST['eid'];//gather contents from 'eid' hidden field on form below
$firstname = mysqli_real_escape_string($conn, trim($_POST['firstname']));
if (empty($firstname)){echo "<p class='error'>Please Enter Your First Name</p>";$valid=false;}
$lastname = mysqli_real_escape_string($conn, trim($_POST['lastname']));
if (empty($lastname)) {echo "<p class='error'>Please enter your last name</p>";$valid = false;}
$firstname = ucfirst(strtolower($firstname));
$lastname = ucfirst(strtolower($lastname));
$reportsto = $_POST['reportsto'];
$title = mysqli_real_escape_string($conn, trim($_POST['title']));
if (empty($title)) {echo "<p class='error'>Please enter a title</p>";$valid = false;}
$bmo = mysqli_real_escape_string($conn, trim($_POST['bmo']));
if (empty($bmo)) {echo "<p class='error'>Please enter a birth month</p>";$valid = false;}
if ($bmo<1 or $bmo>12) {echo "<p class='error'>Birth month must be between 1 and 12</p>";$valid = false;}
$bday = mysqli_real_escape_string($conn, trim($_POST['bday']));
if (empty($bday)) {echo "<p class='error'>Please enter a birth day</p>";$valid = false;}
if ($bday<1 or $bday>31) {echo "<p class='error'>Birth day must be between 1 and 31</p>";$valid = false;}
$byr = mysqli_real_escape_string($conn, trim($_POST['byr']));
if (empty($byr)) {echo "<p class='error'>Please enter a birth year</p>";$valid = false;}
if ($byr<1900 or $byr>2015) {echo "<p class='error'>Birth year must be between 1900 and the present</p>";$valid = false;}
$hmo = mysqli_real_escape_string($conn, trim($_POST['hmo']));
if (empty($hmo)) {echo "<p class='error'>Please enter a hire month</p>";$valid = false;}
if ($hmo<1 or $hmo>12) {echo "<p class='error'>Hire month must be between 1 and 12</p>";$valid = false;}
$hday = mysqli_real_escape_string($conn, trim($_POST['hday']));
if (empty($hday)) {echo "<class='error'>Please enter a hire day</p>";$valid = false;}
if ($hday<1 or $hday>31) {echo "<p class='error'>Hire day must be between 1 and 31</p>";$valid = false;}
$hyr = mysqli_real_escape_string($conn, trim($_POST['hyr']));
if (empty($hyr)) {echo "<p class='error'>Please enter a hire year</p>";$valid = false;}
if ($hyr<1900 or $hyr>2015) {echo "<p class='error'>Hire year must be between 1900 and the present</p>";$valid = false;}
$address = mysqli_real_escape_string($conn, trim($_POST['address']));
if (empty($address)) {echo "<p class='error'>Please enter a password</p>";$valid = false;}
$city = mysqli_real_escape_string($conn, trim($_POST['city']));
if (empty($city)) {echo "<p class='error'>Please enter a city</p>";$valid = false;}
$state = mysqli_real_escape_string($conn, trim($_POST['state']));
if (empty($state)) {echo "<p class='error'>Please enter a state</p>";$valid = false;}
$country = mysqli_real_escape_string($conn, trim($_POST['country']));
if (empty($country)) {echo "<p class='error'>Please enter a country</p>";$valid = false;}
$zip =mysqli_real_escape_string($conn,trim($_POST['zipcode']));
if (!is_numeric($zip)) {echo "<p class='error'>Postal code must be numeric</p>";$valid = false;}
if (!preg_match('/\d{5}(-\d{4})?/',$zip)) {echo "<p class='error'>Invalid postal code.</p>";}
$phone = mysqli_real_escape_string($conn, trim($_POST['phone']));
if (empty($phone)) {echo "<p class='error'>Please enter your phone</p>";$valid = false;}
if (!preg_match('/\D*([2-9]\d{2})(\D*)([2-9]\d{2})(\D*)(\d{4})\D*/',$phone)) {echo "<p class='error'>Invalid phone</p>";}
$fax = mysqli_real_escape_string($conn, trim($_POST['fax']));
if (empty($fax)) {echo "<p class='error'>Please enter your fax</p>";$valid = false;}
if (!preg_match('/\D*([2-9]\d{2})(\D*)([2-9]\d{2})(\D*)(\d{4})\D*/',$fax)) {echo "<p class='error'>Invalid fax</p>";}
$email = mysqli_real_escape_string($conn, trim($_POST['email']));
if (empty($email)) {echo "<p class='error'>Please enter your email</p>";$valid = false;}
if (!preg_match('/[-\w.]+@([A-z0-9][-A-z0-9]+\.)+[A-z]{2,4}/',$email)) {echo "<p class='error'>Invalid email address</p>";}
/////////////////////////////////////DONE GATHERING DATA FROM FORM////////////////////////////////////////
// if all the data collected is valid, update/OVERWRITE EXISTING DATA in the database w/query
//and transfer HEADER DATA to the display page labcomp3-3.php
//and exit this page and go back to labcomp3.3!!!!!!
if ($valid){
$birthdate = "$byr-$bmo-$bday 00:00:00";
$hiredate = "$hyr-$hmo-$hday 00:00:00";
$query = "update Employee set LastName='$lastname', FirstName='$firstname', Title='$title', ReportsTo=$reportsto, BirthDate='$birthdate', HireDate='$hiredate', Address='$address',City='$city',State='$state',Country='$country',PostalCode='$zip',Phone='$phone',Fax='$fax',Email='$email' where EmployeeId=$eid";
//no quotes for $reports to or $eid???
mysqli_query($conn, $query) or die(mysqli_error($conn));
//if change was successful
if (mysqli_affected_rows($conn)>0) {
//SEND BACK TO 3-3!!!
header("Location: 3.3.php?actionheadervarupdatefrom3-6=updated&idheadervarupdatefrom3-6=$eid");
exit();
}
else{
//if NOTHING has changed
echo "<p class='error'>Unable to update record, no data was modified</p>";
}
}
}//end if isset post confirm
else
{
//if CONFIRM BUTTON NOT CLICKED
//then no data sent from 3-3 update button,
if (!isset($_GET['idlinkvarfrom3-3'])){
//AKA you used URL to get directly to this page
//ABNORMAL
echo "<div id='chapterMain'><p class='error'>No Employee ID provided !!!!! <a href='3.3.php'>Return to display page.</a></div>";
}
//AKA you were just sent here from 3.3 update button...
//you have not clicked CONFIRM update at the bottom of the 3-6 form yet
//NORMAL
//pull database info into $eid, based on idLinkVar info sent from 3-3 button
//info is used to populate the form fields on 3-6u(this page) with the data
//from the row from which we clicked the update button back on 3-3
//THIS WILL RUN FIRST.... BEFORE the above PHP... BECAUSE the CONFIRM BUTTON WILL NOT HAVE BEEN CLICKED YET
//we need to run this first to populate the fields w/ the existing data
//but it CANNOT BE CODED FIRST because idLinkVarFrom3-3 will be undefined......
//(not sure why 3-23-19Sat2007---resolved2041)
//after clicking CONFIRM on the form below... we are RE RUNNING 3-6u...
//in which case we are not coming from 3-3 anymore..
//so we arent sending idLinkVar from 3.3 anymore on the second run
//FIRST RUN OF 3.6 upon clicking update on 3.3, starts here
//SECOND RUN OF 3.6 upon clicking confirm on 3.6, starts at top
//INCLUDE this code in the bracket for if(!isset($_GET['idLinkVarFrom3-3'])) becauase this code will not be
//needed on the SECOND RUN
//SECOND RUN= no data passed from idLinkVarFrom3-3
$eid=$_GET['idlinkvarfrom3-3'];
$query = "Select * from Employee where EmployeeId = $eid";
$result = mysqli_query($conn,$query);
if (!$result) {
die(mysqli_error($conn));
}
// check for results
if (mysqli_num_rows($result)> 0) {
// retrieve result row
$row = mysqli_fetch_assoc($result);
$firstname=$row['FirstName'];
$lastname=$row['LastName'];
$email=$row['Email'];
$zip=$row['PostalCode'];
$title=$row['Title'];
$reportsto=$row['ReportsTo'];//pulls existing NUMBER from 'reportsto' field
$birthdate=$row['BirthDate'];
$hiredate=$row['HireDate'];
$bmo=substr($birthdate,5,2);
$bday=substr($birthdate,8,2);
$byr=substr($birthdate,0,4);
$hmo=substr($hiredate,5,2);
$hday=substr($hiredate,8,2);
$hyr=substr($hiredate,0,4);
$address=$row['Address'];
$city=$row['City'];
$state=$row['State'];
$country=$row['Country'];
$phone=$row['Phone'];
$fax=$row['Fax'];
}
else{
echo "<p class='error'>Unable to retrieve employee $eid. <a href='labComp3-3.php'>Return to display page.</a>";
}
}
?>
<form method="post" action="3.6-u.php">
<input type="hidden" name="eid" value="<?php echo $eid; ?>">
<p>
<label for="firsname">First name</label>
<input type="text" name="firstname" id="firstname" value="<?php echo $firstname; ?>">
<label for="lastname">Last name</label>
<input type="text" name="lastname" id="lastname" value="<?php echo $lastname; ?>">
</p>
<p>
<label for="title">Title:</label>
<input type="text" name="title" id="title" value="<?php echo $title; ?>">
</p>
<!---dropdown list----------------------------------
------------>
<p>
<label for="reportsto">Reports to:</label>
<select name="reportsto" id="reportsto">
<?php //create query for dropdown..
$query = "Select EmployeeId, FirstName, LastName from Employee";//get ALL id, firstname, lastname
$result = mysqli_query($conn,$query);
if (!$result) {
die(mysqli_error($conn));
}
if (mysqli_num_rows($result)> 0)
{ //if anything pulled from query..........
while ($row = mysqli_fetch_assoc($result))
{
echo "<option value='".$row['EmployeeId']."'";// print EACH employee id as dropdown VALUE to fill the options with all listed employees
//$reportsto represents existing NUMBER from 'reportsto' field, gathered at line 175 from the specified line to update
// it is the existing NUMBER for MY database row and MY 'reportsto' since I clicked update for MY name on 3.3
if ($reportsto==$row['EmployeeId'])
// if MY reportsto# matches someone's employee ID on the employee list as we work down the list, use that person
{
echo " selected ";
}
// that person will be the one who is SELECTED aka shown as preselected on the dropdown before it is clicked
//NOTE if we dont do this, the first item will be shown by default (that is how we did it in 3-5)
//NOTE all the others will be shown in the default order per database
echo ">".$row['FirstName']." ".$row['LastName']."</option>";
//NOTE we are not printing the reportsTo# itself in our dropdown... just the corresponding first/last name
}
}
//}//no opening bracket to go with this..., per demo**********************************************
//omitted bc causing Parse error: syntax error, unexpected '}', expecting end of file in C:\Apache24\htdocs\Banks\labcomp3-6u.php on line 237
?>
</select>
</p>
<p>
Birth Date:
<input type="text" name="bmo" id="bmo" value="<?php echo $bmo; ?>" size="2">
<input type="text" name="bday" id="bday" value="<?php echo $bday; ?>" size="2">
<input type="text" name="byr" id="byr" value="<?php echo $byr; ?>" size="4">
</p>
<p>
Hire Date:
<input type="text" name="hmo" id="hmo" value="<?php echo $hmo; ?>" size="2">
<input type="text" name="hday" id="hday" value="<?php echo $hday; ?>" size="2">
<input type="text" name="hyr" id="hyr" value="<?php echo $hyr; ?>" size="4">
</p>
<label for="address">Address</label>
<input type="text" name="address" id="address" value="<?php echo $address; ?>">
</p>
<label for="city">city</label>
<input type="text" name="city" id="city" value="<?php echo $city; ?>">
</p>
<label for="state">State</label>
<input type="text" name="state" id="state" value="<?php echo $state; ?>" size="2">
</p>
<label for="country">Country</label>
<input type="text" name="country" id="country" value="<?php echo $country; ?>">
</p>
<p>
<label for="zip">Postal Code</label>
<input type="text" name="zipcode" id="zip" value="<?php echo $zip; ?>">
</p>
<label for="phone">Phone</label>
<input type="text" name="phone" id="phone" value="<?php echo $phone; ?>">
</p>
<label for="fax">Fax</label>
<input type="text" name="fax" id="fax" value="<?php echo $fax; ?>">
</p>
<p>
<label for="email">Email address:</label>
<input type="email" name="email" id="email" value="<?php echo $email; ?>">
</p>
<p>
<input type="submit" name="confirm" value=" Confirm Update Employee">
</p>
</form>
<?php
printFootWithCodeExample('3.6-ucode.php');
?>