Get dates from database to the jQuery Ui Picker in PHP

Question

I am trying to get the dates from the database using PHP. Which will be highlighted in datepicker. To do achieve that I have made a sql query to fetch the dates as shown below.

HTML

Date: <input type="text" id="datepicker">

// Static dates
// An array of dates
var eventDates = {};
eventDates[ new Date( '08/07/2016' )] = new Date( '08/07/2016' );
eventDates[ new Date( '08/12/2016' )] = new Date( '08/12/2016' );
eventDates[ new Date( '08/18/2016' )] = new Date( '08/18/2016' );
eventDates[ new Date( '08/23/2016' )] = new Date( '08/23/2016' );

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

$sql = "SELECT * from `table` WHERE `value` > 0";

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

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

?>
eventDates[new Date('<?php echo $date = $row['date']; ?>')] = new Date(
'<?php echo $date = $row['date']; ?>');
<?php
}
?>

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

So in the above code, I have looped all the dates having value > 0. Which should print all the dates but it doesn't. I have checked the query and it works perfectly in a separate PHP file. It echo out all the date have value > 0.

I am not sure if this is the right method to fetch the date from the database and pass it to the datepicker. There is another method using JSON but I am not familiar with that method since I haven't worked with JSON.

What changes can I make to fetch dates from the database and highlight them in the datepicker?

Have you checked what the completed Javascript actually looks like? — ADyson, Dec 06 '21 at 06:57
@ADyson It shows a blank page. If I replace the ```sql``` query with static manual dates it will show the highlighted dates in datepicker. — tru_shar, Dec 06 '21 at 07:14
No I mean check the generated Javascript in your browser's source code. And check the console for errors. — ADyson, Dec 06 '21 at 07:19
@ADyson got error ```Uncaught Error: Call to a member function fetch_assoc() on bool``` in Javascript and ```Refused to execute script from 'https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.css' because its MIME type ('text/css') is not executable, and strict MIME type checking is enabled.``` in console. — tru_shar, Dec 06 '21 at 07:23
That's actually a PHP error (which has ended up being output into the middle of the Javascript). It means your query failed, so there was nothing to call fetch_assoc on. Turn on mysqli error reporting to find out the exact error — ADyson, Dec 06 '21 at 07:28
@ADyson can you help me out with this? I am not getting what the issue is? https://stackoverflow.com/questions/70285958/dates-fetched-using-ajax-but-not-highlighted-in-jquery-ui-datepicker-php — tru_shar, Dec 09 '21 at 11:33

score 0 · Accepted Answer · answered Dec 06 '21 at 09:23

I have formatted the date to YY/MM/DD from YY-MM-DD. Since the datepicker date formate is YY/MM/DD. If you use the second format with - it will not show the output.

Here is the corrected script code:

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

        $sql = "SELECT * from `table` WHERE `value` > 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>

Get dates from database to the jQuery Ui Picker in PHP

1 Answers1