PHP MYSQL Appointment Booking

0-PHP-APPOINTMENT.MD

PHP MYSQL APPOINTMENT BOOKING SYSTEM

https://code-boxx.com/appointment-booking-php-mysql/

NOTES

1. Create a database and import 1-database.sql.
2. Change the database, "slots", and date settings in 2-lib-appo.php.
3. Access 3a-select.php in the browser.

LICENSE

Copyright by Code Boxx

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

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');

-- (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`);

2-lib-appo.php

<?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();

3a-select.php

<!DOCTYPE html>
<html>
  <head>
    <title>PHP MySQL Appointment</title>
    <meta charset="utf-8">
    <script src="3b-select.js"></script>
    <link rel="stylesheet" href="3c-select.css">
  </head>
  <body>
    <?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 -->
    <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 -->
    <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>
  </body>
</html>

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;
}

3c-select.css

/* (A) WHOLE PAGE */
* {
  font-family: Arial, Helvetica, sans-serif;
  box-sizing: border-box;
}
body {
  max-width: 1000px;
  margin: 0 auto;
  padding: 10px;
  background: #f2f2f2;
}

/* (B) SELECT APPOINTMENT DATE/SLOT */
#select {
  width: 100%;
  border-collapse: collapse;
}
#select th, #select td {
  text-align: center;
  padding: 15px;
  border: 1px solid #d7d7d7;
}
#select th {
  font-weight: 700;
  color: #fff;
  background: #2460ab;
}
#select td { background: #fff; }
#select td.booked {
  color: #b1b1b1;
  background: #f3f3f3;
}
#select td.selected { background: #feffaa; }

/* (C) CONFIRMATION FORM */
#confirm {
  display: flex;
  padding: 10px;
  background: #e1e1e1;
}
#confirm input[type=text] {
  padding: 10px;
  margin-right: 10px;
  border: 0;
}
#confirm input[type=text]:focus {
  outline-width: 0;
}
#confirm input[type=submit] {
  padding: 10px 20px;
  border: 0;
  color: #fff;
  background: #2460ab;
  cursor: pointer;
}
#confirm input[type=submit]:disabled { background: #9d9d9d; }

4-book.php

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

// UP TO YOU TO MODIFY & COMPLETE
// USE AJAX TO CALL SAVE()
// MODIFY SAVE() TO SEND EMAIL
// SAVE TO GOOGLE FORM
// PROCESS ONLINE PAYMENT
// MUST PAY DEPOSIT TO CONFIRM
// WHATEVER YOUR PROJECT REQUIRES...

Thank you for this example .
I want appointment 3 person for one date and samte time
if 3 person is appointment is book
by using if and count persons for 3 person for one date
how to solve this problem
Thanks