JavaScript Scheduler with PHP and MySQL for Dynamic CRUD Operations

Introduction This blog post will provide a step-by-step guide on integrating Syncfusion’s...

Introduction

This blog post will provide a step-by-step guide on integrating Syncfusion’s JavaScript Scheduler control with a CRUD (create, read, update, and delete) application using PHP and a MySQL database.

Prerequisites

Make sure the following have global installations of your environment:

• XAMPP server 
• MySQL 

Getting Started with XAMPP Server

XAMPP, which stands for cross-platform Apache, MySQL, PHP, and Perl, is a software bundle that allows developers to create and test dynamic web applications on their local machines, whether they have Windows, Linux, or Mac. By installing XAMPP, developers can easily set up a local web server and work on their projects offline without needing a live internet connection.

Installation:

1.Install the XAMPP server on your system.

2.Once the installation is complete, navigate to the designated path:C:\xampp\htdocs.

3.Within this directory, create a new folder with a desired name (e.g., “ej2-php-crud-service”).

• This newly created folder will serve as the repository for the database configuration and CRUD action PHP files required for the project.
• Proper creation and configuration of these files within this directory are crucial for the smooth functioning of the project.

Creating a MySQL Database and Table

In this example, I have created a database namedSchedulerDB.I created a table in the database namedAppointmentswith the following fields:Id, Subject, StartTime, EndTime, StartTimezone, EndTimezone, Location, Description, IsAllDay, RecurrenceID, FollowingID, RecurrenceRule, RecurrenceException, IsReadonly, IsBlock, RoomID.

The query to create the table is provided in the following code.

CREATE TABLE `appointments` (
 `Id` int(11) NOT NULL,
 `Subject` varchar(200) DEFAULT NULL,
 `StartTime` datetime NOT NULL,
 `EndTime` datetime NOT NULL,
 `StartTimezone` varchar(200) DEFAULT NULL,
 `EndTimezone` varchar(200) DEFAULT NULL,
 `Location` varchar(200) DEFAULT NULL,
 `Description` varchar(200) DEFAULT NULL,
 `IsAllDay` bit(1) NOT NULL,
 `RecurrenceID` int(11) DEFAULT NULL,
 `FollowingID` int(11) DEFAULT NULL,
 `RecurrenceRule` varchar(200) DEFAULT NULL,
 `RecurrenceException` varchar(200) DEFAULT NULL,
 `IsReadonly` bit(1) DEFAULT NULL,
 `IsBlock` bit(1) DEFAULT NULL,
 `RoomID` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Set up Database Configuration

Navigate to C:\xampp\htdocs\ej2-php-crud-service, create a PHP file namedserver.php, and add the following code.

connect_error) { 
 die("Connection failed" . $conn->connect_error);
 }
?>

The previous PHP code snippet demonstrates the process of creating a connection to a MySQL database using theMySQLilibrary. The first step is to set the connection parameters, such as the server’s name, username, password, and database name. These values are stored in variables,$servername, $username, $password,and$dbname,respectively.

The$servernamevariable is set tolocalhost,which means the database is on the same server as the PHP script. The$usernamevariable is set toroot,the default username for a MySQL server. The$passwordvariable for this example is set toSync@1997.The$dbnamevariable is set toSchedulerDB.

Once the connection parameters are set, the script creates a new connection object by calling theMySQLiconstructor and passing the connection parameters as arguments, which are then assigned to the variable$conn.The script then uses theconnect_errormethod to check if the connection is successful. If the connection is unsuccessful, the script displays the error messageConnection failedand exits.

Set up CRUD Configuration

In the same file,server.php, add the code to perform the CRUD actions. This will be the primary location for handling these actions.

The following code sets headers and handles a JSON request on the server side using PHP. It sets the content type and character encoding to JSON and UTF-8. This allows any origin, method, and headers in the requests using CORS headers; exits the script if the request method isOPTIONS; sends an HTTP status 200 OK; reads the raw post data from the client; converts the JSON string to a PHP array; and assigns that array to a variable. This code is a good practice for handling JSON requests and ensures proper communication between the client and server.

