Dates fetched using ajax but not highlighted in Jquery UI datepicker, PHP

Question

I am using Jquery datepicker to highlight specific dates. Dates are being fetched from the database in PHP and MySQL. Dates will be fetched depending on the selected value in the select tag. The dates are successfully fetched and shown in the console.

If I fetch all the dates in the script directly then it shows the highlighted dates. But when I use select tag and send the selected value through ajax then it doesn't.

Jquery:

<script>
    $(function() {
        // An array of dates
        var eventDates = {};

        $("#select").on("change", function() {
            var truck = $(this).val();

            $.ajax({
                url: 'ajax/vehicledate.php',
                type: "POST",
                /*contentType: "application/json; charset=utf-8",*/
                data: {
                    val: truck
                },
                success: function(data) {
                    console.log(data);
                }
            });

            // datepicker
            $('#start').datepicker({

                dateFormat: "yy-mm-dd",

                beforeShowDay: function(date) {
                    var highlight = eventDates[date];
                    if (highlight) {
                        return [true, "event", 'Tooltip text'];
                    } else {
                        return [true, '', ''];
                    }
                }
            });
        });
    });
</script>

Jquery to fetch all dates

 <script>
    $(function() {
        // An array of dates
        var eventDates = {};

        <?php        

            $sql = "SELECT * from `ut_trips` WHERE `deisel` > 0";

            $result = $connect->query($sql);   

            while( $final=$result->fetch_assoc() ) 
            {

            ?>

            eventDates[new Date('<?php 
            $orgDate = date($final['date']);
            $date = str_replace('-"', '/', $orgDate);  
            $newDate = date("Y/m/d", strtotime($date));  
            echo $newDate   ?>')] = new Date(
            '<?php $orgDate =date($final['date']); 
            $date = str_replace('-"', '/', $orgDate);  
            $newDate = date("Y/m/d", strtotime($date));  
            echo $newDate  
            ?>'
            );

            <?php

            }

            ?>

            // datepicker
            $('#start').datepicker({

                dateFormat: "yy-mm-dd",

                beforeShowDay: function(date) {
                    var highlight = eventDates[date];
                    if (highlight) {
                        return [true, "event", 'Tooltip text'];
                    } else {
                        return [true, '', ''];
                    }
                }
            });
        });
    });
</script>

ajax PHP file

<?php

include("../partials/connect.php");

$v = $_POST['val'];

$sql = "SELECT * from `table` WHERE `value1` > 0 AND `value2` = '".$v."'";

$result = $connect->query($sql);   

