Simple Appointment Booking With PHP MySQL (Free Download)

Welcome to a tutorial on how to create a simple appointment booking system with PHP MySQL. Doctor’s appointments, yoga sessions, spa sessions, lessons, or whatever it is – This is a quick and simple example of an online appointment booking page. Read on!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

PART 1) THE DATABASE

1A) USERS

-- (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=6;

INSERT INTO `users` (`user_id`, `user_name`, `user_email`, `user_password`) VALUES
(1, 'Joa Doe', 'joa@doe.com', '12345'),
(2, 'Job Doe', 'job@doe.com', '12345'),
(3, 'Joe Doe', 'joe@doe.com', '12345'),
(4, 'Jon Doe', 'jon@doe.com', '12345'),
(5, 'Joy Doe', 'joy@doe.com', '12345');

In this example, we shall assume that only registered users can make an appointment. This is a just dummy user table, I will leave links below if you do not already have a user system in place.

• user_id Primary key, auto-increment.
• user_name Full name.
• user_email User’s email. Unique to prevent duplicates.
• user_password The password.

 

 

1B) APPOINTMENTS

-- (B) APPOINTMENTS
CREATE TABLE `appointments` (
  `appo_date` date NOT NULL,
  `appo_slot` varchar(255) NOT NULL,
  `user_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `appointments`
  ADD PRIMARY KEY (`appo_date`,`appo_slot`),
  ADD KEY `user_id` (`user_id`);

This should be pretty self-explanatory. A table to store the appointments.

• appo_date Appointment date, composite primary key.
• appo_slot Appointment slot, composite primary key. This is an open text field for you to decide. For example, AM/PM or hourly slots – “1100, 1200, 1400, 1500”.
• user_id The user who booked this slot. Foreign key.

 

 

PART 2) APPOINTMENT LIBRARY

<?php
class Appointment {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {
    $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
    ]);
  }

  // (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) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) GET APPOINTMENTS IN DATE RANGE
  function get ($from, $to) {
    $this->query(
      "SELECT * FROM `appointments` WHERE `appo_date` BETWEEN ? AND ?",
      [$from, $to]
    );
    $res = [];
    while ($r = $this->stmt->fetch()) {
      $res[$r["appo_date"]][$r["appo_slot"]] = $r["user_id"];
    }
    return $res;
  }

  // (E) SAVE APPOINTMENT
  function save ($date, $slot, $user) {
    // (E1) CHECK SELECTED DATE
    $min = strtotime("+".APPO_MIN." day");
    $max = strtotime("+".APPO_MAX." day");
    $unix = strtotime($date);
    if ($unix<$min || $unix<$max) { $this->error = "Date must be between ".date("Y-m-d", $min)." and ".date("Y-m-d", $max);
    }

    // (E2) CHECK PREVIOUS APPOINTMENT
    $this->query(
      "SELECT * FROM `appointments` WHERE `appo_date`=? AND `appo_slot`=?",
      [$date, $slot]
    );
    if (is_array($this->stmt->fetch())) {
      $this->error = "$date $slot is already booked";
      return false;
    }

    // (E3) CREATE ENTRY
    $this->query(
      "INSERT INTO `appointments` (`appo_date`, `appo_slot`, `user_id`) VALUES (?,?,?)",
      [$date, $slot, $user]
    );
    return true;
  }
}

// (F) APPOINTMENT DATES & SLOTS - CHANGE TO YOUR OWN!
define("APPO_SLOTS", ["Morning", "Afternoon", "Evening"]);
define("APPO_MIN", 1); // next day
define("APPO_MAX", 7); // next week

// (G) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (H) NEW APPOINTMENT OBJECT
$_APPO = new Appointment();

Now that the database is ready, the next step is to create a PHP library to work with it. This looks massive, but keep calm and look closely.

• (A, B, H) When $_APPO = new Appointment() is created, the constructor automatically connects to the database. The destructor closes the connection.
• (C) query() is a simple helper function to run an SQL query.
• (D & E) The “actual” appointment functions.
  • get() Get all appointments in the given date range.
  • save() Save an appointment.
• (F) Appointment date and time slots.
  • APPO_SLOTS As above, everyone will have different “timeslots”. Define yours here.
  • APPO_MIN APPO_MAX Default – Earliest allowed date is tomorrow, the latest is next week. Change to your own.
• (G) Remember to change the database settings to your own.

 

 

PART 3) APPOINTMENT BOOKING PAGE

3A) THE HTML

<?php
// (A) LOAD LIBRARY + INIT
require "2-lib-appo.php";
$start = strtotime("+".APPO_MIN." day");
$end = strtotime("+".APPO_MAX." day");
$booked = $_APPO->get(date("Y-m-d", $start), date("Y-m-d", $end));
?>
 
<!-- (B) SELECT APPOINTMENT DATE/SLOT -->
<h2>SELECT A DATE</h2>
<table id="select">
  <!-- (B1) FIRST ROW : HEADER CELLS -->
  <tr>
    <th></th>
    <?php foreach (APPO_SLOTS as $slot) { echo "<th>$slot</th>"; } ?>
  </tr>
 
  <!-- (B2) FOLLOWING ROWS : DAYS -->
  <?php
  for ($unix=$start; $unix<=$end; $unix+=86400) {
    $thisDate = date("Y-m-d", $unix);
    echo "<tr><th>$thisDate</th>";
    foreach (APPO_SLOTS as $slot) {
      if (isset($booked[$thisDate][$slot])) {
        echo "<td class='booked'>Booked</td>";
      } else {
        echo "<td onclick=\"select(this, '$thisDate', '$slot')\"></td>";
      }
    }
    echo "</tr>";
  }
  ?>
</table>
 
<!-- (C) CONFIRM -->
<h2>CONFIRM</h2>
<form id="confirm" method="post" action="4-book.php">
  <!-- DUMMY USER, FIXED TO 1 FOR DEMO -->
  <input type="hidden" name="user" value="1">
  <input type="text" id="cdate" name="date" readonly placeholder="Select a time slot above">
  <input type="text" id="cslot" name="slot" readonly>
  <input type="submit" id="cgo" value="Go" disabled>
</form>

Not going to explain this page line-by-line, but the HTML interface is divided into two sections.

1. We load the PHP library first. Calculate the start/end dates, and get all the appointments booked within this period.
2. “Choose a time slot” table.
3. Once the user has chosen a date/slot, it will show in this confirmation form – Simply click on “go” to submit.

Well, you can always redo the entire HTML page to your own liking. Using whatever third-party libraries and frameworks of your choice.

 

3B) THE JAVASCRIPT

function select (cell, date, slot) {
  // (A) UPDATE SELECTED CELL
  let last = document.querySelector("#select .selected");
  if (last != null) { last.classList.remove("selected"); }
  cell.classList.add("selected");

  // (B) UPDATE CONFIRM FORM
  document.getElementById("cdate").value = date;
  document.getElementById("cslot").value = slot;
  document.getElementById("cgo").disabled = false;
}

Just a simple function to update the HTML interface when the user picks a date/slot.

 

 

PART 4) SAVE APPOINTMENT

<?php
require "2-lib-appo.php";
echo $_APPO->save ($_POST["date"], $_POST["slot"], $_POST["user"])
  ? "OK" : $_APPO->error;

Finally, we only need to use the save() library function to save the submitted appointment form. Yep, you need to complete this one on your own. See “incomplete system” below.

 

 

INCOMPLETE SYSTEM

My developer senses are tingling, I can hear the trolls screaming “this is an incomplete system”. Of course, this is only a tutorial and a lot of work need to be done.

• Tie into your existing user system, and make sure that only registered users can make appointments. If you don’t already have a user system, see the links below.
• Remember to modify save(). Add your own checks to prevent one person from snapping up all possible slots. For example, users can only book up to 3 slots within 7 days.
• It is possible to open the appointment system to the public, but you will have to find ways to deal with spam.
• On “checkout”, send a confirmation email, SMS, process payment, require a downpayment, whatever your project requires.
• Add your own library functions to allow and deal with cancellations.
• Do your own appointment reports. Simple example – SELECT * FROM `appointments` JOIN `users` USING `user_id` WHERE `appo_date` BETWEEN 'X' AND 'Y'.

The possibilities are endless, and it is impossible to cover everything in this guide – You have to complete your own system.

 

 

LINKS & REFERENCES

• Freeze Rows & Columns Of HTML Tables – Code Boxx
• Simple User Registration With PHP MYSQL – Code Boxx
• Simple User Login System With PHP MySQL – Code Boxx
• JWT Login & Authentication With PHP MYSQL – Code Boxx
• Very Simple Pure HTML CSS Admin Panel – Code Boxx