CODEWITHSUNDEEP
phpmysqlsqlfullcalendar
Giorgi
Giorgi

Reputation: 609

JQuery Full Calendar limit events with PHP

Im using JQuery full calendar event. I know that so far there isn't an option that will load the events by limit.

In month view if i have 100 events on 1 day it makes the table cell go very large. I would like to limit it to 10 events per day.

What i thought i would do was to do it on server side using PHP. Since im loading all events using PHP given the date range, i thought i would load day by day and limit it to 10 events per day.

This is what i have on PHP side:

   if ($currView == 'month') //curr view is the current view of the calendar
    {
        $start = $_GET['start']; //gets from the calendar usually start of month
        $end = $_GET['end']; //gets from calendar usually end of month
        //some array to store all events before using json_encode
        $limitEvents = array();
        $limit = 10;
        //loop through from start till end by 1 day incremental
        for ($i = $start; $i < $end; $i+=strtotime($i, "+1 day"))
        {
            //make the end of day to be the current day but 23:59:59
            $endOfDay = date('m/d/Y', $start);
            $endOfDay = strtotime($endOfDay . ' 23:59:59');
            //load the events from DB using date range which is 1 day and limit of events
            $loaded = $this->loadDataFromDB($start, $endofDay, $limit);
            //merge the arrays
            array_merge($limitTasks, $loaded);
        }
    }

now the problem with this code is that its not optimal when i try to run it it loops through each day and queries per day and takes very long time, it even times out.

My question is, how would i do this on MySQL side? I would give the date range from start of the month and end of the month. Instead of looping day by day on PHP side and loading data per day, i would do that on MySQL side. For example query would look like something:

  SELECT *
  FROM Events_Table
  WHERE Ev_Date BETWEEN :startMonth AND :endMonth 
  (have extra query to load by days within given start and end month
   and have LIMIT 10 per day)

cant finish the above query, the text in parenthesis need to be transferred in valid query so that it selects 10 events for each day and there are 31 days so it should select around 310 events

If anyone has a better solution please help.

Upvotes: 0

Views: 2378

Answers (1)

scott
scott

Reputation: 1070

if you want to have at most 10 per month, change this like below to reduce the limit as you loop through the days...

$currView == 'month') //curr view is the current view of the calendar
    {
        $start = $_GET['start']; //gets from the calendar usually start of month
        $end = $_GET['end']; //gets from calendar usually end of month
        //some array to store all events before using json_encode
        $limitEvents = array();
        $limit = 10;
        //loop through from start till end by 1 day incremental
        for ($i = $start; $i < $end; $i+=strtotime($i, "+1 day"))
        {
            //make the end of day to be the current day but 23:59:59
            $endOfDay = date('m/d/Y', $start);
            $endOfDay = strtotime($endOfDay . ' 23:59:59');
            //load the events from DB using date range which is 
            //1 day and limit of events
            if ($limit>0){ //if you want to have 10 in total add this
              $loaded = $this->loadDataFromDB($start, $endofDay, $limit);
              $limit -= count($loaded); //and this
            } //and this
            //merge the arrays
            array_merge($limitTasks, $loaded);
        }

and for your mysql query to limit the amount you actually get

SELECT *
  FROM Events_Table
  WHERE Ev_Date BETWEEN :startMonth AND :endMonth 
  LIMIT 10

Then if you are still having it go slow, check your database for proper index on your fields. When you are using start and end month in the where clause, they will query much faster when you add an index to each

Also, not sure what you are using to run your queries, but make sure you add single quotes around your placeholders or you can still have mysql injection happen; just escaping the variable is not enough

Upvotes: 1

Related Questions