CREATE TABLE mytable (
id varchar(11) NOT NULL,
title varchar(255) NOT NULL,
description varchar(500) DEFAULT NULL,
url varchar(100) DEFAULT NULL,
color varchar(100) DEFAULT NULL,
freq varchar(100) DEFAULT NULL,
reps varchar(100) DEFAULT NULL,
dtstart varchar(100) DEFAULT NULL,
count varchar(100) DEFAULT NULL,
start date NOT NULL
);
INSERT INTO mytable VALUES (1,'Test1',NULL,NULL,'red','DAILY','2','2023-10-01','2','2023-10-01');
INSERT INTO mytable VALUES (2,'Test1',NULL,NULL,'green','WEEKLY','1','2023-10-01','10','2023-10-01');
INSERT INTO mytable VALUES (3,'Test1',NULL,NULL,'blue','MONTHLY','1','2023-10-01','3','2023-10-01');
INSERT INTO mytable VALUES (4,'Test1',NULL,NULL,'yellow','MONTHLY','1','2023-10-01','6','2023-10-01');
<?php
// Filter events by calendar date
$where_sql = '';
if(!empty($_GET['start']) && !empty($_GET['end'])){
$where_sql .= mysqli_prepare(
$mysqli, " WHERE start BETWEEN '".$_GET['start']."' AND '".$_GET['end']."' "
);
}
// Fetch events from database
$sql = "SELECT
id,
title,
description,
url,
freq As 'rrule.freq',
reps As 'rrule.interval',
start As 'rrule.dtstart',
count As 'rrule.count',
start
FROM mytable $where_sql";
$result = $mysqli->query($sql);
$eventsArr = array();
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
$event = array();
$event['id'] = $row['id'];
$event['title'] = $row['title'];
$event['description'] = $row['description'];
$event['url'] = $row['url'];
$event['rrule'] = array();
$event['rrule']['freq'] = $row['rrule.freq'];
$event['rrule']['reps'] = $row['rrule.interval'];
$event['rrule']['dtstart'] = $row['rrule.dtstart'];
$event['rrule']['count'] = $row['rrule.count'];
$event['start'] = $row['start'];
array_push($eventsArr, $event);
}
}
// Render event data in JSON format
echo json_encode($eventsArr);