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!

ⓘ 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

  • Create a database and import 1-database.sql.
  • Change the database, “slots”, and date settings in 2-lib-appo.php.
  • Access 3a-select.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 APPOINTMENTS

All right, let us now get into more details on how the PHP MySQL appointment system work.

 

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=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

1-database.sql
-- (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

2-lib-appo.php
<?php
class Appointment {
  // (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);
  }

  // (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", "utf8");
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

3a-select.php
<?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

3b-select.js
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

4-book.php
<?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.

 

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.

 

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

 

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 *