I would appreciate some help please. For some reason my update statement below is not updating the student name when I use the where clause with the student_id as one of the conditions. When I use the trainer_id it update all of the students under that trainer, but I only want to the statement to update that specific student detail such as the surname.
An extract from the code is below:
<?php
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (isset($_POST['submit'])) {
// Grab the profile data from the POST
$first_name = mysqli_real_escape_string($dbc, trim($_POST['firstname']));
$last_name = mysqli_real_escape_string($dbc, trim($_POST['lastname']));
$error = false;
// Update the profile data in the database
if (!$error) {
if (!isset($_GET['student_id'])){
$query = "UPDATE student INNER JOIN trainer ON trainer.trainer_id = student.trainer_id SET student.first_name = '$first_name', student.last_name = '$last_name'".
"WHERE student.trainer_id ='" . $_SESSION['trainer_id'] . "' AND student.student_id = '" . $_GET['student_id'] . "'";
mysqli_query($dbc, $query);
// Confirm success with the user
echo '<p>Your profile has been successfully updated. Would you like to <a href="viewprofile.php">view your profile</a>?</p>';
mysqli_close($dbc);
exit();
}
else {
echo '<p class="error">You must enter all of the profile data (the picture is optional).</p>';
}
}
}// End of check for form submission
else {
// Grab the profile data from the database
$query = "SELECT first_name, last_name FROM student WHERE trainer_id = '" . $_SESSION['trainer_id'] . "' AND student_id = '" . $_GET['student_id'] . "'";
$data = mysqli_query($dbc, $query);
$row = mysqli_fetch_array($data);
if ($row != NULL) {
$first_name = $row['first_name'];
$last_name = $row['last_name'];
}
else {
echo '<p class="error">There was a problem accessing your profile.</p>';
}
}
mysqli_close($dbc);
?>
<form enctype="multipart/form-data" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<fieldset>
<legend>Personal Information</legend>
<label for="firstname">First name:</label>
<input type="text" id="firstname" name="firstname" value="<?php if (!empty($first_name)) echo $first_name; ?>" /><br />
<label for="lastname">Last name:</label>
<input type="text" id="lastname" name="lastname" value="<?php if (!empty($last_name)) echo $last_name; ?>" /><br />
</fieldset>
<input type="submit" value="Save Profile" name="submit" />
</form>
