Javascript Calendar

[cutielou22]

I have a javascript calendar and I want to make it so when a event is added to a mysql table it can be seen on the calendar. I know you can't put SELECT and other mysql stuff in a script so how can I do this?

HTML:

<div id="main" class="container">
  <span class="jumbotron">
    <h1 class="text-center">
      <a id="left" href="#"> 
        <i class="fas fa-chevron-left"></i>
      </a>
      <span id="month"></span>
      <span id="year"></span>
       <a id="right" href="#"> 
        <i class="fas fa-chevron-right"></i>
        </a>
	</h1>
    </span>

  <span class="row">
    <span class="col-sm-10 col-sm-offset-1"></span>
  </span>
   <table class="table"></table>
</div>

JAVASCRIPT for calendar:

$(document).ready(function() {
  var currentDate = new Date();
  function generateCalendar(d) {
    function monthDays(month, year) {
      var result = [];
      var days = new Date(year, month, 0).getDate();
      for (var i = 1; i <= days; i++) {
        result.push(i);
      }
      return result;
    }
    Date.prototype.monthDays = function() {
      var d = new Date(this.getFullYear(), this.getMonth() + 1, 0);
      return d.getDate();
    };
    var details = {
      // totalDays: monthDays(d.getMonth(), d.getFullYear()),
      totalDays: d.monthDays(),
      weekDays: ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'],
      months: ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'],
    };
    var start = new Date(d.getFullYear(), d.getMonth()).getDay();
    var cal = [];
    var day = 1;
    for (var i = 0; i <= 6; i++) {
      cal.push(['<tr>']);
      for (var j = 0; j < 7; j++) {
        if (i === 0) {
          cal[i].push('<td>' + details.weekDays[j] + '</td>');
        } else if (day > details.totalDays) {
          cal[i].push('<td>&nbsp;</td>');
        } else {
          if (i === 1 && j < start) {
            cal[i].push('<td>&nbsp;</td>');
          } else {
            cal[i].push('<td class="day">' + day++ + '</td>');
          }
        }
      }
      cal[i].push('</tr>');
    }
    cal = cal.reduce(function(a, b) {
      return a.concat(b);
    }, []).join('');
    $('table').append(cal);
    $('#month').text(details.months[d.getMonth()]);
    $('#year').text(d.getFullYear());
    $('td.day').mouseover(function() {
      $(this).addClass('hover');
    }).mouseout(function() {
      $(this).removeClass('hover');
    });
  }
  $('#left').click(function(e) {
    $('table').text('');
    if (currentDate.getMonth() === 0) {
      currentDate = new Date(currentDate.getFullYear() - 1, 11);
      generateCalendar(currentDate);
    } else {
      currentDate = new Date(currentDate.getFullYear(), currentDate.getMonth() - 1)
      generateCalendar(currentDate);
    }
	
	 e.preventDefault();
  });
  $('#right').click(function(e) {
    $('table').html('<tr></tr>');
    if (currentDate.getMonth() === 11) {
      currentDate = new Date(currentDate.getFullYear() + 1, 0);
      generateCalendar(currentDate);
    } else {
      currentDate = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1)
      generateCalendar(currentDate);
    }
	
	 e.preventDefault();
  
  });
  
  generateCalendar(currentDate);
});

Not sure if needed, but I provided all the code. For the sake of my question lets say I have database table called "current_events" and I want to show all events on the calendar in a different color.

[Barand]

generate the calendar.

make an AJAX request which returns a JSON encoded array of your events from the database.

process the array, adding the events and colours to the calendar

[cutielou22]

Okay. So I have done only ajax forms. So I am getting a little confused while researching . . . what I have added:

Underneath:

generateCalendar(currentDate);

I added (still in the same function as generateCalendar - not sure if correct either):

   $.ajax({
            type: "POST",
            dataType: 'json',
            url: "cal_events.php",
            async: false,
            data: {date: currentDate},
            contentType: "application/json; charset=utf-8",
            success: function (msg) {
                console.log(msg);                
            }
    });

 