header("Content-type:application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: *");
header("Access-Control-Allow-Headers: *");
header('Access-Control-Allow-Origin: *', false);
if($_SERVER['REQUEST_METHOD'] == 'OPTIONS') {
 header( "HTTP/1.1 200 OK" );
 exit;
}
$json_param = file_get_contents('php://input');
$param = json_decode($json_param,true);

Now, let’s write the code for CRUD actions.

if(isset($param['action'])) {
 if ($param['action'] == "insert" || ($param['action'] == "batch" && !empty($param['added']))) {
 error_log("This is an error message");
 if ($param['action'] == "insert") {
 $id = isset($param['value']['Id']) ? $param['value']['Id'] : null;
 $subject = isset($param['value']['Subject']) ? $param['value']['Subject'] : null;
 $startTime = isset($param['value']['StartTime']) ? $param['value']['StartTime'] : null;
 $endTime = isset($param['value']['EndTime']) ? $param['value']['EndTime'] : null;
 $location = isset($param['value']['Location']) ? $param['value']['Location'] : null;
 $description = isset($param['value']['Description']) ? $param['value']['Description'] : null;
 $isAllDay = isset($param['value']['IsAllDay']) ? $param['value']['IsAllDay'] : null;
 $recurrenceId = isset($param['value']['RecurrenceID']) && $param['value']['RecurrenceID'] > 0 ? $param['value']['RecurrenceID'] : null;
 $recurrenceRule = isset($param['value']['RecurrenceRule']) ? $param['value']['RecurrenceRule'] : null;
 $recurrenceException = isset($param['value']['RecurrenceException']) && !empty($param['value']['RecurrenceException']) ? $param['value']['RecurrenceException'] : null;
 $roomId = isset($param['value']['RoomID']) && !empty($param['value']['RoomID']) ? $param['value']['RoomID'] : null;

 $startTime = clone new DateTime($startTime);
 $timezone = new DateTimeZone('Asia/Calcutta');
 $startTime->setTimezone($timezone);
 $startTime = $startTime->format('Y-m-d H:i:s');
 $endTime = clone new DateTime($endTime);
 $timezone = new DateTimeZone('Asia/Calcutta');
 $endTime->setTimezone($timezone);
 $endTime = $endTime->format('Y-m-d H:i:s');

 if($recurrenceRule == null) {
 $sql = "INSERT INTO `appointments` (`Id`, `Subject`, `StartTime`, `EndTime`, `Location`, `Description`, `IsAllDay`, `RoomID`) VALUES ('$id', '$subject', '$startTime', '$endTime', '$location','$description', '$isAllDay', '$roomId')";
 } else {
 if($recurrenceId == null) {
 $sql = "INSERT INTO `appointments` (`Id`, `Subject`, `StartTime`, `EndTime`, `Location`, `Description`, `IsAllDay`, `RecurrenceRule`, `RoomID`) VALUES ('$id', '$subject', '$startTime', '$endTime', '$location','$description', '$isAllDay', '$recurrenceRule', '$roomId')";
 } else {
 $sql = "INSERT INTO `appointments` (`Id`, `Subject`, `StartTime`, `EndTime`, `Location`, `Description`, `IsAllDay`, `RecurrenceID`, `RecurrenceRule`, `RecurrenceException`, `RoomID`) VALUES ('$id', '$subject', '$startTime', '$endTime', '$location','$description', '$isAllDay', '$recurrenceId', '$recurrenceRule', '$recurrenceException', '$roomId')";
 }
 }
 $result = $conn->query($sql);
 }
 else if ($param['action'] == "batch" && !empty($param['added'])) {
 foreach($param['added'] as $add) {
 $id =isset($add['Id']) ? $add['Id'] : null;
 $subject = isset($add['Subject']) ? $add['Subject'] : null;
 $startTime = isset($add['StartTime']) ? $add['StartTime'] : null;
 $endTime = isset($add['EndTime']) ? $add['EndTime'] : null;
 $location = isset($add['Location']) ? $add['Location'] : null;
 $description = isset($add['Description']) ? $add['Description'] : null;
 $isAllDay = isset($add['IsAllDay']) ? $add['IsAllDay'] : null;
 $recurrenceId = isset($add['RecurrenceID']) && $add['RecurrenceID'] > 0 ? $add['RecurrenceID'] : null;
 $recurrenceRule = isset($add['RecurrenceRule']) ? $add['RecurrenceRule'] : null;
 $recurrenceException = isset($add['RecurrenceException']) && !empty($add['RecurrenceException']) ? $add['RecurrenceException'] : null;
 $roomId = isset($add['RoomID']) && !empty($add['RoomID']) ? $add['RoomID'] : null;
 $startTime = clone new DateTime($startTime);
 $timezone = new DateTimeZone('Asia/Calcutta');
 $startTime->setTimezone($timezone);
 $startTime = $startTime->format('Y-m-d H:i:s');
 $endTime = clone new DateTime($endTime);
 $timezone = new DateTimeZone('Asia/Calcutta');
 $endTime->setTimezone($timezone);
 $endTime = $endTime->format('Y-m-d H:i:s');

 if($recurrenceRule == null) {
 $sql = "INSERT INTO `appointments` (`Id`, `Subject`, `StartTime`, `EndTime`, `Location`, `Description`, `IsAllDay`, `RoomID`) VALUES ('$id', '$subject', '$startTime', '$endTime', '$location','$description', '$isAllDay', '$roomId')";
 } else {
 if($recurrenceId == null) {
 $sql = "INSERT INTO `appointments` (`Id`, `Subject`, `StartTime`, `EndTime`, `Location`, `Description`, `IsAllDay`, `RecurrenceRule`, `RoomID`) VALUES ('$id', '$subject', '$startTime', '$endTime', '$location','$description', '$isAllDay', '$recurrenceRule', '$roomId')";
 } else {
 $sql = "INSERT INTO `appointments` (`Id`, `Subject`, `StartTime`, `EndTime`, `Location`, `Description`, `IsAllDay`, `RecurrenceID`, `RecurrenceRule`, `RecurrenceException`, `RoomID`) VALUES ('$id', '$subject', '$startTime', '$endTime', '$location','$description', '$isAllDay', '$recurrenceId', '$recurrenceRule', '$recurrenceException', '$roomId')";
 }
 }
 $result = $conn->query($sql);
 }
 }
 }
}

