Simple Leave Management System With PHP MySQL (Free Download)

Welcome to a tutorial on how to create a simple leave management system with PHP and MySQL. Yep, there are already tons of such “leave management systems” all over the Internet. So here’s one that is slightly different, a barebones system with no third-party frameworks. Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • This tutorial is meant for people who want to learn or build on an existing system – If you just want a “complete system out of the box”, check out Cadre instead.
  • Create a database and import 1-database.sql.
  • Change the database settings in 2-lib-leave.php to your own.
  • Run x-dummy.php to create dummy data entries.
  • Access 4a-apply.php in the browser.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

SCREENSHOT

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

PHP MYSQL LEAVE MANAGEMENT

All right, let us now get into more details on how the leave management system works.

 

PART 1) THE DATABASE

1A) HOLIDAYS

1-database.sql
-- (A) HOLIDAYS
CREATE TABLE `holidays` (
  `holiday_id` bigint(20) NOT NULL,
  `holiday_name` varchar(255) NOT NULL,
  `holiday_date` date NOT NULL,
  `holiday_half` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `holidays`
  ADD PRIMARY KEY (`holiday_id`),
  ADD KEY `holiday_date` (`holiday_date`);
 
ALTER TABLE `holidays`
  MODIFY `holiday_id` bigint(20) NOT NULL AUTO_INCREMENT;

Captain Obvious to the rescue, this table is used to store the holidays.

  • holiday_id Primary key and auto-increment.
  • holiday_name Name of the holiday. E.g. Christmas, New Year, etc…
  • holiday_date The date.
  • holiday_half Define 1 if it is a “half-day holiday”. For example, New Year’s Eve is a half-day holiday.

P.S. If you want to be “accurate”, you can change holiday_half to “F” for a full day, “AM” or “PM”.

 

1B) ENTITLED LEAVE

1-database.sql
-- (B) LEAVE ENTITLED
CREATE TABLE `leave_entitled` (
  `user_id` bigint(20) NOT NULL,
  `leave_type` varchar(1) NOT NULL,
  `leave_days` decimal(4,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `leave_entitled`
  ADD PRIMARY KEY (`user_id`,`leave_type`);

The “number of days leave” each user/staff has.

  • user_id Foreign key and partial primary key.
  • leave_type Partial primary key. An open character field for you to call whatever types of leave the company has. For example, “P”aid, “U”npaid, “M”edical.
  • leave_days The number of days entitled for this type of leave.

 

 

1C) LEAVE TAKEN

1-database.sql
-- (C) LEAVE TAKEN
CREATE TABLE `leave_taken` (
  `leave_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `leave_type` varchar(255) NOT NULL,
  `leave_status` varchar(1) NOT NULL DEFAULT 'P',
  `leave_from` datetime NOT NULL,
  `leave_to` datetime NOT NULL,
  `leave_days` decimal(4,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 
 
ALTER TABLE `leave_taken`
  ADD PRIMARY KEY (`leave_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `leave_type` (`leave_type`),
  ADD KEY `leave_status` (`leave_status`),
  ADD KEY `leave_from` (`leave_from`),
  ADD KEY `leave_to` (`leave_to`);
 
ALTER TABLE `leave_taken`
  MODIFY `leave_id` bigint(20) NOT NULL AUTO_INCREMENT;

This table records the leave applied by the users/staff.

  • leave_id Primary key and auto-increment.
  • user_id Foreign key.
  • leave_type Foreign key.
  • leave_status Status for this leave entry. For example – “P”ending, “A”pproved, “D”eclined.
  • leave_from Date starting from.
  • leave_to Date ending.

 

1D) LEAVE TAKEN (DAYS)

1-database.sql
-- (D) LEAVE TAKEN INDIVIDUAL DAYS
CREATE TABLE `leave_taken_days` (
  `leave_id` bigint(20) NOT NULL,
  `leave_day` date NOT NULL,
  `leave_half` varchar(1) NOT NULL DEFAULT 'F'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `leave_taken_days`
  ADD PRIMARY KEY (`leave_id`,`leave_day`);

Some of you guys should be confused with this final table. Isn’t the leave period already captured above? Well, a quick example will explain things:

  • John takes leave from Thursday afternoon to next Monday afternoon.
  • That is – Thursday PM, Friday full day, Monday AM.

The above “start and end dates” cannot capture half days, thus the need for this table.

  • leave_id Partial primary key, foreign key.
  • leave_day Partial primary key.
  • leave_half
    • F Full day leave.
    • A Morning leave.
    • P Afternoon leave.

 

PART 2) PHP LIBRARY

2A) INITIALIZE