Then I created the page cal_events.php with this code:

$share_with = user_shared($user);

$stmt2 = $mysqli->prepare("SELECT date, status FROM pto_tracker WHERE account = ? ORDER BY date");
$stmt2->bind_param('i', $share_with);
$stmt2->execute();
$stmt2->store_result();
$count = $stmt2->num_rows;
$stmt2->bind_result($date, $status);
$stmt2->fetch();
$stmt2->close();

$current_date = cleansafely($_POST['date']);

if (($count >= 1) && ($current_date == $date)){
	
		if ($status == "pending") {
			
			$background_color = "#f0cb11";
			$status_show = "<span class=\"label plain\">Pending</span>";
			
		}
		
		if ($status == "approved") {
			
			$background_color = "#4CAF50";
			$status_show = "<span class=\"label green\">Approved</span>";
			
		}
		
		if ($status == "denied") {
			
			$background_color = "#c62d1f";
			$status_show = "<span class=\"label red\">Denied</span>";
		
		}
	
	$array .= $date . $status_show;
	
}

	$out = array_values($array);
    json_encode($out);
	
	//echo json_encode($array, JSON_FORCE_OBJECT);

I am not sure how to go about actually finding the dates on the calendar to the ones in table.

[Barand]

I would create the event array with the days as the keys and the event descriptions as the values

[ "18":"Carol service", "25":"Christmas Day", "31":"New Years Eve" ]

and give each <td> a data-day attribute

<tr><td data-day='01'>1</td> ... <td data-day='31'>31</td></tr>

Now it's easy to match them.

[cutielou22]

So I added data-day, but that must up all dates in the calendar. What would data-day be linked to - I have nothing called that right now.

Is the ajax I have now correct then (and in the correct spot)? - is json_encode() correct also? I am completely new to this. Just using Google. 

 

[Barand]

I am a little confused by your ajax query. Your code produces a calendar for the current month and your ajax code sends the date.

All OK so far, but then your query searches by account only. I would have expected you to get the events just for that current month as those are the only ones you need.

Having got an array of this month's events (as in my example above) I would

for each element in array
    find <td> with data-day == element key
    change its background color
    add a tooltip title attribute with element value
end for each

That will highlight days with an event and display the event on hovering over the day. EG

But then, you haven't really explained how you envisage it working.

[cutielou22]

Well, I want to make it so a user can have there own events and they show up in the calendar. The calendar you can "flip" through the months to look at the months coming up. I want the days of events to be highlighted a certain color.

Since you say my " ajax code sends the date": then how do I grab that information and compare it with the calendar? That is what I don't get.

[Barand]

This seems to work

<?php
include 'db_inc.php';                               // use your own
$db = pdoConnect('testdb');                         // connection process

session_start();
$_SESSION['user'] = 1;                              // just for testing, You use your log in value



