HTML Calendar and JS problems

I would like to make a Javascript-based calendar for managing holidays. My problem is, I am using a SQL database to store the holidays, which I am accessing with PHP. How can I use the results of the PHP query in a JS Script ?

For example, to add holidays for an employee i have to do that :

<?php $req='select Nom,DateDebut,DateFin,NbJour from conge where Statut="Valide"';
$res=mysqli_prepare($connect,$req);
$var=mysqli_stmt_execute($res);
$var=mysqli_stmt_bind_result($res,$Nom,$DateDebut,$DateFin,$NbJour);
while(mysqli_stmt_fetch($res)) {
   // ???
}
?>
<!-- partial -->
<script src='https://cdnjs.cloudflare.com/ajax/libs/semantic-ui/2.2.7/semantic.min.js'></script>
<script src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js'></script>
<script src='https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.17.1/moment.min.js'></script>
      
<script src='https://cdnjs.cloudflare.com/ajax/libs/fullcalendar/3.1.0/fullcalendar.min.js'></script><script>
          
$(document).ready(function() {
    $('#calendar').fullCalendar({
        header: {
            left: 'prev,next today',
            center: 'title',
            right: 'month,basicWeek,basicDay'
        },
        defaultDate: '<?php echo date("Y-m-d");?>',
        navLinks: true, 
        editable: true,
        eventLimit: true, 
        events: [
            {
                title: '<?php echo $_SESSION["Login"]?>',
                start: '2021-04-30',
                end: '2021-05-03'
            }

When I am just using a <?php echo $_SESSION ?> it is working perfectly. But when I try with the loop around stmt_fetch($res) it’s not working. How could I do that?

Answer

Since best practices dictate that you should separate your PHP and presentational code as much as possible, what you want to do is build an array of data within PHP, and then safely dump it into your JavaScript when it’s needed. Always use json_encode() when inserting data into JavaScript from PHP to ensure proper escaping of values.

I’d also recommend against using mysqli; but if you must, at least use the object-oriented interface. It’s much easier to use and results in a slightly more modern-looking code.

<?php
$query = 'SELECT Nom, DateDebut, DateFin, NbJour FROM conge WHERE Statut="Valide"';
$stmt = $connect->prepare($query);
$stmt->execute();
$stmt->bind_result($Nom, $DateDebut, $DateFin, $NbJour);
$events = [];
while($stmt->fetch()) {
    $events[] = [
        "title" => "Vacances de $Nom",
        "start" => $DateDebut,
        "end" => $DateFin
    ];
}
?>

$(document).ready(function() {
    $('#calendar').fullCalendar({
        header: {
            left: 'prev, next, today',
            center: 'title',
            right: 'month, basicWeek, basicDay'
        },
        defaultDate: '<?= json_encode(date("Y-m-d"), JSON_HEX_TAG) ?>',
        navLinks: true, 
        editable: true,
        eventLimit: true, 
        events: <?= json_encode($events, JSON_HEX_TAG) ?>
    });
})();