2-lib-leave.php
class Leave {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () { try {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  } catch (Exception $ex) { exit($ex->getMessage()); }}
 
  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }
 
  // (C) HELPER FUNCTION - EXECUTE SQL QUERY
  function query ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
  // ...
}
 
// (H) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (I) DEFINITIONS
define("LEAVE_MIN", date("Y-m-d"));
define("LEAVE_TYPES", [
  "P" => "Paid",
  "U" => "Unpaid",
  "M" => "Medical"
]);
define("LEAVE_STATUS", [
  "P" => "Pending",
  "A" => "Approved",
  "D" => "Denied"
]);
 
// (J) NEW LEAVE OBJECT
$_LEAVE = new Leave();

Now that we have established the database, we will build a PHP library to work with it. The top and bottom sections should be pretty self-explanatory.

  • (A, B, J) When $_LEAVE = new Leave() is created, the constructor automatically connects to the database. The destructor closes the database connection.
  • (C) query() is a helper function to run SQL queries.
  • (H) Remember to change the database settings to your own.
  • (I) As above, these are the “status code definitions”.

 

 

2B) HOLIDAYS

2-lib-leave.php
// (D) SET HOLIDAY
function holiday ($name, $date, $half=0, $id=null) {
  if ($id==null) {
    $sql = "INSERT INTO `holidays` (`holiday_name`, `holiday_date`, `holiday_half`) VALUES (?, ?, ?)";
    $data = [$name, $date, $half];
  } else {
    $sql = "UPDATE `holidays` SET `holiday_name`=?, `holiday_date`=?, `holiday_half`=? WHERE `holiday_id`=?";
    $data = [$name, $date, $half, $id];
  }
  $this->query($sql, $data);
  return true;
}

Next, we have a very obvious function… Use this to set the holidays.

 

2C) SET ENTITLED LEAVE

2-lib-leave.php
// (E) SET ENTITLED LEAVE
function entitle ($id, $type, $total, $taken, $left, $year=null) {
  $this->query(
    "REPLACE INTO `leave_entitled` (`user_id`, `leave_type`, `leave_days`) VALUES (?, ?, ?)",
    [$id, $type, $days]
  );
  return true;
}

Not much of a mystery for this one either. A function to set the leave entitled to a staff member.

 

2D) CHECK THE GIVEN DATE PERIOD

2-lib-leave.php
// (F) CHECK GIVEN PERIOD
function check ($from, $to) {
  // (F1) CHECK START & END DATES
  $start = strtotime($from);
  $end = strtotime($to);
  if ($start<strtotime(LEAVE_MIN)) { return ["error"=>"Earliest leave is ".LEAVE_MIN]; }
  if ($end < $start) { return ["error"=>"End date is earlier than start date?"]; } 
 
  // (F2) DATE RANGE
  $days = [];
  for ($unix=$start; $unix<=$end; $unix+=86400) {
    $weekend = date("N", $unix) > 5;
    $days[date("Y-m-d", $unix)] = [
      "a" => $weekend ? 0 : 1,
      "n" => $weekend ? "Weekend" : ""
    ];
  }
 
  // (F3) HOLIDAYS
  $this->query(
    "SELECT * FROM `holidays` WHERE `holiday_date` BETWEEN ? AND ?",
    [$from, $to]
  );
  while ($r = $this->stmt->fetch()) {
    $days[$r["holiday_date"]] = [
      "a" => $r["holiday_half"]==1 ? 2 : 0,
      "n" => $r["holiday_name"]
    ];
  }
 
  // (F4) GET LEAVE ALREADY TAKEN WITHIN PERIOD
  $this->query(
    "SELECT d.* FROM `leave_taken_days` d
     LEFT JOIN `leave_taken` t 
     ON (d.`leave_id`=t.`leave_id` AND t.`user_id`=?)
     WHERE d.`leave_day` BETWEEN ? AND ? AND t.`leave_status`!='D'",
    [$id, $from, $to]
  );
  while ($r = $this->stmt->fetch()) {
    $days[$r["leave_day"]] = [
      "a" => 0,
      "n" => "Already taken leave on this day"
    ];
  }
 
  // (F5) RESULTS
  // $days[YYYY-MM-DD] = ["a"=>CODE BELOW, "n"=>NOTES];
  // 2 : Can apply half/none
  // 1 : Can apply full/half/none
  // 0 : No need to apply leave
  return $days;
}

