0

I am working on a leave management system whereby when an administrator creates an account, annual leave days are added by default in the staff table. When a staff member applies for leave days to take and has the leave request approved, I want to subtract the taken days in the leave table from the annual leave days assigned in the staff table, after the subtraction I want to update the staff table annual leave days. I am stuck on how I can join the table, calculate from two different tables' values, and finally update the staff table leave days. Below is my code:

Staff table

staff_id
firstname
lastname
username
dob
doa
password
leave_days

Leave

leaveid
staffid
date_issued
leave_days_taken
status

approve.php

<?php
session_start();

//Getting leaveid from /leave page
$leaveid = $_GET['leaveid'];

//DB Connection
$conn = mysqli_connect('localhost', 'root', '','leavemanagement');
//If approve button was clicked

//Update the status column
$sql = "UPDATE `leave` SET status = 'Approved' WHERE leaveid = '$leaveid'";
$query = mysqli_query($conn, $sql);

if($query == true){?>
    <script>
        alert("Leave request has been approved");
        window.location.href = 'leave.php';
    </script>
<?php
} else{ ?>
    <script>
        alert("Leave request has not been approved");
        window.location.href = 'leave.php';
    </script>
  <?php  } ?>
ADyson
  • 57,178
  • 14
  • 51
  • 63
Arthur Sven Msiska
  • 1
  • 2
    **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. – ADyson Sep 01 '22 at 09:47
  • 2
    https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. – ADyson Sep 01 '22 at 09:47
  • 2
    Never configure your web app to login to the database as `root`. Root can do whatever it likes, so on top of the SQL injection vulnerabilities this just leaves your database an open book for hackers. Instead create a separate user account specifically for this application which has only the permissions it actually _needs_ in order to work properly. Don't even use the root account as a shortcut during development or testing, because you need to test your account permissions as well - otherwise when you go live you might have unexpected errors relating to the user account setup. – ADyson Sep 01 '22 at 09:48
  • 1
    `I want to subtract the taken days in the leave table from the annual leave days assigned in the staff table`...ok, please show us some sample data and the expected result. This doesn't have much, if anything, to do with the PHP code you've shown us. Please read [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) – ADyson Sep 01 '22 at 09:52
  • `I want to update the staff table annual leave days.`...it would be better to keep the total number of days as a permanent record, and then have a separate field which stores the current remaining days for the current year. – ADyson Sep 01 '22 at 09:54
  • Hmm, looks familiar, do you now have more than one account? – RiggsFolly Sep 01 '22 at 09:55
  • @RiggsFolly This is my first time asking this question, as for my account, I briefly had one in 2017. I lost track of it – Arthur Sven Msiska Sep 01 '22 at 10:44

0 Answers0