This PHP code block is used to insert appointment details into a database. We first check whether theactionparameter is set, whether it isinsertorbatch, and whether there are added values. If these conditions are met, the code block sets several variables by checking whether specific values exist in thevaluearray of theparamvariable, such as the subject, start time, end time, and so on. It then converts the start and end times to a specific time zone and formats them as a string in theY-m-d H:i:sformat. Finally, it creates an SQL query to insert the data into the appointments table, with different columns included depending on whether therecurrenceRuleorrecurrenceIdvariables are null. The SQL query is then executed using the MySQLi query function.

if ($param['action'] == "update" || ($param['action'] == "batch" && !empty($param['changed']))) {
 if ($param['action'] == "update") {
 $id = isset($param['value']['Id']) ? $param['value']['Id'] : null;
 $subject = isset($param['value']['Subject']) ? $param['value']['Subject'] : null;
 $startTime = isset($param['value']['StartTime']) ? $param['value']['StartTime'] : null;
 $endTime = isset($param['value']['EndTime']) ? $param['value']['EndTime'] : null;
 $location = isset($param['value']['Location']) ? $param['value']['Location'] : null;
 $description = isset($param['value']['Description']) ? $param['value']['Description'] : null;
 $isAllDay = isset($param['value']['IsAllDay']) ? $param['value']['IsAllDay'] : false;
 $recurrenceId = isset($param['value']['RecurrenceID']) && $param['value']['RecurrenceID'] > 0 ? $param['value']['RecurrenceID'] : null;
 $recurrenceRule = isset($param['value']['RecurrenceRule']) ? $param['value']['RecurrenceRule'] : null;
 $recurrenceException = isset($param['value']['RecurrenceException']) && !empty($param['RecurrenceException']) ? $param['value']['RecurrenceException'] : null;
 $roomId = isset($param['value']['RoomID']) && !empty($param['RoomID']) ? $param['value']['RoomID'] : null;

 $startTime = clone new DateTime($startTime);
 $timezone = new DateTimeZone('Asia/Calcutta');
 $startTime->setTimezone($timezone);
 $startTime = $startTime->format('Y-m-d H:i:s');
 $endTime = clone new DateTime($endTime);
 $timezone = new DateTimeZone('Asia/Calcutta');
 $endTime->setTimezone($timezone);
 $endTime = $endTime->format('Y-m-d H:i:s');

 if($recurrenceRule == null){
 $sql = "UPDATE `appointments` SET `Subject` = '$subject', `StartTime` = '$startTime', `EndTime` = '$endTime', `Location` = '$location', `Description` = '$description', `IsAllDay` = '$isAllDay', `RoomID` = '$roomId' WHERE `appointments`.`Id` = '$id'";
 }
 else {
 $sql = "UPDATE `appointments` SET `Subject` = '$subject', `StartTime` = '$startTime', `EndTime` = '$endTime', `Location` = '$location', `Description` = '$description', `IsAllDay` = $isAllDay, `RecurrenceID` = $recurrenceId,`RecurrenceRule` = $recurrenceRule,`RecurrenceException` = $recurrenceException, `RoomID` = '$roomId' WHERE `appointments`.`Id` = '$id'";
 }
 $result = $conn->query($sql);
 }
 else if ($param['action'] == "batch" && !empty($param['changed'])) {
 foreach($param['changed'] as $update) {
 $id = isset($update['Id']) ? $update['Id'] : null;
 $subject = isset($update['Subject']) ? $update['Subject'] : null; 
 $startTime = isset($update['StartTime']) ? $update['StartTime'] : null; 
 $endTime = isset($update['EndTime']) ? $update['EndTime'] : null; 
 $location = isset($update['Location']) ? $update['Location'] : null;
 $description = isset($update['Description']) ? $update['Description'] : null;
 $isAllDay = isset($update['IsAllDay']) ? $update['IsAllDay'] : false;
 $recurrenceId = isset($update['RecurrenceID']) && $update['RecurrenceID'] > 0 ? $update['RecurrenceID'] : null;
 $recurrenceRule = isset($update['RecurrenceRule']) ? $update['RecurrenceRule'] : null;
 $recurrenceException = isset($update['RecurrenceException']) && !empty($update['RecurrenceException']) ? $update['RecurrenceException'] : null;
 $roomId = isset($update['RoomID']) && !empty($update['RoomID']) ? $update['RoomID'] : null;

 $startTime = clone new DateTime($startTime);
 $timezone = new DateTimeZone('Asia/Calcutta');
 $startTime->setTimezone($timezone);
 $startTime = $startTime->format('Y-m-d H:i:s');
 $endTime = clone new DateTime($endTime);
 $timezone = new DateTimeZone('Asia/Calcutta');
 $endTime->setTimezone($timezone);
 $endTime = $endTime->format('Y-m-d H:i:s');

 if($recurrenceRule == null){
 $sql = "UPDATE `appointments` SET `Subject` = '$subject', `StartTime` = '$startTime', `EndTime` = '$endTime', `Location` = '$location', `Description` = '$description', `IsAllDay` = '$isAllDay', `RoomID` = '$roomId' WHERE `appointments`.`Id` = $id";
 }
 else {
 if($recurrenceId == null) {
 if($recurrenceException == null) {
 $sql = "UPDATE `appointments` SET `Subject` = '$subject', `StartTime` = '$startTime', `EndTime` = '$endTime', `Location` = '$location', `Description` = '$description', `IsAllDay` = '$isAllDay', `RecurrenceRule` = '$recurrenceRule', `RoomID` = '$roomId' WHERE `appointments`.`Id` = '$id'";
 } else {
 $sql = "UPDATE `appointments` SET `Subject` = '$subject', `StartTime` = '$startTime', `EndTime` = '$endTime', `Location` = '$location', `Description` = '$description', `IsAllDay` = '$isAllDay', `RecurrenceRule` = '$recurrenceRule', `RecurrenceException` = '$recurrenceException', `RoomID` = '$roomId' WHERE `appointments`.`Id` = '$id'";
 }
 } else {
 $sql = "UPDATE `appointments` SET `Subject` = '$subject', `StartTime` = '$startTime', `EndTime` = '$endTime', `Location` = '$location', `Description` = '$description', `IsAllDay` = '$isAllDay', `RecurrenceID` = '$recurrenceId', `RecurrenceRule` = '$recurrenceRule', `RecurrenceException` = '$recurrenceException', `RoomID` = '$roomId' WHERE `appointments`.`Id` = '$id'";
 }
 }
 $result = $conn->query($sql);
 }
 }
}