This function is a little on the heavy side. Let’s start with something simple, there are 2 steps in the leave application process.

  1. Select the type of leave and a date period.
  2. Then, select full/AM/PM/none for the individual days.

Yes, check() is something like a “helper function” for the second step. It checks the date period ($from and $to) selected in the first step, and determines if leave is applicable to every individual day.

P.S. Complete this function on your own. The rules are subjective to the company and region. For example, long leave of N days must be applied M weeks in advance.

 

2E) APPLY FOR LEAVE

2-lib-leave.php
// (G) APPLY LEAVE
function apply ($id, $type, $days) {
  // (G1) APPLIED LEAVE DAYS
  $days = json_decode($days, true);
  $from = array_key_first($days);
  $to = array_key_last($days);
  $year = substr($from, 0, 4);
  $total = 0;
  foreach ($days as $day=>$half) { $total += $half=="F" ? 1 : 0.5 ; }
 
  // (G2) GET ENTITLED LEAVE
  $this->query(
    "SELECT `leave_days` FROM `leave_entitled` WHERE `user_id`=? AND `leave_type`=?",
    [$id, $type]
  );
  $entitled = $this->stmt->fetchColumn();
  if (!is_numeric($entitled)) {
    $this->error = "Insufficient leave days.";
    return false;
  }
 
  // (G3) CALCULATE REMAINING LEAVE
  $this->query(
    "SELECT SUM(`leave_days`) FROM `leave_taken` 
     WHERE `user_id`=? AND `leave_type`=? 
     AND `leave_status`!='D' AND `leave_from` BETWEEN ? AND ?",
    [$id, $type, "$year-01-01", "$year-12-31"]
  );
  $taken = $this->stmt->fetchColumn();
  if (is_numeric($taken)) { $entitled -= $taken; }
  if ($total > $entitled) {
    $this->error = "Insufficient leave days.";
    return false;
  }
 
  // (G4) AUTO-COMMIT OFF
  $this->pdo->beginTransaction();
 
  // (G5) CREATE "MAIN LEAVE ENTRY"
  $this->query(
    "INSERT INTO `leave_taken`
    (`user_id`, `leave_type`, `leave_from`, `leave_to`, `leave_days`)
    VALUES (?, ?, ?, ?, ?)",
    [$id, $type, $from, $to, $total]
  );
  $id = $this->pdo->lastInsertId();
 
  // (G6) LEAVE DAYS
  $sql = "INSERT INTO `leave_taken_days` (`leave_id`, `leave_day`, `leave_half`) VALUES ";
  $data = [];
  foreach ($days as $d=>$h) {
    $sql .= "(?,?,?),";
    $data[] = $id; $data[] = $d; $data[] = $h;
  }
  $this->query(substr($sql,0,-1).";", $data);
 
  // (G7) DONE
  $this->pdo->commit();
  return true;
}

Lastly, this function completes the “checkout process” and saves the actual leave application. Once again, you may want to add your own touches – Double check the submitted dates, send an email after saving, and complete your own approval process.

 

 

2F) DUMMY DATA

x-dummy.php
<?php
// (A) LOAD LIBRARY
require "2-lib-leave.php";
 
// (B) ADD HOLIDAYS
echo $_LEAVE->holiday("Test Holiday Eve", date("Y-m-d", strtotime("+1 day")), 1) ? "OK" : $_LEAVE->error ;
echo $_LEAVE->holiday("Test Holiday", date("Y-m-d", strtotime("+2 days"))) ? "OK" : $_LEAVE->error ;
 
// (C) ENTITLE LEAVE
echo $_LEAVE->entitle(999, "M", 10) ? "OK" : $_LEAVE->error ;
echo $_LEAVE->entitle(999, "P", 11) ? "OK" : $_LEAVE->error ;
echo $_LEAVE->entitle(999, "U", 12) ? "OK" : $_LEAVE->error ;

