Hi,
A few months back I posted about having a search box that would show the next occurrence date and was given a fantastic helper class on these forums. However I’ve run into a few problems in terms of speed of the search, as more entries are being entered the search is getting slower and slower to generate (sometimes up to 20 seconds). I was wondering if anyone knows of a way I can improve the load speed for the search?
At the moment I have separated the search from the actual page and load it asynchronously via AJAX after the scheduler has been loaded. However the scheduler seems to operate synchronously when it saves, when I try and save/move an item it just builds up a cue of changes to push to the server whenever it can. Which will bypass my other asynchronous AJAX checks that are required (and work fine after the search is finished loading). Is it possible to change the scheduler to run asynchronously as well?
Otherwise I’ll need to switch the search’s AJAX to synchronous so I can have the other checks running, which means the user could be stuck waiting for the search to load for a while :\
The helper class (slightly modified from the original):
[code]<?php
class SchedulerDate {
public $ev_id;
public $start_date;
public $end_date;
public $text;
public $ev_pid;
public $service;
public $address;
public $abn;
public $rec_type;
public $event_length;
public $type;
public $coun;
public $coun2;
public $day;
public $days;
public $result;
public $updates;
public $updates_time;
public $date_start_general;
function __construct($data, $updates, $date_start_general) {
$start_date = date_parse($data['start_date']);
$this->start_date = mktime($start_date['hour'], $start_date['minute'], $start_date['second'], $start_date['month'], $start_date['day'], $start_date['year']);
$end_date = date_parse($data['end_date']);
$this->end_date = mktime($end_date['hour'], $end_date['minute'], $end_date['second'], $end_date['month'], $end_date['day'], $end_date['year']);
$this->date_start_general = $date_start_general;
if ($this->end_date == false) {
$this->end_date = mktime(0, 0, 0, 1, 1, 2038);
}
$this->text = $data['text'];
$this->rec_type = $data['rec_type'];
$this->event_length = $data['event_length'];
$this->ev_id = $data['tasks_id'];
$this->ev_pid = $data['event_pid'];
$this->service = $data['service'];
$this->address = $data['address'];
$this->abn = $data['abn'];
$this->orig_event = $data;
if ($this->rec_type != '') {
list($this->type, $this->coun, $this->coun2, $this->day, $this->days, $this->extra) = preg_split("/(_|#)/", $this->rec_type);
}
$this->days = preg_split("/,/",$this->days);
if ($this->days[0] == '') {
$this->days = array();
}
$this->updates = $updates;
}
function get_day_of_week($time_stamp) {
$week_day = getdate($time_stamp);
$week_day = $week_day['wday'];
return $week_day;
}
function get_updates($cur_date) {
if (isset($this->updates[$cur_date])) {
if ($this->updates[$cur_date]['event_pid'] == $this->ev_id) {
if ($this->updates[$cur_date]['rec_type'] == 'none') {
return 1;
}
if ($this->updates[$cur_date]['rec_type'] == '') {
return array('tasks_id' => $this->updates[$cur_date]['tasks_id'], 'start_date' => $this->updates[$cur_date]['start_date'], 'end_date' => $this->updates[$cur_date]['end_date'], 'text' => $this->updates[$cur_date]['text'], 'rec_type' => $this->rec_type, 'event_pid' => $this->updates[$cur_date]['event_pid'], 'event_length' => $this->event_length );
}
return 0;
}
} else {
return 0;
}
}
function get_days($cur_date, $date_start, $date_end) {
$day = 60*60*24;
for ($j = 0; $j<7; $j++) {
$week_day = $this->get_day_of_week($cur_date);
if (in_array($week_day, $this->days)) {
if (($cur_date > $date_start)&&($cur_date < $date_end)) {
$changes = $this->get_updates($cur_date);
if ($changes == 0) {
if ($cur_date > $this->start_date) {
$this->result[] = array('tasks_id' => $this->ev_id, 'start_date' => date("Y-m-d H:i:s", $cur_date), 'end_date' => date("Y-m-d H:i:s", $cur_date + $this->event_length), 'text' => $this->text, 'rec_type' => $this->rec_type, 'event_pid' => $this->ev_pid, 'event_length' => $this->event_length, 'service' => $this->service, 'address' => $this->address, 'abn' => $this->abn );
}
} elseif ($changes != 1) {
$this->result[] = $changes;
}
}
}
$cur_date += $day;
}
return $cur_date;
}
function get_day($cur_date, $date_start, $date_end) {
$cur_date = mktime(date("H", $cur_date), date("i", $cur_date), date("s", $cur_date), date("m", $cur_date), 1, date("Y", $cur_date));
$coun = ($this->day - 1)*7;
$cday = $this->get_day_of_week($cur_date);
$nday = $this->coun2*1 + $coun - $cday + 1;
if ($nday <= $coun) {
$cur_date = mktime(date("H", $cur_date), date("i", $cur_date), date("s", $cur_date), date("m", $cur_date), $nday+7, date("Y", $cur_date));
} else {
$cur_date = mktime(date("H", $cur_date), date("i", $cur_date), date("s", $cur_date), date("m", $cur_date), $nday, date("Y", $cur_date));
}
if (($cur_date > $date_start)&&($cur_date < $date_end)) {
$changes = $this->get_updates($cur_date);
if ($changes == 0) {
if (date("Y-m-d H:i:s", $cur_date) > $this->date_start_general) {
$this->result[] = array('tasks_id' => $this->ev_id, 'start_date' => date("Y-m-d H:i:s", $cur_date), 'end_date' => date("Y-m-d H:i:s", $cur_date + $this->event_length), 'text' => $this->text, 'rec_type' => $this->rec_type, 'event_pid' => $this->ev_pid, 'event_length' => $this->event_length, 'service' => $this->service, 'address' => $this->address, 'abn' => $this->abn );
}
} elseif ($changes != 1) {
$this->result[] = $changes;
}
}
return $cur_date;
}
function transpositor($date_start) {
$step = 1;
$day = 60*60*24;
if (!$this->coun) {
return;
}
if (($this->type == 'day')||($this->type == 'week')) {
if ($this->type == 'week') {
$step = 7;
}
$diff = $date_start - $this->start_date;
$diff = floor($diff/$day);
$delta = floor($diff/($this->coun*$step));
if ($delta>=0) {
$this->start_date = mktime(date("H", $this->start_date), date("i", $this->start_date), date("s", $this->start_date), date("m", $this->start_date), date("d", $this->start_date) + $delta*$step*$this->coun, date("Y", $this->start_date));
}
} else {
if ($this->type == 'year')
$step = 12;
$delta = ceil(((date("Y", $date_start)*12 + date("m", $date_start)*1) - (date("Y", $this->start_date)*12 + date("m", $this->start_date)*1))/($step*$this->coun));
if ($delta>=0) {
$this->start_date = mktime(date("H", $this->start_date), date("i", $this->start_date), date("s", $this->start_date), date("m", $this->start_date)+$delta*$step*$this->coun, date("d", $this->start_date), date("Y", $this->start_date));
}
}
}
function date_generator($date_start, $date_end) {
$final = array();
$day = 60*60*24;
$cur_date = $this->start_date;
$this->result = array();
$i = 0;
while (($date_end > $cur_date)&&($this->end_date > $cur_date)) {
$cur_date = $this->start_date;
if (($this->type == 'day')||($this->type == 'week')) {
$st = 1;
if ($this->type == 'week') {
$st = 7;
}
$step = $st*$i*$this->coun;
$cur_date += $step*$day;
} elseif (($this->type == 'month')||($this->type == 'year')) {
$st = 1;
if ($this->type == 'year') {
$st = 12;
}
$step = $st*$i*$this->coun;
$cur_date = mktime(date("H", $cur_date), date("i", $cur_date), date("s", $cur_date),date("m", $cur_date) + $step, date("d", $cur_date), date("Y", $cur_date));
}
$this->get_correct_date($cur_date, $date_start, $date_end);
$i++;
if ($this->type == '')
break;
}
return $this->result;
}
function get_correct_date($cur_date, $date_start, $date_end) {
$final = array();
$day = 60*60*24;
if (count($this->days)) {
$week_day = $this->get_day_of_week($cur_date);
$cur_date -= ((--$week_day)*$day);
$cur_date = $this->get_days($cur_date, $date_start, $date_end);
if (date("Y-m-d H:i:s", $cur_date) > $this->date_start_general) {
$ev = $this->orig_event;
$ev['start_date'] = date("Y-m-d H:i:s", $cur_date);
$ev['end_date'] = date("Y-m-d H:i:s", $cur_date + $this->event_length);
$this->result[] = $ev;
// $this->result[] = array(‘tasks_id’ => $this->ev_id, ‘start_date’ => date(“Y-m-d H:i:s”, $cur_date), ‘end_date’ => date(“Y-m-d H:i:s”, $cur_date + $this->event_length), ‘text’ => $this->text, ‘rec_type’ => $this->rec_type, ‘event_pid’ => $this->ev_pid, ‘event_length’ => $this->event_length, ‘service’ => $this->service, ‘address’ => $this->address, ‘abn’ => $this->abn );
}
} elseif (($this->coun2 != ‘’)&&($this->day != ‘’)) {
$cur_date = $this->get_day($cur_date, $date_start, $date_end);
} else {
if (($cur_date > $date_start)&&($cur_date < $date_end)) {
$changes = $this->get_updates($cur_date);
if ($changes == 0) {
if (date(“Y-m-d H:i:s”, $cur_date) > $this->date_start_general) {
$ev = $this->orig_event;
$ev[‘start_date’] = date(“Y-m-d H:i:s”, $cur_date);
$ev[‘end_date’] = date(“Y-m-d H:i:s”, $cur_date + $this->event_length);
$this->result[] = $ev;
// $this->result[] = array(‘tasks_id’ => $this->ev_id, ‘start_date’ => date(“Y-m-d H:i:s”, $cur_date), ‘end_date’ => date(“Y-m-d H:i:s”, $cur_date + $this->event_length), ‘text’ => $this->text, ‘rec_type’ => $this->rec_type, ‘event_pid’ => $this->ev_pid, ‘event_length’ => $this->event_length, ‘service’ => $this->service, ‘address’ => $this->address, ‘abn’ => $this->abn );
}
} elseif ($changes != 1) {
$this->result[] = $changes;
}
}
}
}
}
class SchedulerHelper
{
public $date_start;
public $date_end;
public $date_start_ts;
public $date_end_ts;
public $connect;
public $table_name;
public $field_start;
public $field_end;
function __construct($connect, $table_name, $fields="start_date,end_date") {
$this->connect = $connect;
$this->table_name = $table_name;
list($this->field_start, $this->field_end) = explode(",", $fields);
$this->field_start = trim($this->field_start);
$this->field_end = trim($this->field_end);
}
function get_dates($date_start, $date_end) {
$this->date_start = $date_start;
$this->date_end = $date_end;
$date_start = date_parse($date_start);
$this->date_start_ts = mktime($date_start['hour'], $date_start['minute'], $date_start['second'], $date_start['month'], $date_start['day'], $date_start['year']);
$date_end = date_parse($date_end);
$this->date_end_ts = mktime($date_end['hour'], $date_end['minute'], $date_end['second'], $date_end['month'], $date_end['day'], $date_end['year']);
$final = array();
$updates = Array();
$query = "SELECT * FROM ".$this->table_name." WHERE `rec_type`='none' OR (`rec_type`='' AND `event_length`!='0')";
$res = mysql_query($query, $this->connect);
for ($i = 0; $i < mysql_num_rows($res); $i++) {
$event = mysql_fetch_assoc($res);
$updates[mysql_result($res, $i, 'event_length')] = $event;
}
$query = "SELECT * FROM ".$this->table_name." WHERE (`start_date`<='".($this->date_end)."' AND `end_date`>='".($this->date_start)."')";
$res = mysql_query($query, $this->connect);
while ($data = mysql_fetch_assoc($res)) {
$event_cur = new SchedulerDate($data, $updates, $this->date_start);
$event_cur->transpositor($this->date_start_ts);
$final_temp = $event_cur->date_generator($this->date_start_ts, $this->date_end_ts);
foreach ($final_temp as $v) {
$final[] = $v;
}
}
return $final;
}
function get_event($id, $date_start = "1970-01-01", $date_end = "2038-01-01") {
$this->date_start = $date_start;
$this->date_end = $date_end;
$date_start = date_parse($date_start);
$this->date_start_ts = mktime($date_start['hour'], $date_start['minute'], $date_start['second'], $date_start['month'], $date_start['day'], $date_start['year']);
$date_end = date_parse($date_end);
$this->date_end_ts = mktime($date_end['hour'], $date_end['minute'], $date_end['second'], $date_end['month'], $date_end['day'], $date_end['year']);
$final = array();
$updates = Array();
$query = "SELECT * FROM ".$this->table_name." WHERE `rec_type`='none' OR (`rec_type`='' AND `event_length`!='0')";
$res = mysql_query($query, $this->connect);
for ($i = 0; $i < mysql_num_rows($res); $i++) {
$event = mysql_fetch_assoc($res);
$updates[mysql_result($res, $i, 'event_length')] = $event;
}
$query = "SELECT * FROM ".$this->table_name." WHERE (`start_date`<='".($this->date_end)."' AND `end_date`>='".($this->date_start)."') AND tasks_id='".$id."'";
$res = mysql_query($query, $this->connect);
while ($data = mysql_fetch_assoc($res)) {
$event_cur = new SchedulerDate($data, $updates, $this->date_start);
$event_cur->transpositor($this->date_start_ts);
$final_temp = $event_cur->date_generator($this->date_start_ts, $this->date_end_ts);
foreach ($final_temp as $v) {
$final[] = $v;
}
}
return $final;
}
function get_rosterevent($id, $date_start = "1970-01-01", $date_end = "2038-01-01") {
$this->date_start = $date_start;
$this->date_end = $date_end;
$date_start = date_parse($date_start);
$this->date_start_ts = mktime($date_start['hour'], $date_start['minute'], $date_start['second'], $date_start['month'], $date_start['day'], $date_start['year']);
$date_end = date_parse($date_end);
$this->date_end_ts = mktime($date_end['hour'], $date_end['minute'], $date_end['second'], $date_end['month'], $date_end['day'], $date_end['year']);
$final = array();
$updates = Array();
$query = "SELECT * FROM ".$this->table_name." WHERE `rec_type`='none' OR (`rec_type`='' AND `event_length`!='0')";
$res = mysql_query($query, $this->connect);
for ($i = 0; $i < mysql_num_rows($res); $i++) {
$event = mysql_fetch_assoc($res);
$updates[mysql_result($res, $i, 'event_length')] = $event;
}
$query = "SELECT * FROM ".$this->table_name." WHERE (`start_date`<='".($this->date_end)."' AND `end_date`>='".($this->date_start)."') AND id='".$id."'";
$res = mysql_query($query, $this->connect);
while ($data = mysql_fetch_assoc($res)) {
$event_cur = new SchedulerDate($data, $updates, $this->date_start);
$event_cur->transpositor($this->date_start_ts);
$final_temp = $event_cur->date_generator($this->date_start_ts, $this->date_end_ts);
foreach ($final_temp as $v) {
$final[] = $v;
}
}
return $final;
}
}
?>[/code]
And the file that is used to generate the search (my site runs off mysqli, while the scheduler class uses mysql, which is why the first few lines setup a different connection to the rest of the code):
[code]<?php
include “…/includes/defs.php”;
include “…/includes/SchedulerHelper.php”;
include “…/includes/mysql.php”;
$conn = @ mysql_connect(HOST, USER, PASSWORD)
or die(“Could not connect”);
mysql_select_db(CAL_INFO_DB, $conn)
or show_error();
$table_name = “tasks”;
$fields = “start_date, end_date”;
$helper = new SchedulerHelper($conn, $table_name, $fields);
$abn = mysql_real_escape_string($_GET[‘abn’], $conn);
/*
* getNextDate is used to retrieve date of the next occurence of an event
* Params:
helper: A SchedulerHelper object
recType: The reccurrence pattern of an event
id: The id of an event
*/
function getNextDate($helper, $recType = “”, $id = ‘-1’) {
if ($id != “-1”) {
$startDate = date(“Y-m-d”);
$recType = explode("_", $recType);
$endDate = date(“Y-m-d”, strtotime("+".$recType[1]." ".$recType[0]));
$evs = array();
$event = $helper->get_event($id, $startDate, $endDate);
//Change the date format and store the returned event(s)
for ($i = 0; $i < count($event); $i++) {
$event[$i][“start_date”] = date(“d-m-Y”, strtotime($event[$i][“start_date”]));
$evs[] = $event[$i];
}
$event = array();
for($j = 0; $j < count($evs); $j++) {
$ev = $evs[$j];
//Set the first event to be the potential returned event
if ($j == 0)
$event = $ev;
if ($ev != "") {
for($i = 0; $i < count($evs); $i++) {
if (strtotime($evs[$i]['start_date']) >= strtotime(date("Y-m-d"))) {
//On the (originally) same day AND id's don't equal then the new occurrence takes the place of the series
if (strtotime(date("d-m-Y", (int)$evs[$i]['event_length'])) == strtotime(date("d-m-Y", strtotime($ev['start_date']))) && $evs[$i]['tasks_id'] != $ev['tasks_id'] && $evs[$i] != "" && $evs[$j] != "") {
if ($evs[$i]['event_pid'] == "0") {
$evs[$i] = $ev;
$evs[$j] = "";
} else {
$evs[$j] = $evs[$i];
$evs[$i] = "";
$event = $evs[$j];
}
}
$i = count($evs);
} else {
$evs[$i] = "";
}
}
}
}
$event['start_date'] = date("Y-m-d H:i:s", strtotime($event['start_date']));
$connection2 = calendar_dbOpen();
//Update the next_series_date for the correct db item
// Was the original event a series?
if ($recType != "") {
if ($event['event_pid'] == "0") {
$q = "UPDATE tasks SET next_series_date = '".$event['start_date']."' WHERE tasks_id = '".$event['tasks_id']."'";
} else {
$q = "UPDATE tasks SET next_series_date = '".$event['start_date']."' WHERE tasks_id = '".$event['event_pid']."'";
}
} else {
$q = "UPDATE tasks SET next_series_date = '".$event['start_date']."' WHERE tasks_id = '".$event['tasks_id']."'";
}
$r = mysqli_query($connection2, $q);
my_dbClose($connection2);
}
return $event['start_date'];
}
/*
* checkDatesInArray is used to check if a date is already in use to remove duplicates
* Params:
arrs: the array of objects to check
item: the object to check against
*/
function checkDatesInArray($arrs = “”, $item) {
$items = array();
for($i = 0; $i < count($arrs); $i++) {
//is the start date of array item in the greater than that of the item being checked AND is it the same address and service?
if (strtotime($arrs[$i][2]) > strtotime($item[2]) && $arrs[$i][4] == $item[4] && $arrs[$i][14] == $item[14]) {
return $i;
}
}
return count($arrs);
}
$html = "";
$html .= "var addressList = [";
$con = calendar_dbOpen();
//Get the next occurrences for the series' that are in the future
$query = "SELECT event_pid, tasks_id FROM tasks WHERE abn = '$abn' AND event_pid != '0' AND end_date >= CURDATE()";
$result = mysqli_query($con, $query);
$q = "";
if (mysqli_num_rows($result) > 0) {
while ($item = mysqli_fetch_array($result))
$q .= " OR tasks_id = '" . $item[0] . "' OR tasks_id = '" . $item[1] . "'";
$q = substr($q, 4);
}
if (strlen($q) == 0)
$q = "tasks_id = '0'";
//SELECT everything related to the task where the id's are of the previous search.
//tasks returned will be those of future occurrences and the series relating to those occurrences
$query = "SELECT * FROM tasks WHERE ($q) AND end_date >= CURDATE() ORDER BY address ASC";
$result = mysqli_query($con, $query);
$itemsExcluded = array();
$items = array();
$tmpItem = array();
//Loop though all returned results
while ($item = mysqli_fetch_array($result)) {
$tmpItem[] = $item;
}
my_dbClose($con);
for($counterx = 0; $counterx < count($tmpItem); $counterx++) {
$item = $tmpItem[$counterx];
//if event_pid == 0, then check when it's next occurrence is and set that as the start date
if ($item[6] == "0") {
$item[2] = getNextDate($helper, $item[5], $item[0]);
}
$tasksIdList[] = $item[0];
//Don't allow a series to be shown if an occurrence occurs on that day
$newId = checkDatesInArray($items, $item);
$itemsExcluded[] = $items[$newId][0];
$items[$newId] = $item;
}
$con = calendar_dbOpen();
$ii = 0;
//Work out which events need to be excluded so that both series and occurrence dates are not displayed for the same event.
while($ii < count($items)) {
$newId = "N/A";
for($i = 0; $i < count($items); $i++) {
//If same address and service but not checking the exact same event
if ($items[$i][4] == $items[$ii][4] && $items[$i][14] == $items[$ii][14] && $i != $ii) {
if (strtotime(date("Y-m-d H:i:s", strtotime($items[$i][2]))) > strtotime(date("Y-m-d H:i:s", strtotime($items[$ii][2])))) {
$occurrance = strtotime(date("Y-m-d H:i:s", strtotime($items[$i][2])));
//is there a rec_type and the has not been removed?
if ($items[$ii][5] != "" && $items[$ii][5] != "none") {
$rec = $items[$ii][5];
$rec = explode("_", $rec);
$ed = strtotime("+".$rec[1]." ".$rec[0]);
if ($ed < $occurrance) {
$newId = $i;
} else {
$newId = $ii;
}
} else {
$rec = $items[$ii][5];
$newId = $i;
}
} else {
$newId = $ii;
}
$i = count($items);
$itemsExcluded[] = $items[$newId][0];
$items[$newId] = "";
}
}
$ii++;
}
//Get all the tasks details for the company and have not been excluded due to being duplicate events
$query = "SELECT rec_type, tasks_id FROM tasks WHERE abn = '$abn' AND end_date >= CURDATE()";
$r = mysqli_query($con, $query);
while ($item = mysqli_fetch_array($r))
$tmpItems[] = $item;
my_dbClose($con);
foreach ($tmpItems as $item) {
getNextDate($helper, $item[0], $item[1]);
}
$con = calendar_dbOpen();
//Grab the required information to display exclude the items found previously as these will cause duplicates in the autocomplete
$query = "SELECT address, service, event_length, start_date, end_date, rec_type, event_pid, next_series_date FROM tasks WHERE abn = '$abn' AND end_date >= CURDATE()";
for($i = 0; $i < count($itemsExcluded); $i++)
$query .= " AND tasks_id != '".$itemsExcluded[$i]."'";
$query .= " ORDER BY address";
$result = mysqli_query($con, $query);
$items = array();
while ($item = mysqli_fetch_array($result)) {
$items[] = $item;
}
$counterx = mysqli_num_rows($result);
my_dbClose($con);
//loop though all the results to find the next occurrence date
//Then store this date in $html along with the other data required for the autocomplete options with onselect displaying the lightbox for the selected item
//Label: address (service) [date]
//Value: address (service)
for($i = 0; $i < $counterx; $i++) {
$sDate = $items[$i][3];
$eDate = $items[$i][4];
$next = $items[$i][7];
//If occurrence
if ($items[$i][6] != "0" && strtotime(date("Y-m-d",strtotime($sDate))) >= strtotime(date("Y-m-d"))) {
$sDate = date('d-m-Y', strtotime($sDate));
$html .= "{label:'" . $items[$i][0] . " (" . $items[$i][1] . ") [" . $sDate . "]', value:'" . $items[$i][0] . " (" . $items[$i][1] . ")'},\n";
//If series
} else if (strtotime($eDate) >= strtotime(date("Y-m-d"))) {
if ($recType[0] == "month" || $recType[0] == "year") {
$html .= "{label:'" . $items[$i][0] . " (" . $items[$i][1] . ")', value:'" . $items[$i][0] . " (" . $items[$i][1] . ")'},";
} else {
if ($next == "1970-01-01 00:00:00")
$next = $sDate;
$html .= "{label:'" . $items[$i][0] . " (" . $items[$i][1] . ") [" . date("d-m-Y", strtotime($next)) . "]', value:'" . $items[$i][0] . " (" . $items[$i][1] . ")'},";
}
}
}
$html .= "];";
mysql_close($conn);
//Prints the variable addressList to the page to be used in autocomplete field
echo $html;
?>[/code]
Regards,
Dean