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 Leave Manage Useful Bits & Links
The End

 

DOWNLOAD & NOTES

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

 

QUICK NOTES

  • Download and unzip into your HTTP folder.
  • Create a database and import 1-database.sql.
  • Change the database settings in 2-lib-leave.php to your own.
  • Run 2x-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.

 

 

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) USERS

1-database.sql
-- (A) USERS
CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_password` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`);
 
ALTER TABLE `users`
  MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
 
INSERT INTO `users` (`user_id`, `user_name`, `user_email`, `user_password`) VALUES
  (1, 'Jon Doe', 'jon@doe.com', 'ABCDE');

First, we shall address the elephant in the room. We need a user or staff table.

  • user_id Primary key and auto-increment.
  • user_name Full name.
  • user_email User email, unique to prevent duplicates.
  • user_password User’s password. Encrypt this in your own system.

That’s all. Feel free to add more fields as required, or use your existing staff table.

 

1B) HOLIDAYS

1-database.sql
-- (B) 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. You may want to further change holiday_half to “F” for a full day, “AM” or “PM”.

 

1C) ENTITLED LEAVE

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

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

  • user_id Foreign key and partial primary key.
  • leave_type Partial primary key. An open text field for you to call whatever types of leave the company has. For example, paid, unpaid, sick, hospitalization, maternity, etc…
  • leave_year Applicable to which year.
  • leave_total The total number of days for this type of leave.
  • leave_taken The number of days taken for this type of leave.
  • leave_left The number of days left for this type of leave.

 

 

1D) LEAVE TAKEN

1-database.sql
-- (D) 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_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_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_from Date starting from.
  • leave_to Date ending.

 

1D) LEAVE TAKEN (DAYS)

1-database.sql
-- (E) 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, 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
    • 0 Not on leave.
    • 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) 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.

  • When $_LEAVE = new Leave() is created, the constructor automatically connects to the database.
  • The destructor closes the database connection.
  • Remember to change the database settings to your own.

 

 

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 Captain 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) {
  if ($year==null) { $year = date("Y"); }
  $this->query(
    "REPLACE INTO `leave_entitled`
    (`user_id`, `leave_type`, `leave_year`, `leave_total`, `leave_taken`, `leave_left`)
    VALUES (?, ?, ?, ?, ?, ?)",
    [$id, $type, $year, $total, $taken, $left]
  );
  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) START & END DATES
  $start = strtotime($from);
  $end = strtotime($to);
  if ($end < $start) {
  $this->error = "End date cannot be earlier than start date";
    return false;
  }
 
  // (F2) GET HOLIDAYS
  $this->query(
    "SELECT * FROM `holidays` WHERE `holiday_date` BETWEEN ? AND ?",
    [$from, $to]
  );
  $holidays = [];
  while ($row = $this->stmt->fetch()) {
    $holidays[$row["holiday_date"]] = [
      "name" => $row["holiday_name"],
      "half" => $row["holiday_half"]
    ];
  }
 
  // (F3) GET LEAVE ALREADY TAKEN WITHIN PERIOD
  $this->query(
    "SELECT * FROM `leave_taken_days` WHERE `leave_day` BETWEEN ? AND ?",
    [$from, $to]
  );
  $taken = [];
  while ($row = $this->stmt->fetch()) {
    $taken[$row["leave_day"]] = $row["leave_half"];
  }
 
  // (F4) APPLICABLE DAYS TO APPLY WITHIN PERIOD
  $days = [];
  for ($unix=$start; $unix<=$end; $unix+=86400) {
    // (F4-1) CURRENT DATE & DAY
    $thisDate = date("Y-m-d", $unix);
    $thisDay = date("N", $unix);
 
    // (F4-2) CHECK DAY
    // 0 - need not apply
    // 1 - can apply for none/half/full day
    // 2 - can apply for none/half day
    $apply = 1; $note = "";
    if (isset($taken[$thisDate])) {
      $apply = 0;
      $note = "Already took leave";
    } else if (isset($holidays[$thisDate])) {
      $apply = $holidays[$thisDate]["half"]==0 ? 0 : 2 ;
      $note = $holidays[$thisDate]["name"];
    } else if ($thisDay>=6) {
      // @TODO - is sat/sun a working day?
      $apply = 0;
      $note = "Weekend";
    }
 
    // (F4-3) DAY APPLICABLE
    $days[$thisDate] = [
      "a" => $apply,
      "n" => $note
    ];
  }
 
  // (F5) RETURN RESULT
  return $days;
}

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

  • Select a type of leave, along with a date period.
  • Then, select full/AM/PM/none for the individual days.

Yes, check() is something like a “helper function” for the second step. It checks $from to $to, 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 X days must be taken Y weeks in advance.

 

 

2E) APPLY FOR LEAVE

2-lib-leave.php
// (G) APPLY LEAVE
function apply ($user, $type, $days) {
  // (G1) GET ENTITLED LEAVE
  $this->query(
    "SELECT * FROM `leave_entitled` WHERE `user_id`=? AND `leave_type`=?",
    [$user, $type]
  );
  $entitle = $this->stmt->fetch();
  if (!is_array($entitle)) {
    $this->error = "Not enough leave days to apply";
    return false;
  }
 
  // (G2) CALCULATE & CHECK TOTAL NUMBER OF DAYS
  $days = json_decode($days, true);
  $total = 0;
  foreach ($days as $day=>$half) {
    if ($half==0) { continue; }
    else if ($half=="F") { $total++; }
    else { $total+=0.5; }
  }
  if ($total > $entitle["leave_left"]) {
    $this->error = "Not enough leave days to apply";
    return false;
  }
 
  // (G3) AUTO-COMMIT OFF
  $this->pdo->beginTransaction();
 
  // (G4) CREATE "MAIN LEAVE ENTRY"
  $this->query(
    "INSERT INTO `leave_taken`
    (`user_id`, `leave_type`, `leave_from`, `leave_to`, `leave_days`)
    VALUES (?, ?, ?, ?, ?)",
    [$user, $type, key($days), array_key_last($days), $total]
  );
  $id = $this->pdo->lastInsertId();
 
  // (G5) UPDATE "LEAVE REMAINING"
  $this->query(
    "UPDATE `leave_entitled`
    SET `leave_taken`=?, `leave_left`=?
    WHERE `user_id`=? AND `leave_type`=? AND `leave_year`=?",
    [($entitle["leave_taken"] + $total), ($entitle["leave_left"] - $total),
    $user, $type, substr(key($days), 0, 4)]
  );
  unset($entitle); unset($total);
 
  // (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 email after saving, and complete your own approval process.

 

2F) DUMMY DATA

2x-dummy.php
<?php
// (A) LOAD LIBRARY
require "2-lib-leave.php";
 
// (B) ENTITLE LEAVE
echo $_LEAVE->entitle(1, "Paid", 15, 0, 15, date("Y"))
  ? "OK" : $_LEAVE->error;
 
// (C) ADD HOLIDAY
echo $_LEAVE->holiday("Test Holiday", date("Y-m-d"))
  ? "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
require "2-lib-leave.php";
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($_POST["start"], $_POST["end"]));
    break;

  // (D) APPLY FOR LEAVE
  case "apply";
    echo $_LEAVE->apply($_POST["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["start"]="YYYY-MM-DD", and $_POST["end"]="YYYY-MM-DD".

 

 

PART 4) HTML APPLY LEAVE

4A) THE HTML

4a-apply.php
<!-- (A) FIXED TO USER ID 1 FOR DEMO -->
<input type="hidden" id="user_id" value="1"/>