We are done with the library. This is a small bit to demonstrate how the library works and to add simple example entries.

 

PART 3) AJAX HANDLER

3-ajax-leave.php
<?php
// (A) LOAD LIBRARY + FIXED USER ID
require "2-lib-leave.php";
$user = 999;
 
if (isset($_POST["req"])) { switch ($_POST["req"]) {
  // (B) INVALID REQUEST
  default: echo "Invalid request"; break;

  // (C) CHECK SELECTED PERIOD
  case "check":
    echo json_encode($_LEAVE->check($user, $_POST["from"], $_POST["to"]));
    break;

  // (D) APPLY FOR LEAVE
  case "apply";
    echo $_LEAVE->apply($user, $_POST["type"], $_POST["days"])
      ? "OK" : $_LEAVE->error ;
    break;
}}

The library will not do anything by itself. So here’s a simple “API endpoint”. Very easy to use. Just send $_POST["req"] along with the required parameters. For example – To check a given period, send $_POST["req"] = "check", $_POST["from"] = "YYYY-MM-DD", and $_POST["to"] = "YYYY-MM-DD".

P.S. The user ID $user is fixed in this demo, tie this into your own user system. If you don’t have a login system, check out the links below.

 

PART 4) APPLY LEAVE PAGE

4A) THE HTML

4a-apply.php
<?php
// (A) LOAD PHP LIBRARY
require "2-lib-leave.php";

// (B) HTML ?>
<!-- (B1) SELECT LEAVE TYPE + PERIOD -->
<form id="form_period" onsubmit="return apply.check()">
  <label>Leave Type</label>
  <select id="leave_type"><?php foreach (LEAVE_TYPES as $c=>$t) {
    echo "<option value='$c'>$t</option>";
  } ?></select>
  <label>Start Date</label>
  <input type="date" id="leave_from" min="<?=LEAVE_MIN?>" value="<?=LEAVE_MIN?>" required>
  <label>End Date</label>
  <input type="date" id="leave_to" min="<?=LEAVE_MIN?>" value="<?=date("Y-m-d", strtotime(LEAVE_MIN)+604800)?>" required>
  <input type="submit" value="Next">
</form>
 
<!-- (B2) SELECT INDIVIDUAL DAYS -->
<form id="form_days" class="hide" onsubmit="return apply.go()">
  <div id="form_days_list"></div>
  <div id="form_days_total">
    <div class="rowDate">Total Days: </div>
    <div id="form_days_count"></div>
  </div>
  <input type="submit" value="Apply">
  <input type="button" value="Cancel" onclick="apply.toggle(false)">
</form>

So far so good? The system is actually fully functional with the API and library. This final step is nothing but a demo HTML page on what an “apply leave” page could look like.

  1. Load the PHP library.
  2. As above, there are 2 steps to the leave application.
    • (B1) First step, select the leave type and period.
    • (B2) Second step, select full/AM/PM for each individual day.

 

 

4B) THE JAVASCRIPT