while( $final=$result->fetch_assoc() ) 
{

?>

eventDates[new Date('<?php 
$orgDate = date($final['date']);
$date = str_replace('-"', '/', $orgDate);  
$newDate = date("Y/m/d", strtotime($date));  
echo $newDate   ?>')] = new Date(
'<?php $orgDate =date($final['date']); 
$date = str_replace('-"', '/', $orgDate);  
$newDate = date("Y/m/d", strtotime($date));  
echo $newDate  
?>'
);

<?php

}

?>

console output

eventDates[new Date('2021/11/10')] = new Date(
'2021/11/10'
);


eventDates[new Date('2021/11/12')] = new Date(
'2021/11/12'
);


eventDates[new Date('2021/11/13')] = new Date(
'2021/11/13'
);

Update:

echo out the date in jason format

echo jason_encode($newDate);

Jquery

 dataType: "json",
 success: function(data) {
            console.log(data);
            return data;
 }

using dataType: "json" outputs nothing in console and if I comment It out it will output eventDates[new Date('2021\/11\/12')] = new Date( '2021\/11\/12' ); in colsole.

Answer 1

The best way I can see to update the display after the calendar has already been rendered is to use the refresh method. How to highlight new dates though? You could do that by adding the new dates to your existing set of eventDates, and then simply refreshing the whole calendar - beforeShowDay will process the new, updated set of eventDates, and your new dates will be higlighted.

See the snippet below for a working example.

But firstly, your code is open to SQL injection! Fix this first, immediately, eg How can I prevent SQL injection in PHP? It should be pretty simple, eg maybe something like this:

include("../partials/connect.php");
$sql = "SELECT * from `table` WHERE `value1` > 0 AND `value2` = ?";
$stmt = $connect->prepare($sql);   
$stmt->bind_param('s', $_POST['val']);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // ... etc, some tips below

OK, on to the Javascript. Some notes and suggestions:

1. Currently you are initialising the datepicker inside the select change handler. This means that every time the select is changed, you re-initialise the datepicker. You shouldn't do that - initialise it once, independent of any user interaction.

2. I am not sure I understand why you have structured eventDates that way. At least according to the code/requirements you've shown here, it would be far simpler as a simple array, something like:

   // An array of dates
   var eventDates = [
       "2021/12/01",
       "2021/12/02",
       "2021/12/03",
   ];
   

   I've gone with this for my demo code in the snippet below.

3. In your AJAX call, you're passing a value with val: selectvalue - but selectvalue does not seem to be defined anywhere. You did already find the selected value though - truck. So that's the value you want to send.

4. Convention is that POST is for changing data (eg making a purchase, updating a record), while GET is for reading data. You're just loading some date data, so that really should be a GET. I'd suggest sticking with convention and updating your back end to respond to GET instead of POST.

5. Once you receive your new dates from the AJAX call, you need to add them to the existing set of eventDates. If eventDates is a simple array as suggested above, that's easy (at least if you search SO for answers!): eventDates.push(...newDates);.. Update from discussion in comments, new dates should actually replace existing dates completely: eventDates = newDates;

6. And finally, refresh the whole calendar: $('#start').datepicker("refresh");

Putting it all together, here is a working demo:

$(function() {
    // An array of dates - dummy data for demo
    var eventDates = [
        "2021/12/01",
        "2021/12/02",
        "2021/12/03",
    ];

    // Initialise datepicker - once
    $('#start').datepicker({
        dateFormat: "yy-mm-dd",
        beforeShowDay: function(date) {
            // date is a JS date, we want to compare it to the dates
            // in eventDates, so we need to format it.  Datepicker has
            // a handy utility for formatting dates, see
            // https://api.jqueryui.com/datepicker/#utility-formatDate
            var formatted = $.datepicker.formatDate('yy/mm/dd', date);

            // Easy way to check if a value is in an array
            if (eventDates.indexOf(formatted) === -1) {
                return [true, '', ''];
            } else {
                return [true, "event", 'Tooltip text'];
            }
        }
    });

    $("#select").on("change", function() {
        // Can't do AJAX here, but we can write the success method, and 
        // just use a hard-coded set of results.  Say your AJAX returns
        // a new set of dates, as an array:
        var data = [
            "2021/12/24",
            "2021/12/25",
            "2021/12/26",
        ];
        
        // To merge existing dates with new dates
        // eventDates.push(...data);

        // To show only new dates:
        eventDates = data;

        // So new eventDates is updated with the new dates, we want
        // to reresh the whole calendar.
        $('#start').datepicker("refresh");
        
        // Done!  To be clear, your AJAX success would look like this:
        // success: function(data) {
        //    eventDates.push(...data);
        //    $('#start').datepicker("refresh");
        // }
    }); 
});

.event {
    background-color: red;
}

<link href="https://code.jquery.com/ui/1.13.0/themes/base/jquery-ui.css" rel="stylesheet"/>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://code.jquery.com/ui/1.13.0/jquery-ui.min.js"></script>
<input id="start" type="text">

<select id="select">
    <option>Change me</option>
    <option value="foo">Foo</option>
    <option value="bar">Bar</option>
</select>

As to your PHP, assuming you have fixed the SQL injection problems described earlier, you need to return a JSON array. I can't really work out what your existing code is doing - what format is $final['date']? To demostrate how to create and return your array, I'll just assume it is actually a date in the right format. Adjust as necessary.

// Define the empty array we will return.
$eventDates = [];

// Iterate over your query results ...
while ($final = $result->fetch_assoc()) {
    // ... adding each date to the array, let's say $final['date'] is 
    // formatted like 2021/12/25
    $eventDates[] = $final['date'];
}

// We want to return JSON, so jQuery can read it
header('Content-Type: application/json; charset=utf-8');
echo json_encode($eventDates);