<!-- (B) SELECT PERIOD -->
<form id="form_period" onsubmit="return apply.check()">
  <label>Type</label>
  <select id="leave_type">
    <option value="Paid">Paid</option>
    <option value="Sick">Sick</option>
  </select>
  <label>Start Date</label>
  <input type="date" id="date_start" min="<?=date("Y-m-d")?>" required/>
  <label>End Date</label>
  <input type="date" id="date_end" min="<?=date("Y-m-d")?>" required/>
  <input type="submit" value="Next"/>
</form>
 
<!-- (C) SELECT HALF/FULL DAY -->
<form id="form_days" class="hide" onsubmit="return apply.go()"></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. For this demo, the user ID is fixed to 1.
  2. As above, there are 2 steps to the leave application. This is the first step, selecting the leave type and period.
  3. The second step, selecting full/AM/PM for each individual day.

 

4B) THE JAVASCRIPT

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

  // (B) CHECK SELECTED PERIOD
  check : () => {
    // (B1) START & END DATES
    let start = document.getElementById("date_start").value,
        end = document.getElementById("date_end").value;
    if (new Date(start) > new Date(end)) {
      alert("Start date cannot be later than end date");
    }

    // (B2) FORM DATA
    let data = new FormData();
    data.append("req", "check");
    data.append("start", start);
    data.append("end", end);

    // (B3) AJAX CHECK SELECTED PERIOD
    fetch ("3-ajax-leave.php", {
      method: "post", body: data
    })
    .then(res => res.json())
    .then(days => {
      // (B3-1) RESET "SELECT HALF/FULL DAY" FORM
      let row, wrap = document.getElementById("form_days");
      wrap.innerHTML = "";

      // (B3-2) DRAW "SELECT HALF/FULL DAY" FORM
      for (let d in days) {
        row = document.createElement("label");
        row.innerHTML = d;
        wrap.appendChild(row);

        row = document.createElement("select");
        row.dataset.date = d;
        if (days[d]["a"]==0) {
          row.innerHTML = `${days[d]["n"]}`;
          row.disabled = true;
        } else if (days[d]["a"]==1) {
          row.innerHTML = `Full DayPMAMNo Leave`;
        } else {
          row.innerHTML = `PMAMNo Leave`;
        }
        wrap.appendChild(row);
      }

      // (B3-3) APPLY & CANCEL BUTTONS
      row = document.createElement("input");
      row.type = "submit";
      row.value = "Apply";
      wrap.appendChild(row);
      row = document.createElement("input");
      row.type = "button";
      row.value = "Cancel";
      row.onclick = () => { apply.toggle(false); };
      wrap.appendChild(row);

      // (B3-4) TOGGLE HTML FORMS
      apply.toggle(true);
    });

    // (B4) PREVENT FORM SUMBIT
    return false;
  },

  // (C) APPLY LEAVE
  go : () => {
    // (C1) GET & CHECK SELECTED DAYS
    let days = {}, pass = false;
    for (let s of document.querySelectorAll("#form_days select")) {
      days[s.dataset.date] = s.value;
      if (s.value != "0") { pass = true; }
    }
    if (pass === false) { alert("No days selected!"); }

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

    // (C3) AJAX SUBMIT
    fetch ("3-ajax-leave.php", {
      method: "post", body: data
    })
    .then(res => res.text())
    .then(res => {
      // @TODO - REDIRECT TO SOMEWHERE ELSE
      // APPROVAL PROCESS
      // EMAIL CONFIRMATION
      // WHATEVER ELSE
      alert(res);
      window.reload();
    });

    // (C4) PREVENT FORM SUMBIT
    return false;
  }
};

Not going to explain line-by-line and bore you to tears. Basically, Javascript to drive 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. 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.

 

USEFUL 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.
  • Add your own library functions as required – Delete holidays, cancel leave, etc…
  • Do your own leave reports. Excel, Word, PDF, whatever is required.

Yep, 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 – These are your homework.

 

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.