4b-apply.js
var apply = {
  // (A) HTML INTERFACE - TOGGLE PERIOD/SELECT DAY
  toggle : (direction) => {
    if (direction) {
      document.getElementById("form_period").classList.add("hide");
      document.getElementById("form_days").classList.remove("hide");
    } else {
      document.getElementById("form_days").classList.add("hide");
      document.getElementById("form_period").classList.remove("hide");
    }
  },

  // (B) CHECK SELECTED PERIOD
  check : () => {
    // (B1) FROM & TO DATES
    let from = document.getElementById("leave_from").value,
        to = document.getElementById("leave_to").value;
    if (new Date(from) > new Date(to)) {
      alert("Start date cannot be later than end date");
    }

    // (B2) FORM DATA
    let data = new FormData();
    data.append("req", "check");
    data.append("type", document.getElementById("leave_type").value);
    data.append("from", from);
    data.append("to", to);
 
    // (B3) AJAX CHECK SELECTED PERIOD
    fetch ("3-ajax-leave.php", { method: "post", body: data })
    .then(res => res.json())
    .then(days => {
      // (B3-1) ERROR!
      if (days.error) {
        alert(days.error);
        return false;
      }

      // (B3-2) RESET "SELECT HALF/FULL DAY" FORM
      let row, wrap = document.getElementById("form_days");
      wrap.innerHTML = "";
 
      // (B3-3) DRAW "SELECT HALF/FULL DAY" FORM
      for (let [d,s] of Object.entries(days)) {
        row = document.createElement("div");
        row.className = "row";
        row.innerHTML = 
        `<div class="rowDate">
          <div>${d}</div>
          <small>${s.n ? s.n : ""}</small>
        </div>
        <select class="rowHalf"></select>`;
        wrap.appendChild(row);

        row = row.getElementsByTagName("select")[0];
        row.onchange = apply.total;
        row.dataset.date = d;
        if (s["a"]==0) {
          row.innerHTML = `<option value="">NA</option>`;
          row.disabled = true;
        } else if (s["a"]==1) {
          row.innerHTML = `<option value="F">Full Day</option>
          <option value="P">PM</option>
          <option value="A">AM</option>
          <option value="">No Leave</option>`;
        } else {
          row.innerHTML = `<option value="P">PM</option>
          <option value="A">AM</option>
          <option value="">No Leave</option>`;
        }
      }
 
      // (B3-4) TOGGLE HTML FORMS
      apply.total();
      apply.toggle(true);
    });
    return false;
  },

  // (C) CALCULATE TOTAL DAYS
  total : () => {
    let days = 0;
    for (let d of document.querySelectorAll("#form_days_list select")) {
      if (d.value=="") { continue; }
      else if (d.value=="F") { days++; }
      else { days += 0.5; }
    }
    document.getElementById("form_days_count").innerHTML = days;
  },
 
  // (D) APPLY LEAVE
  go : () => {
    // (D1) GET DAYS
    let days = {};
    for (let d of document.querySelectorAll("#form_days_list select")) {
      if (d.value=="") { continue; }
      days[d.dataset.date] = d.value;
    }
    if (Object.keys(days).length==0) {
      alert("No dates selected!");
      return false;
    }

    // (D2) FORM DATA
    let data = new FormData();
    data.append("req", "apply");
    data.append("type", document.getElementById("leave_type").value);
    data.append("days", JSON.stringify(days));

    // (D3) AJAX SUBMIT
    fetch ("3-ajax-leave.php", { method: "post", body: data })
    .then(res => res.text())
    .then(res => {
      alert(res);
      console.log("@TODO - WHATEVER YOU WANT TO DO NEXT");
      // APPROVAL PROCESS
      // EMAIL CONFIRMATION
      // REDIRECT TO SOMEWHERE ELSE
    });
    return false;
  }
};

Not going to explain line-by-line and bore you to tears. Basically, this Javascript drives the HTML interface.

  1. toggle() Toggle between the first and second steps.
  2. check() Sends the selected period to the PHP AJAX handler. Draws the full/AM/PM selectors for the individual days.
  3. total() Calculate the total number of leave days.
  4. go() Apply for the actual leave itself.

Well, this is a dummy demo after all. Feel free to rebuild the entire HTML page with your own preferred library and framework.

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

IMPROVEMENTS NEEDED

My developer senses are tingling, trolls are going to scream “this is an incomplete system”! Of course, this is a tutorial only. There are a lot of things left to do.

  • User login, role management, admin panel – See links below.
  • The system currently works for one location only. If you are supporting multiple countries, a lot of timezone and region stuff needs to be factored in.
  • You may want to create a separate “leave history” table to record all the changes to the leave records.
  • Secure the API and library. Only administrators can access and use the functions.
  • Complete your own check() – Is Sat/Sun a working day? Any special rules? Cannot take more than N days leave in a row, must be N days in advance?
  • Complete your own apply() – Add a “pending approval” flag. Add your own role management, and maybe even department as required. You may also want to double-check the submitted days.
  • How are leave days calculated if the leave crosses the year? E.G. Taking leave from 30 Dec 2076 to 5 Jan 2077.
    • Leave days will be deducted from 2076?
    • Leave days will be deducted from 2077?
    • Or separate 2076 and 2077 accordingly?
  • Add your own library functions as required – Delete holidays, cancel leave, etc…
  • Do your own leave reports. Excel, Word, PDF, whatever is required.

Everyone has a different starting point and requirements. It is nearly impossible to create a “one size fits all” system, and I cannot provide free consultations for everyone – You have to complete your own system.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *