Fetch FullCalender.io Event Objects (JSON) from Database for a specific date-range

Question

I have a huge database and a lot of events. Every time I go to the Calendar Mainpage (monthly view) all the existing events in the database are loaded, despite I'm only viewing the current month. At the moment my html page is over 3MB big and tends to slow down my browser tab.

To solve this problem I started to change the code to fetch only the events from the current month as json. Unfortunately, the start and end dates for the date-range are not working - the page is still fetching all events from the database. I have already done a few hours of research and a lot of tweaks.

Im using FullCalendar V3.10

So far I managed to fetch my FullCalendar.io events - I have two event sources - so I used:

eventSources: [
    {
        url: 'include/load-calendar-event.php', // use the `url` property
        color: '#008000',   
    },              
    {
        url: 'include/load-calendar-event-retour.php', // use the `url` property
        color: '#008000',
    },                          

    // any other sources...
],

The corresponding two files to fetch the events are almost identical - so one should be enough:

load-calendar-event.php

require_once('bdd.php');
$sql = "SELECT * FROM messages"; // this selects all rows
$req = $bdd->prepare($sql);
$req->execute();
$events = $req->fetchAll();

$data = array();

foreach($events as $event) {
    $start = explode(" ", $event['start']);
    $end = explode(" ", $event['end']);
    if($start[1] == '00:00:00'){
        $start = $start[0];
    }else{
        $start = $event['start'];
    }
    if($end[1] == '00:00:00'){
        $end = $end[0];
    }else{
        $end = $event['end'];
    }   

    $data[] = array(    
        'id'=> $event['id'],
        'title'=> $event['title'],                              
        'start'=> $start,
        'end'=> $end,
        'color'=> $event['color']   
    );
}

echo json_encode($data);

By calling the Calendar Page the browsers makes the following calls - please note the date (2020-01-01 - 2020-02-01) and data format:

...include/load-calendar-event.php?start=2020-01-01&end=2020-02-01&_=1578601056565

and

...include/load-calendar-event-retour.php?start=2020-01-01&end=2020-02-01&_=1578601056566

screenshot of call in firebug

The date format of the events are saved in the database with the following format (YYYY-MM-DD HH:mm:ss):

2020-01-11 10:00:00

Question:

Any idea how to fetch the events ONLY for the current month?

user2355051 · Accepted Answer · 2020-01-09 21:16:39Z

1

Why are you selecting all the rows in the table and iterating over them?

You need to update your query to pull in the start and end values you're passing as a $_GET parameter.

require_once('bdd.php');
$sdate = $_GET['start']; //LIKE THIS
$edate = $_GET['end']; //AND THIS
$sql = "SELECT * FROM messages WHERE date >= ".$sdate." AND date <= ".$edate; // this selects all rows.  Change 'date' to whatever your column name is in the database.
$req = $bdd->prepare($sql);
$req->execute();
$events = $req->fetchAll();





$data = array();

foreach($events as $event) {

    $start = explode(" ", $event['start']);
    $end = explode(" ", $event['end']);
    if($start[1] == '00:00:00'){
        $start = $start[0];
    }else{
        $start = $event['start'];
    }
    if($end[1] == '00:00:00'){
        $end = $end[0];
    }else{
        $end = $event['end'];
    }   

    $data[] = array(    
        'id'=> $event['id'],
        'title'=> $event['title'],                              
        'start'=> $start,
        'end'=> $end,
        'color'=> $event['color']   
    );

}


echo json_encode($data);

You could do this more easily by simply using PHP's date function to generate the start and end dates instead of passing in variables with your query string params. Although this will give you more flexibility. You can even include some if/else statements to generate different queries if these values are set.

See: https://www.php.net/manual/en/function.date.php

answered Jan 9, 2020 at 21:16

user2355051

6551 gold badge10 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

eva last Over a year ago

Hi, thx so far. Any idea how to change the date format? i tried your code and it returns no events - i guess its due to the different date formats...

user2355051 Over a year ago

what format is the date in the database and what is the field set as? YYYY-MM-DD Within mysql run this query and post the output. SHOW CREATE TABLE messages

eva last Over a year ago

The format is YYYY-MM-DD HH:mm:ss and set as datetime in the database.

ADyson Over a year ago

This concept is good but OP should be warned that is is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. Never insert unsanitised data directly into your SQL like this. The way this code is written now, someone could easily steal, incorrectly change, or even delete the data.

Collectives™ on Stack Overflow

Fetch FullCalender.io Event Objects (JSON) from Database for a specific date-range

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related