if (isset($_GET['ajax'])) {
    $events = [];
    $t = round($_GET['date'],0);
    $date = (new DateTime("@$t"))->format('Y-m-d');
    $res = $db->prepare("SELECT day(date) as day
                              , status
                         FROM pto_tracker
                         WHERE account = ?
                             AND EXTRACT(YEAR_MONTH FROM date) = EXTRACT(YEAR_MONTH FROM ?)     
                        ");
    $res->execute( [ $_SESSION['user'], $date ]);
    foreach ($res as $r) {
        $events[$r['day']] = $r['status']; 
    }
    exit(json_encode($events));
}
?>

<!DOCTYPE HTML>
<html>
<head>
<title>Test</title>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.0/jquery.min.js"></script>
<script type='text/javascript'>
$(document).ready(function() {
    var currentDate = new Date();
  
  $('#left').click(function(e) {
    $('table').text('');
    if (currentDate.getMonth() === 0) {
      currentDate = new Date(currentDate.getFullYear() - 1, 11);
      generateCalendar(currentDate);
    } else {
      currentDate = new Date(currentDate.getFullYear(), currentDate.getMonth() - 1)
      generateCalendar(currentDate);
    }
    
     e.preventDefault();
  });
  $('#right').click(function(e) {
    $('table').html('<tr></tr>');
    if (currentDate.getMonth() === 11) {
      currentDate = new Date(currentDate.getFullYear() + 1, 0);
      generateCalendar(currentDate);
    } else {
      currentDate = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1)
      generateCalendar(currentDate);
    }
    
     e.preventDefault();
    
  });
   generateCalendar(currentDate);
  
});

    function generateCalendar(d) 
    {
        
        Date.prototype.monthDays = function() {
          var d1 = new Date(d.getFullYear(), d.getMonth() + 1, 0);
          return d1.getDate();
        };
        
        var details = {
          // totalDays: monthDays(d.getMonth(), d.getFullYear()),
          totalDays: d.monthDays(),
          weekDays: ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'],
          months: ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'],
        };
        var start = new Date(d.getFullYear(), d.getMonth()).getDay();
        var cal = [];
        var day = 1;
        
        for (var i = 0; i <= 6; i++) {
          cal.push(['<tr>']);
          for (var j = 0; j < 7; j++) {
            if (i === 0) {
              cal[i].push('<td>' + details.weekDays[j] + '</td>');
            } else if (day > details.totalDays) {
              cal[i].push('<td>&nbsp;</td>');
            } else {
              if (i === 1 && j < start) {
                cal[i].push('<td>&nbsp;</td>');
              } else {
                cal[i].push('<td class="day" data-day="' + day + '">' + day++ + '</td>');
              }
            }
          }
          cal[i].push('</tr>');
        }
        cal = cal.reduce(function(a, b) {
          return a.concat(b);
        }, []).join('');
        $('#caltable').append(cal);
        $('#month').text(details.months[d.getMonth()]);
        $('#year').text(d.getFullYear());
        $('td.day').mouseover(function() {
        $(this).addClass('hover');
        }).mouseout(function() {
          $(this).removeClass('hover');
        });
        
        getEventsArray(d);
    }
  
    function getEventsArray(d)
    {   
        $.get(
            "",
            {"ajax":1, "date":d.getTime()/1000},
            function(resp) {
                console.log(resp)
                //var r = JSON.parse(resp)
                $.each(resp, function (k,v) {
                    var day = $(".day[data-day="+k+"]")
                    if (v == 'Approved') {
                        $(day).css("background-color", "#4CAF50")
                    }
                    else if (v == 'Pending') {
                        $(day).css("background-color", "#f0cb11")
                    }
                    else {
                        $(day).css("background-color", "#c62d1f")
                    }
                    $(day).attr("title", v)
                })
            },
            "JSON"
        )
        
    }
    
    function monthDays(month, year) 
    {
      var result = [];
      var days = new Date(year, month, 0).getDate();
      for (var i = 1; i <= days; i++) {
        result.push(i);
      }
      return result;
    }
        

</script>
<style>
.container {
  width: 80%;
  margin: 15px auto;
}
</style>
</head>
<body>
<div><h1>Calendar</h1></div>
<div>
    <h3><span id='month'></span> <span id='year'></span></h3>
</div>
<div>
     <table id="caltable"></table>
</div>

</body>
</html>

Not very pretty but gives ...

 

from this data ...

+----+---------+------------+----------+
| id | account | date       | status   |
+----+---------+------------+----------+
|  1 |       1 | 2020-11-05 | Approved |
|  2 |       2 | 2020-11-10 | Denied   |
|  3 |       1 | 2020-11-12 | Pending  |
|  4 |       1 | 2020-11-16 | Denied   |
|  5 |       2 | 2020-12-15 | Approved |
|  6 |       1 | 2020-12-08 | Approved | *
|  7 |       2 | 2020-12-12 | Pending  |
|  8 |       1 | 2020-12-15 | Denied   | *
|  9 |       1 | 2020-12-17 | Pending  | *
+----+---------+------------+----------+

 

Edited December 28, 2020 by Barand