The previous code block is used to update appointment details in the database. It first checks whether theactionparameter is set toupdateorbatch, and whether there are changed values. If these conditions are met, it sets several variables by checking whether specific values exist in thevaluearray of theparamvariable, such as the subject, start time, end time, and so on. It then converts the start and end times to a specific time zone and formats them as a string in the format ofY-m-d H:i:s. Finally, it creates an SQL query to update the data in the appointments table with the new values, with different columns included depending on whether therecurrenceRuleis null. The SQL query is then executed using the MySQLi query function. If the action isbatch, it will iterate through all the changed appointments and update them one by one.

if ($param['action'] == "remove" || ($param['action'] == "batch" && !empty($param['deleted']))) {
 if ($param['action'] == "remove") {
 $id = $param['key'];
 $sql = "DELETE FROM `appointments` WHERE `Id`='$id'"; 
 $result = $conn->query($sql);
 }
 else if ($param['action'] == "batch" && !empty($param['deleted'])) {
 foreach($param['deleted'] as $delete) { 
 if($delete['Id'] != null)
 {
 $id = $delete['Id'];
 $sql = "DELETE FROM `appointments` WHERE `Id`='$id'";
 $result = $conn->query($sql);
 } 
 }
 }
}

This previous code block is used to delete appointment details from a database. It first checks whether theactionparameter is set toremoveorbatchand whether there are deleted values. If the action isremove, it sets the variable$idto the value of thekeyparameter and creates an SQL query to delete the appointment with that specific ID from the appointments table. If the action isbatch, it will iterate through all the deleted appointments and delete them by their specific ID. The SQL query is then executed using the MySQLi query function.

