jQuery Datepicker Ajax Request to Highlight Days from MySQL

datepicker available days
datepicker available days
With a bit of fiddling you can retrieve a bunch of dates in a given timespan from the database (or elsewhere) and make your jQuery UI Datepicker a bit more practical and informative.

A method within the Datepicker plugin called beforeShowDay can do the following magic:

  1. disable a given day from selection
  2. add a CSS class to a given day
  3. add a tooltip to a given day

Whilst it is probably a bit easier to make the ajax request itself using the beforeShowDay method, that puts us in the uncomfortable position of making 30+ ajax requests, one for every day displayed. If we are showing 2 months or the page has more than one datepicker, thats 60+ requests!

The better option is to make a single request onload, and an additional request whenever the month or year is changed.

The purpose of my implementation is to highlight days which have no due items in my Studiotime time management web software. This allows me to select a due date without worrying about whether I have other items already due on that day. You can just as easily check for other parameters relevant to your application – you need only adjust the sql query.

Lets start with the freeDays array and the onload javascript on the datepicker input.

// declare freeDays global
var freeDays = [];

// perform initial json request for free days
fetchFreeDays();

$(document).ready(function()
{

// fairly standard configuration, importantly containing beforeShowDay and onChangeMonthYear custom methods
$(“#datepicker”).datepicker({
changeMonth: true,
changeYear: true,
showOtherMonths: true,
selectOtherMonths: true,
dateFormat: ‘DD, d MM, yy’,
altField: ‘#date_due’,
altFormat: ‘yy-mm-dd’,
beforeShowDay: highlightDays,
onChangeMonthYear: fetchFreeDays,
firstDay: 1 // rows starts on Monday
});
});

// query for free days in datepicker
function fetchFreeDays(year, month)
{
var start_date = ”;

// if a month and year were supplied, build a start_date in yyyy-mm-dd format
if (year != undefined && month != undefined) {
start_date = year +’-‘;
start_date += month +’-‘;
start_date += ’01’;
}

$.getJSON(“ajax.todos.php?start_date=”+ start_date, function(data){
$.each(data, function(index, value) {
freeDays.push(value.freeDate); // add this date to the freeDays array
});
});
}

// runs for every day displayed in datepicker, adds class and tooltip if matched to days in freeDays array
function highlightDays(date)
{
for (var i = 0; i < freeDays.length; i++) { if (new Date(freeDays[i]).toString() == date.toString()) { return [true, 'free-day', 'no to-do items due']; // [0] = true | false if this day is selectable, [1] = class to add, [2] = tooltip to display } } return [true, '']; } [/javascript] So now we have a working method of requesting a php page on every page load and again every time the month or year selectors are changed. Lets have a look at how the PHP and MySQL script looks: [php] // ajax.todos.php $i = 0; // counter prevents infinite loop $cutoff = '61'; // limit on timespan (in days) $result = array(); // if date is provided, use it, otherwise default to today $start_date = (!empty($start_date)) ? mysql_real_escape_string($start_date) : date('Y-m-d'); $check_date = $start_date; $end_date = date('Y-m-d', strtotime("$start_date +$cutoff days")); // never retrieve more than 2 months while ($check_date != $end_date) { // check if any incomplete todos exist on this date if (mysql_result(mysql_query("SELECT COUNT(id) FROM " . DB_TODOS . " WHERE date_due = '$check_date'"), 0) == 0) { $result[] = array('freeDate' => $check_date);
}

// +1 day to the check date
$check_date = date(‘Y-m-d’, strtotime(“$check_date +1 day”));

// break from loop if its looking like an infinite loop
$i++;
if ($i > $cutoff) break;
}

header(‘Content-type: application/json’);
echo json_encode($result);
[/php]
The CSS rule is as follows:

/* override free days background in jquery ui datepicker */
.free-day {
  background: #2e9500;
}

.free-day a {
  opacity: 0.7;
}

Disclaimer: I am no jQuery ninja and produced this tutorial by combining various other resources that I have linked to below. If you have any suggestions or improvements let everybody benefit by contributing to the comments below!

Notes:

  • I’m not entirely happy with the way this CSS rule looks
  • There is most likely an issue with the ajax result failing to be retrieved before the datepicker pops up, which could be worked around by embedding datepicker into the ajax call

Resources: