2

Need your help with my Code. The page is for approving the "leave application" For example, the employee applied for "Sick Leave" and It is approved it will deduct 1 from the sl_credit which is from the user_info table on my database and will also update the remarks, remarkable, and status to the tblleaves table. I am getting an error message "error updating table"

On my code, I just tried the "Approved" to test if it is working.

I hope someone could help me with this. I'm very new to programming and would really appreciate your help.

Thanks in advance. 

    <?php

$uid = $_GET['ref'];

$leavetype = $_GET['leavetype'];
$adminremark = $_POST['adminremark'];
$status = $_POST['status'];
$adminremarkdate = $_POST['adminremarkdate'];
$date = date("Y-m-d H:i:s");


include '../db_config/connection.php';

if ($leavetype == "Sick Leave" || $status == "Approved") {

$sql = "UPDATE tblleaves, user_info SET adminremark='$adminremark', status='$status', adminremarkdate = '$date', user_info.sl_credit=user_info.sl_credit-1, WHERE id='$uid'";

}elseif ($leavetype == "Vacation Leave" || $status == "Approved") {
    $sql = "UPDATE tblleaves, user_info SET adminremark='$adminremark', status='$status', adminremarkdate = '$date', user_info.vl_credit=user_info.vl_credit-1, WHERE id='$uid'";
}


if ($conn->query($sql) === TRUE) {
    header("location:leave-details.php?ref=$uid");
} else {
    echo "Error updating record: " . $conn->error;
}
$conn->close();

?>
Gufran Hasan
  • 8,910
  • 7
  • 38
  • 51
jackflick
  • 53
  • 9

1 Answers1

0

You should include the whole error message, you're just telling us the error message that you coded in to your echo...

As for people saying you can't update two tables at once, normally that's true, but in MySQL you can: http://sqlfiddle.com/#!9/d85e9/1

The three problems I can see are...


You have an extra , just before your WHERE clause, remove it...

user_info.sl_credit=user_info.sl_credit-1, WHERE id='$uid'
                                         ^


The WHERE clause needs to reference both tables being updated...

WHERE user_info.id = '$uid' AND tblleaves.user_id = user_info.id

(Or similar)


Also, you have SET adminremark='$adminremark', but every column being updated should specify the table as well...

SET tblleaves.adminremark='$adminremark', etc, etc

OR

SET user_info.adminremark='$adminremark', etc, etc
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Hi MatBailie. Yes. That's the error message I set up. meaning to say that my code is not working. I will try that. – jackflick May 09 '18 at 11:18
  • @jackflick - And so, by telling us the error message ***you*** wrote, you've hidden the full error message from MySQL. It's nice to know that it's not working, and my answer shows two reasons why. But you should include the ***full and complete*** error message from MySQL. – MatBailie May 09 '18 at 11:19
  • Hi Mat. It did work. but it deducts 1 to every employee I have on the table. – jackflick May 09 '18 at 11:29
  • Here's the code. `$sql = "UPDATE tblleaves, user_info SET tblleaves.adminremark='$adminremark', tblleaves.status='$status', tblleaves.adminremarkdate = '$date', user_info.sl_credit = user_info.sl_credit - 1 WHERE id='$uid'";` – jackflick May 09 '18 at 11:29
  • @jackflick see my edited answer. Your where clause is insufficient – MatBailie May 09 '18 at 11:30
  • It worked Mat. now I need help in setting up the if it is "sick leave" or "vacation leave" it will deduct and only if the leave status is approve. – jackflick May 09 '18 at 11:39
  • That's a different question. You should isolate your problem and create a new question about that specific scenario. – MatBailie May 09 '18 at 11:43