CODEWITHSUNDEEP
phpjquerysqlfullcalendar
curosio
curosio

Reputation: 78

FullCalendar - only load events between dates

sorry for my english! I'm using FullCalendar with sql and php. The thing is that I have a lot of events in the db (more than 4000) and I would like to load only the actual week events and on click to next/prev button load again the events for this week...

My .js is:

$('#calendar').fullCalendar({
    defaultView:'agendaWeek',
    events: base+"v.php?acction=start",
    .....

And my query in v.php is:

$query_visitas ='SELECT *,concat_ws(" ",name,surname) as title, 
visit_id as id, concat_ws(" ",date_start,time_start) as start,
concat_ws(" ",date_end,time_end) as end FROM visits v 
LEFT JOIN pacient p ON v.pacient_id = p.id 
ORDER BY START';

Thanks for the suggestions, because I'm going crazy!

Upvotes: 0

Views: 923

Answers (1)

doublesharp
doublesharp

Reputation: 27607

When you fetch FullCalendar events from the server using JSON the start and end dates of the displayed calendar as sent with the request. See events as a JSON feed for documentation.

You will need to consume these values and use them to filter the results that are returned by your SELECT. It gets a bit tricky because we need to find rows that:

  • Have a end time after start and before end.
  • Have a start time after start and before end.
  • Have a start time before start and and end time after end.

You will need to use CONCAT_WS() to form a valid start and end datetime string, and then convert it to a proper DATE datatype using STR_TO_DATE().

// FullCalendar V1 sends timestamps
$start =  isset($_REQUEST['start'])? intval($_REQUEST['start']) : 0;
$end =  isset($_REQUEST['end'])? intval($_REQUEST['end']) : 0;

// FullCalendar V2 sends ISO8601 date strings
$start =  isset($_REQUEST['start'])? strtotime($_REQUEST['start']) : 0;
$end =  isset($_REQUEST['end'])? strtotime($_REQUEST['end']) : 0;

// convert the timestamps to date strings for the SQL
$start_date = date('Y-m-d', $start);
$end_date = date('Y-m-d', $end);

// ordinarily you would use a prepared statement, but since you didn't specify a driver they variables are included inline - should be sanitized by date()`
$sql = <<<SQL
    SELECT *, 
        visit_id as id, 
        CONCAT_WS(' ', name, surname) as title, 
        CONCAT_WS(' ', date_start, time_start) as start,
        CONCAT_WS(' ', date_end, time_end) as end 
    FROM visits v 
    LEFT JOIN pacient p 
        ON v.pacient_id = p.id 
    WHERE
        -- anything with an end between the start/end
        STR_TO_DATE(CONCAT_WS(' ', date_end, time_end), '%Y-%m-%d %h:%i:%s') BETWEEN '{$start_date}' AND '{$end_date}'
        OR 
        -- anything with an end between the start/end
        STR_TO_DATE(CONCAT_WS(' ', date_start, time_start), '%Y-%m-%d %h:%i:%s') BETWEEN '{$start_date}' AND '{$end_date}'
        OR 
        -- anything with a start before the start and an end after the end
        (
            STR_TO_DATE(CONCAT_WS(' ', date_start, time_start), '%Y-%m-%d %h:%i:%s') < '{$start_date}' 
            AND STR_TO_DATE(CONCAT_WS(' ', date_end, time_end), '%Y-%m-%d %h:%i:%s') > '{$end_date}'
        )
    ORDER BY start, end
SQL;

Upvotes: 1

Related Questions