$json = array();
if (isset($param["StartDate"]) && isset($param["EndDate"])) {
 $sql = "SELECT * FROM `appointments`";
 $appointmentList = $conn->query($sql);
 $json = $appointmentList->fetch_all(MYSQLI_ASSOC);
}
echo json_encode($json, JSON_NUMERIC_CHECK);

Finally, this code block retrieves appointment details from a database and returns them as a JSON-encoded string. It first checks whether theStartDateandEndDateparameters are set. If they are, it creates an SQL query to select all the data from the appointments table. The query is executed using the MySQLi query function, and the result is saved in the$appointmentListvariable. The result is then fetched and saved in the$jsonvariable and encoded as a JSON string using thejson_encode()function. TheJSON_NUMERIC_CHECKflag is passed to ensure that numeric values are encoded as numbers, not strings. Finally, the encoded JSON string is printed.

Set up Scheduler UI

We will now design a user-friendly, front-end interface utilizing the Syncfusion JavaScript Scheduler component and integrate it with the previously established server.

First, we create anindex.phpfile within our application located atC:\xampp\htdocs \ej2-php-crud-service. This file will serve as the primary front-end interface for our application.

To utilize Syncfusion’s Scheduler component within our application, we include the necessary CDN links within the head tag of our code. This allows us to access and implement the component within our application.

We will now begin crafting the code for the HTML body. The body content is straightforward and simple, adding a div element with an ID ofSchedule. This div will serve as the container for our Scheduler component, which will be appended via script.