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, ''];
}

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:

 
// 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);

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:

21 thoughts on “jQuery Datepicker Ajax Request to Highlight Days from MySQL

  1. Hi that’s a great tutorial i finally have my datepicker as I ever wanted!
    I only have two issue:
    the css is actually working only on Chrome .Both on IE8 and firefox day cells remain unchanged.
    Secondly most important , some days (pushed anyway onto the array) are not styled until i pass the month and then come back..why? thanks luca

  2. Hi Luca,

    Regarding the CSS not working outside of Chrome – yes I can confirm i’m getting the same thing – amazing that I haven’t tested outside of Chrome until now!

    Regarding the style not showing until the month has been passed, I have also had intermittent trouble with this as well.

    Unfortunately, I can’t give you any solutions – I played around with it at the time of writing and couldn’t get change the outcome.

    if anyone can post a solution I will gladly update the post – sorry, as I said definitely a work in progress.

  3. actually, no wonder the style is not working in IE and FF, the free-day class is not even being added to the td class.

    That is… interesting.

  4. actually, no wonder the style is not working in IE and FF, the free-day class is not even being added to the td class.

    That is… interesting.

    I’m putting a question on stackoverflow and we can hopefully get this resolved.

  5. http://stackoverflow.com/questions/5428176/jquery-datepicker-beforeshowday-browser-inconsistency-and-update-issues

    The problem is that fetchFreeDays() is asynchronous, so it is possible that $(“#datepicker”).datepicker() finished executeing before you have populated your freeDays array, therefore you don’t see anything when the page first renders.

    Try putting $(“#datepicker”).datepicker() inside your $.getJSONs callback function.

    Sounds promising, haven’t had a change to test yet.

  6. hello sorry for late response!
    ..what does it mean the above??
    maybe this??

    $.getJSON(“33BetaDs.php?startdate=”+ start_date, function(data){

    $.each(data, function(index, value) {
    eventDays.push(value.day); // add this date to the freeDays array

    });

    //##load datetimepicker

    $(“.from,.to”).datetimepicker({
    changeMonth: true,
    changeYear: true,
    showOtherMonths: true,
    selectOtherMonths: true,
    dateFormat: ‘yy-mm-dd’,
    timeFormat: ‘hh:mm’,
    //altField: ‘#date_due’,
    //altFormat: ‘yy-mm-dd’,
    beforeShowDay: styleDays,
    onChangeMonthYear: fetchEventDays,
    firstDay: 1 // rows starts on Monday
    });
    });

    }

  7. yes that was my interpretation as well, and it did resolve the previous month from not fetching in time, but it still doesn’t work in FF or IE. Very mysterious… and unfortunately the stackoverflow thread has gone quiet. I might have to take my question elsewhere.

    function fetchDayUsage(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';
        }
    
        $.ajax({
          async: false,
          url: "ajax.todos?action=fetchDayUsage&start_date="+ start_date,
          dataType: "json",
          success: function(data) {
            // loop over dayUsage array result
            $.each(data, function(index, value) {
              // add to different arrays depending on how much time is allocated in estimates
              switch (value.type) {
                case 'free':
                  freeDays.push(value.date); // add this date to the freeDays array
                break;
              }
            });
    
            // datepicker popout for date due on create new to-do
            $("#datepicker").datepicker({
              changeMonth: true,
              changeYear: true,
              showOtherMonths: true,
              selectOtherMonths: true,
              //numberOfMonths: 2,
              dateFormat: 'DD, d MM, yy',
              altField: '#date_due',
              altFormat: 'yy-mm-dd',
              //beforeShowDay: $.datepicker.noWeekends,
              beforeShowDay: highlightDays,
              onChangeMonthYear: fetchDayUsage,
              firstDay: 1 // rows starts on Monday
            });
          }
        });
    }
  8. Hi Steve,
    Thanks for the post.This is the exact functionality I’m trying to implement. I’m using inline datepicker.The problem i’m facing is that when onChangeMonthYear fires the displayed month changes but year and month belong to selected date only.What I meant is that year,month do not correspond to displayed month and bcoz of that I’m processing only previous month not current month.How can I get year month for currently displayed month,year? Thanx.

  9. Thanks for posting this Steve. I was able to use your code as a starting point but I also ran into the issue of it not working in Firefox so I ended up getting it to work by formatting my own date string as YYYY-MM-DD based on the date object and using that to see if it was in the array using jQuery.inArray() method. You can check out my code here: http://jsbin.com/ikafuz/10/edit

  10. hi,i am new to jquery and ajax. i want to highlight the database dates in calender.i used this code but i cannt get anything. so please help me to complete my reqirement…anyone please.. i tried it for last 2 weeks but i cannt..

  11. HI Steve ,
    Thanks for the great tutorial . It would be great if you update the above code so that to make synchronous ajax requests . I has to goto the next month or click in one of the months first to see the highlighted days . Since all ajax request are sent asynchronous by default , i added the code below before the .getJson method and the magic appears when the page loads at first :) yeye
    $.ajaxSetup({ async: false }); // to make requests sent synchronous

    If you have other ajax request you might want them to be sent asynchronously remember to set it to true( to the default value ) .

  12. I hope this help you with the IE <= 8 opacity problem (if you didn't find the solution since march :) )

    remember that IE <=8 doesn't follow the opacity css property, it use it's own dirty IE ugly wired non standard one
    so add the filter:alpha() to the css pseudo class and it's done

    .free-day a {
    opacity: 0.7;
    filter: alpha(opacity=70);
    }

  13. Try to use Method refresh into .complet()

    $.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
    });
    }).complete(function(){
    $(“#datepicker”).datepicker(“refresh”);
    });;

  14. Hi! Have you implemented this solution in a wordpress loop? I have query for current day (onSelect) and for the onChangeMonthYear, but I can’t highlight the days with posts in my jquery calendar. Can you help me?

    My current day returns posts of current day in a div with ajax and my onChangeMonthYear returns the posts in div with ajax (the same div mentioned previously).

    Thanks!

  15. Hi Thomas!

    I need to program something similar to your example.

    I have a database with news (date – description – link – so on..)
    I would like to change css from every date into datepicker to help users know which date has news.

    Here is my code, I implemented a filter. Just need to change css to those days are loaded on my database.

    Copy code

    $(function() {
    $(“#datepicker”).datepicker({
    changeMonth: true,
    changeYear: true,

    beforeShowDay: // need help here! :)

    onSelect: function(selectedDate) {
    var mydate;
    fecha = $(“#datepicker”).attr(“value”);
    $.ajax({
    type: “GET”,
    url: “news.php”,
    data: ‘datepicker=’+mydate,
    success: function(data) {
    $(‘#result’).html(data);
    }
    });
    }

    });
    });

    I think I can do it with a function: beforeShowDay, but I don’t know how, I am learning jquery and I was surfing the web without luck until now.

    Maybe I need an array to call data from my database, here my script:

    But I don’t now how to call this into my datepicker.

    Hope can help me with this.
    Thank u so much!

  16. Call the .refresh method ($("#dp").datepicker("refresh");) whenever the freeDays variable (or any other constraint which affects the result of beforeShowDay) is changed. This will refresh the datepicker; which in turn calls the beforeShowDate method appropriately. The datepicker display will update even if it is already open.

  17. Hi Steve.

    Your tutorial works fine, but Pedrazzi have an important update for your script. I put the $(“#datepicker”).datepicker(“refresh”); inside the getJson and works fine. I made a lot of searches and your tutorial is the best for this questions.

    Congrats and sorry for my english. :D
    Mateus

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>