javascriptphpsqlfullcalendarfullcalendar-scheduler

how to display event on fullCalendar with resourceTimeGrid and room reservations connected to db?


I want to create a room reservation web with fullCalendar display and consists of 3 rooms only, connected to mysql database.

So I already input the data into db and checked phpyadmin, the data was succesfully inserted schedule_list tbl

but it wasn't displayed on the calendar fullCalendar img

what did i miss ? this is my index php using bootstrap index.php

<?php require_once('db-connect.php') ?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Scheduling</title>
    <link rel="stylesheet" href="https://pro.fontawesome.com/releases/v5.10.0/css/all.css" integrity="sha384-AYmEC3Yw5cVb3ZcuHtOA93w35dYTsvhLPVnYs9eStHfGJvOvKxVfELGroGkvsg+p" crossorigin="anonymous"/>
    <link rel="stylesheet" href="./css/bootstrap.min.css">
    <link rel="stylesheet" href="./fullcalendar/lib/main.min.css">
    <script src="./js/jquery-3.6.0.min.js"></script>
    <script src="./js/bootstrap.min.js"></script>
    <script src='https://cdn.jsdelivr.net/npm/fullcalendar-scheduler@6.1.10/index.global.min.js'></script>
    <!-- <script src='https://cdn.jsdelivr.net/npm/fullcalendar/index.global.min.js'></script> -->
    <!-- <script src="./fullcalendar/lib/main.min.js"></script> -->
    <style>
        :root {
            --bs-success-rgb: 71, 222, 152 !important;
        }

        html,
        body {
            height: 100%;
            width: 100%;
            font-family: Apple Chancery, cursive;
        }

        .btn-info.text-light:hover,
        .btn-info.text-light:focus {
            background: #000;
        }
        table, tbody, td, tfoot, th, thead, tr {
            border-color: #ededed !important;
            border-style: solid;
            border-width: 1px !important;
        }
    </style>
</head>

<body class="bg-light">
    <nav class="navbar navbar-expand-lg navbar-dark bg-dark bg-gradient" id="topNavBar">
        <div class="container">
            <a class="navbar-brand" href="https://fortius-si.co.id/">
            FSI
            </a>

            <div>
                <b class="text-light">Room Schedules</b>
            </div>
        </div>
    </nav>
    <div class="container py-5" id="page-container">
        <div class="row">
            <div class="col-md-9">
                <div id="calendar"></div>
            </div>
            <div class="col-md-3">
                <div class="cardt rounded-0 shadow">
                    <div class="card-header bg-gradient bg-primary text-light">
                        <h5 class="card-title">Schedule Form</h5>
                    </div>
                    <div class="card-body">
                        <div class="container-fluid">
                            <form action="save_schedule.php" method="post" id="schedule-form">
                                <input type="hidden" name="id" value="">
                                <div class="form-group mb-2">
                                  <label for="room" class="control-label">Room</label>
                                  <select class="form-control form-control-sm rounded-0" name="room" id="room" required>
                                      <option value="a">Room A</option>
                                      <option value="b">Room B</option>
                                      <option value="c">Room C</option>
                                  </select>
                                </div>
                                <div class="form-group mb-2">
                                    <label for="title" class="control-label">Title</label>
                                    <input type="text" class="form-control form-control-sm rounded-0" name="title" id="title" required>
                                </div>
                                <div class="form-group mb-2">
                                    <label for="description" class="control-label">Description</label>
                                    <textarea rows="3" class="form-control form-control-sm rounded-0" name="description" id="description" required></textarea>
                                </div>
                                <div class="form-group mb-2">
                                    <label for="start_datetime" class="control-label">Start</label>
                                    <input type="datetime-local" class="form-control form-control-sm rounded-0" name="start_datetime" id="start_datetime" required>
                                </div>
                                <div class="form-group mb-2">
                                    <label for="end_datetime" class="control-label">End</label>
                                    <input type="datetime-local" class="form-control form-control-sm rounded-0" name="end_datetime" id="end_datetime" required>
                                </div>
                            </form>
                        </div>
                    </div>
                    <div class="card-footer">
                        <div class="text-center">
                            <button class="btn btn-primary btn-sm rounded-0" type="submit" form="schedule-form"><i class="fa fa-save"></i> Save</button>
                            <button class="btn btn-default border btn-sm rounded-0" type="reset" form="schedule-form"><i class="fa fa-reset"></i> Cancel</button>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <!-- Event Details Modal -->
    <div class="modal fade" tabindex="-1" data-bs-backdrop="static" id="event-details-modal">
        <div class="modal-dialog modal-dialog-centered">
            <div class="modal-content rounded-0">
                <div class="modal-header rounded-0">
                    <h5 class="modal-title">Schedule Details</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body rounded-0">
                    <div class="container-fluid">
                        <dl>
                            <dt class="text-muted">Title</dt>
                            <dd id="title" class="fw-bold fs-4"></dd>
                            <dt class="text-muted">Description</dt>
                            <dd id="description" class=""></dd>
                            <dt class="text-muted">Start</dt>
                            <dd id="start" class=""></dd>
                            <dt class="text-muted">End</dt>
                            <dd id="end" class=""></dd>
                        </dl>
                    </div>
                </div>
                <div class="modal-footer rounded-0">
                    <div class="text-end">
                        <button type="button" class="btn btn-primary btn-sm rounded-0" id="edit" data-id="">Edit</button>
                        <button type="button" class="btn btn-danger btn-sm rounded-0" id="delete" data-id="">Delete</button>
                        <button type="button" class="btn btn-secondary btn-sm rounded-0" data-bs-dismiss="modal">Close</button>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <!-- Event Details Modal -->

<?php 
$schedules = $conn->query("SELECT * FROM `schedule_list`");
$sched_res = [];
foreach($schedules->fetch_all(MYSQLI_ASSOC) as $row){
    $row['sdate'] = date("F d, Y h:i A",strtotime($row['start_datetime']));
    $row['edate'] = date("F d, Y h:i A",strtotime($row['end_datetime']));
    $sched_res[$row['id']] = $row;
}
?>
<?php 
if(isset($conn)) $conn->close();
?>
</body>
<script>
    var scheds = $.parseJSON('<?= json_encode($sched_res) ?>')
</script>
<script src="./js/script.js"></script>

</html>

script.js this is my script.js contains fullCalendar configuration settings with resource time grid 4 days but i just display 3 days and resources are 3 rooms consists of Room A, Room B, Room C with id: 'a','b','c'

let events = [];
document.addEventListener('DOMContentLoaded',function() {
  if (!!scheds) {
      Object.keys(scheds).map(k => {
          let row = scheds[k]
          events.push({ id: row.id, title: row.title, start: row.start_datetime, end: row.end_datetime, room_id: row.room_id });
      })
  }

  let calendar = new FullCalendar.Calendar(document.getElementById('calendar'), {
    schedulerLicenseKey: 'CC-Attribution-NonCommercial-NoDerivatives',
    initialView: 'resourceTimeGridFourDay',
    headerToolbar: {
      left: 'prev,next',
      center: 'title',
      right: 'resourceTimeGridDay,resourceTimeGridFourDay'
    },
    views: {
      resourceTimeGridFourDay: {
        type: 'resourceTimeGrid',
        duration: { days: 3 },
        buttonText: '3 days'
        }
    },
      selectable: true,
      themeSystem: 'bootstrap',
      resources: [
        { id: 'a', title: 'Room A' },
        { id: 'b', title: 'Room B' },
        { id: 'c', title: 'Room C'}
      ],
      events: events.map(event => ({
        ...event,
        resourceId: event.room_id
      })),
      eventClick: function(info) {
          let _details = $('#event-details-modal')
          let id = info.event.id
          if (!!scheds[id]) {
              _details.find('#title').text(scheds[id].title)
              _details.find('#description').text(scheds[id].description)
              _details.find('#start').text(scheds[id].sdate)
              _details.find('#end').text(scheds[id].edate)
              // _details.find('#room').text(scheds[id].room)
              _details.find('#edit,#delete').attr('data-id', id)
              _details.modal('show')
          } else {
              alert("Event is undefined");
          }
      },

      editable: true,
      timeFormat: 'H(:mm)'
  });

  calendar.render();

        // Form reset listener
  $('#schedule-form').on('reset', function() {
      $(this).find('input:hidden').val('')
      $(this).find('input:visible').first().focus()
  })

        // Edit Button
  $('#edit').click(function(e) {
    e.preventDefault()
      let id = $(this).attr('data-id')
      if (!!scheds[id]) {
          let _form = $('#schedule-form')
          console.log(String(scheds[id].start_datetime), String(scheds[id].start_datetime).replace(" ", "\\t"))
          _form.find('[name="id"]').val(id)
          _form.find('[name="title"]').val(scheds[id].title)
          _form.find('[name="description"]').val(scheds[id].description)
          _form.find('[name="start_datetime"]').val(String(scheds[id].start_datetime).replace(" ", "T"))
          _form.find('[name="end_datetime"]').val(String(scheds[id].end_datetime).replace(" ", "T"))
          $('#event-details-modal').modal('hide')
          _form.find('[name="title"]').focus()
      } else {
          alert("Event is undefined");
      }
  })

        // Delete Button / Deleting an Event
  $('#delete').click(function() {
    e.preventDefault()
      let id = $(this).attr('data-id')
      if (!!scheds[id]) {
          let _conf = confirm("Are you sure to delete this scheduled event?");
          if (_conf === true) {
              location.href = "../delete_schedule.php?id=" + id;
          }
      } else {
        alert("Event is undefined");
      }
  })
})

save_schedule.php

<?php 
require_once('db-connect.php');
if($_SERVER['REQUEST_METHOD'] !='POST'){
    echo "<script> alert('Error: No data to save.'); location.replace('./') </script>";
    $conn->close();
    exit;
}
extract($_POST);
$allday = isset($allday);

if(empty($id)){
    $sql = "INSERT INTO `schedule_list` (`title`,`description`,`start_datetime`,`end_datetime`, `room_id`) VALUES ('$title','$description','$start_datetime','$end_datetime', '$room')";
}else{
    $sql = "UPDATE `schedule_list` set `title` = '{$title}', `description` = '{$description}', `start_datetime` = '{$start_datetime}', `end_datetime` = '{$end_datetime}' `room_id` = '$room' where `id` = '{$id}'";
}
$save = $conn->query($sql);
if($save){
    echo "<script> alert('Schedule Successfully Saved.'); location.replace('./') </script>";
}else{
    echo "<pre>";
    echo "An Error occured.<br>";
    echo "Error: ".$conn->error."<br>";
    echo "SQL: ".$sql."<br>";
    echo "</pre>";
}
$conn->close();
?>

I used room id based on ids in script.js on resources with id: a,b,c and room A, room B, room C. I already inserted the room data in room_list table consists of id and room_name. the room_id on schedule_list table is the FK of id in room_list table.


Solution

  • Yes, as I expected the problem was with the client-side on script.js it should be like this

    if (!!scheds) {
      Object.keys(scheds).map(k => {
          let row = scheds[k]
          events.push({ id: row.id, title: row.title, start: row.start_datetime, end: row.end_datetime, resourceId: row.room_id });
      })
    

    }

    I should've used resourceId:row.room_id instead of room_id:row.room_id so yeah its minor mistake.