PHP Calendar with data from MYSQL

jacko_162 · December 22, 2018

Has anyone done this before?

i have the following VERY basic looking calendar to display the current month and <strong> on the current date;

<?php
/* Set the default timezone */
date_default_timezone_set("Europe/London");

/* Set the date */
$date = strtotime(date("Y-m-d"));

$day = date('d', $date);
$month = date('m', $date);
$year = date('Y', $date);
$firstDay = mktime(0,0,0,$month, 1, $year);
$title = strftime('%B', $firstDay);
$dayOfWeek = date('D', $firstDay);
$daysInMonth = cal_days_in_month(0, $month, $year);
/* Get the name of the week days */
$timestamp = strtotime('next Sunday');
$weekDays = array();
for ($i = 0; $i < 7; $i++) {
  $weekDays[] = strftime('%a', $timestamp);
  $timestamp = strtotime('+1 day', $timestamp);
}
$blank = date('w', strtotime("{$year}-{$month}-01"));
?>
<table class='cal-table cal-table-bordered' border=1 style='table-layout: fixed;'>
  <tr>
    <th colspan='7' class='cal-text-center'> <?php echo $title ?> <?php echo $year ?> </th>
  </tr>
  <tr>
    <?php foreach($weekDays as $key => $weekDay) : ?>
      <td class='cal-text-center"'><?php echo $weekDay ?></td>
    <?php endforeach ?>
  </tr>
  <tr class='cal-row'>
    <?php for($i = 0; $i < $blank; $i++): ?>
      <td></td>
    <?php endfor; ?>
    <?php for($i = 1; $i <= $daysInMonth; $i++): ?>
      <?php if($day == $i): ?>
	  <?php //database code here ?>
        <td><strong><?php echo $i ?></strong></td>
      <?php else: ?>
        <td><?php echo $i ?></td>
      <?php endif; ?>
      <?php if(($i + $blank) % 7 == 0): ?>
        </tr><tr class='cal-row'>
      <?php endif; ?>
    <?php endfor; ?>
    <?php for($i = 0; ($i + $blank + $daysInMonth) % 7 != 0; $i++): ?>
      <td></td>
    <?php endfor; ?>
  </tr>
</table>

how can i get data into this calendar from my 'events' table, to perhaps highlight the date of the event and show a hidden <DIV> when rollover. i have columns: 'eventStart' and 'eventEnd' and 'eventName' in the 'events' table.

or shall i look for something else? this has to be super simple with no backend or multiple files being used etc..

thanks

mac_gyver · December 22, 2018

before the start of the display code, you would query for and retrieve any event data matching the year and month being displayed. when you retrieve the data, you would store it as a sub-array of rows (there can be multiple events on any date) in a main array variable with the date (YYYY-MM-DD) value as the the main array's index. in your calendar display code, you would build the date (YYYY-MM-DD) value for the current day being displayed and test if the main array of retrieved data has an index for that date (you can just use isset().) if so, you would loop over the sub-array of data to reference each row of event information for that date.

Edited December 22, 2018 by mac_gyver

jacko_162 · December 22, 2018

so its not completely impossible to use this code, thats good news. will start getting data now and report back if i have any problems.

jacko_162 · December 23, 2018

ok i have made my query and added it to an array and get the following array;

Array
(
    [0] => Array
        (
            [id] => 1
            [dateStart] => 2018-12-26
            [dateEnd] => 2018-12-26
            [name] => Test Event 1
        )

    [1] => Array
        (
            [id] => 2
            [dateStart] => 2018-12-27
            [dateEnd] => 2018-12-27
            [name] => Test Event 2
        )

    [2] => Array
        (
            [id] => 3
            [dateStart] => 2018-12-28
            [dateEnd] => 2018-12-28
            [name] => Test Event 3
        )

    [3] => Array
        (
            [id] => 4
            [dateStart] => 2018-12-29
            [dateEnd] => 2018-12-29
            [name] => Test Event 4
        )

)

but im struggling to figure out where and how in the calendar code to find the current day being displayed and to add in the event(s) matching that day in the array.

here is my code so far;

<?php
if ($result = $con->query("SELECT * FROM eventsTest")) {
	$array = array();
    while($row=mysqli_fetch_assoc($result)) {
        $array[]=$row;
    }
print "<pre>";
print_r($array);
print "</pre>"; // DEBUG--- show all array data
} 

/* Set the default timezone */
date_default_timezone_set("Europe/London");

/* Set the date */
$date = strtotime(date("Y-m-d"));

$day = date('d', $date);
$month = date('m', $date);
$year = date('Y', $date);
$firstDay = mktime(0,0,0,$month, 1, $year);
$title = strftime('%B', $firstDay);
$dayOfWeek = date('D', $firstDay);
$daysInMonth = cal_days_in_month(0, $month, $year);
/* Get the name of the week days */
$timestamp = strtotime('next Sunday');
$weekDays = array();
for ($i = 0; $i < 7; $i++) {
  $weekDays[] = strftime('%a', $timestamp);
  $timestamp = strtotime('+1 day', $timestamp);
}
$blank = date('w', strtotime("{$year}-{$month}-01"));
?>
<table class='table table-bordered' border=1 style="table-layout: fixed;">
  <tr>
    <th colspan="7" class="text-center"> <?php echo $title ?> <?php echo $year ?> </th>
  </tr>
  <tr>
    <?php foreach($weekDays as $key => $weekDay) : ?>
      <td class="text-center"><?php echo $weekDay ?></td>
    <?php endforeach ?>
  </tr>
  <tr>
    <?php for($i = 0; $i < $blank; $i++): ?>
      <td></td>
    <?php endfor; ?>
    <?php for($i = 1; $i <= $daysInMonth; $i++): ?>
      <?php if($day == $i): ?>
        <td class='cal-today'><strong><?php echo $i ?></strong></td>
      <?php else: ?>
        <td><?php echo $i ?></td>
      <?php endif; ?>
      <?php if(($i + $blank) % 7 == 0): ?>
        </tr><tr>
      <?php endif; ?>
    <?php endfor; ?>
    <?php for($i = 0; ($i + $blank + $daysInMonth) % 7 != 0; $i++): ?>
      <td></td>
    <?php endfor; ?>
  </tr>
</table>

Barand · December 23, 2018

14 hours ago, mac_gyver said:

with the date (YYYY-MM-DD) value as the the main array's index

How about

Array
(
    [2018-12-26] => Array
        (
            [0] => Array
                (
                    [dateEnd] => 2018-12-27
                    [name] => Test Event 1
                )

        )

    [2018-12-27] => Array
        (
            [0] => Array
                (
                    [dateEnd] => 2018-12-27
                    [name] => Test Event 2
                )

        )

    [2018-12-28] => Array
        (
            [0] => Array
                (
                    [dateEnd] => 2108-12-28
                    [name] => Test Event 3
                )

            [1] => Array
                (
                    [dateEnd] => 2018-12-30
                    [name] => Test Event 4
                )

        )

)

ginerjm · December 23, 2018

You could use JS on each day of the calendar. A click event could then use that day as the index of the events array and display a static div in the middle of the calendar display that your php created but did not show. Add that day's event (or events) to the div and wait for a click to close it.

jacko_162 · December 24, 2018

15 hours ago, Barand said:

How about


Array
(
    [2018-12-26] => Array
        (
            [0] => Array
                (
                    [dateEnd] => 2018-12-27
                    [name] => Test Event 1
                )

        )

    [2018-12-27] => Array
        (
            [0] => Array
                (
                    [dateEnd] => 2018-12-27
                    [name] => Test Event 2
                )

        )

    [2018-12-28] => Array
        (
            [0] => Array
                (
                    [dateEnd] => 2108-12-28
                    [name] => Test Event 3
                )

            [1] => Array
                (
                    [dateEnd] => 2018-12-30
                    [name] => Test Event 4
                )

        )

)

ok now i have the array as you said above using;

<?php
if ($result = $con->query("SELECT dateStart, dateEnd, name FROM eventsTest")) {
	$array = array();
while($row=mysqli_fetch_assoc($result)) {
    $array[$row['dateStart']][] = $row;
}
// DEBUG--- show all array data
print "<pre>";
print_r($array);
print "</pre>";
}
?>

its now grouped my array by the dateStart Index,

now im completely lost as to figuring out how to go about building out what day to show them on the calendar code.

ginerjm · December 24, 2018

The question is - do you want to do this using JS or do you want to go back to the server and build the div to display the info from the array?

I'm not the best at using json, but I believe that you want to take your sql data and build that array and then convert it to json and pass that object to your web page when you display the calendar. Then, using my previous suggestion, use some JS code to retrieve the correct piece of the json object to display the data in that static div that you suddenly make visible.

I'm sure someone else can help you with actual code if you (and they) decide that this is the Best way to go....

Barand · December 24, 2018

$days_events = $array[ $date_selected ];

foreach ( $days_events as $event ) {

    // display event

}

jacko_162 · December 24, 2018

1 hour ago, Barand said:
$days_events = $array[ $date_selected ];

foreach ( $days_events as $event ) {

    // display event

}
where an i getting $date_selected variable from?

mac_gyver · December 24, 2018

someone already posted the steps you would perform -

On 12/22/2018 at 10:56 AM, mac_gyver said:

in your calendar display code, you would build the date (YYYY-MM-DD) value for the current day being displayed and test if the main array of retrieved data has an index for that date (you can just use isset().) if so, you would loop over the sub-array of data to reference each row of event information for that date.

Barand · December 24, 2018

1 hour ago, jacko_162 said:

where an i getting $date_selected variable from?

That would be up to you. It depends on how you design the page.

Strider64 · December 29, 2018

Here's might be another way in achieving the same thing?

I personally just CSS highlight the day (you could also make the font bolder or what have you) by doing something like the following.

    protected function currentDays() {
        //$this->result = $this->checkForEntry($this->current->format("Y-m-d"));
        if ($this->now->format("F j, Y") === $this->current->format("F j, Y")) {
            $this->calendar .= "\t\t" . '<td class="background">';
        } else {
            $this->calendar .= "\t\t" . '<td>';
        }
        $this->calendar .= '<a href="' . $this->pageName . '?date=' . $this->current->format('Y-m-d') . '">' . $this->current->format("j") . '</a>';
        $this->calendar .= "</td>\n";
    }

To understand it better think of $this-> as $

Here's the full Class that I have been tinkering with over the last 3 years or so.

<?php

namespace Library\Calendar;

use DateTime;
use Library\Database\Database;
//use Library\Calendar\Location;

class Calendar {

    protected $date = \NULL;
    protected $page = 0;
    public $output = \NULL;
    protected $username = \NULL;
    protected $user_id = \NULL;
    protected $query = \NULL;
    protected $stmt = \NULL;
    protected $urlDate = \NULL;
    protected $sendDate = \NULL;
    protected $prev = \NULL;
    public $current = \NULL;
    protected $next = \NULL;
    protected $month = \NULL;
    protected $day = \NULL;
    protected $year = \NULL;
    protected $days = \NULL;
    protected $currentDay = \NULL;
    protected $highlightToday = \NULL;
    protected $highlightHoliday = \NULL;
    protected $isHoliday = \NULL;
    protected $prevMonth = \NULL;
    protected $nextMonth = \NULL;
    public $selectedMonth = \NULL;
    public $n = \NULL;
    public $result = \NULL;
    protected $calendar = \NULL; // The HTML Calender:
    protected $alphaDay = [0 => "Sun", 1 => "Mon", 2 => "Tue", 3 => "Wed", 4 => "Thu", 5 => "Fri", 6 => "Sat"];
    protected $smallDays = [0 => "S", 1 => "M", 2 => "T", 3 => "W", 4 => "T", 5 => "F", 6 => "S"];
    protected $imporantDates = [];
    protected $myPage = \NULL;
    protected $now = \NULL;
    protected $monthlyChange = \NULL;
    protected $pageName = "index";

    /* Constructor to create the calendar */

    public function __construct($date = null) {
        $this->selectedMonth = new \DateTime($date, new \DateTimeZone("America/Detroit"));
        $this->current = new \DateTime($date, new \DateTimeZone("America/Detroit"));
        $this->current->modify("first day of this month");
        $this->n = $this->current->format("n"); // Current Month as a number (1-12):
    }

    public function fileLocation() {
        return $this->returnLocation();
    }

    public function set_user_id($user_id = -1) {
        $this->user_id = $user_id;
    }

    public function checkIsAValidDate($myDateString) {
        return (bool) strtotime($myDateString);
    }

    public function changeMonth($setDate) {
        self::__construct($setDate, $size);
    }

    public function phpDate() {
        $setDate = filter_input(INPUT_GET, 'location', FILTER_SANITIZE_FULL_SPECIAL_CHARS);
        $valid = $this->checkIsAValidDate($setDate);
        if (isset($setDate) && strlen($setDate) === 10 && $valid) {
            self::__construct($setDate);
        }
    }

    public function returnDate() {
        return $this->selectedMonth;
    }

    public function getHolidayNames() {
        return $this->isHoliday->checkForHoliday($this->selectedMonth->format('Y-m-j'));
    }

    /*
     * Not Currently Being Used:
     */
    protected function checkForEntry($calDate) {
        $blog = "blog.php";
        $db = Database::getInstance();
        $pdo = $db->getConnection();
        $this->username = isset($_SESSION['user']) ? $_SESSION['user']->username : \NULL;

        $this->query = 'SELECT 1 FROM cms WHERE page_name=:page_name AND DATE_FORMAT(date_added, "%Y-%m-%d")=:date_added AND user_id=:user_id';

        $this->stmt = $pdo->prepare($this->query);

        $this->stmt->execute([':page_name' => $blog, ':date_added' => $calDate, ':user_id' => $this->user_id]);

        $this->result = $this->stmt->fetch();

        /* If result is true there is data in day, otherwise no data */
        if ($this->result) {
            return \TRUE;
        } else {
            return \FALSE;
        }
    }

    /* Highlight Today's Date on Calendar */

    protected function currentDays() {
        //$this->result = $this->checkForEntry($this->current->format("Y-m-d"));
        if ($this->now->format("F j, Y") === $this->current->format("F j, Y")) {
            $this->calendar .= "\t\t" . '<td class="background">';
        } else {
            $this->calendar .= "\t\t" . '<td>';
        }
        $this->calendar .= '<a href="' . $this->pageName . '?date=' . $this->current->format('Y-m-d') . '">' . $this->current->format("j") . '</a>';
        $this->calendar .= "</td>\n";
    }

    /* Draw Days (make Table Cells) on Calendar */

    protected function drawDays() {

        $this->now = new \DateTime("Now", new \DateTimeZone("America/Detroit"));
        $this->calendar .= "\t<tr>\n";
        $x = 1;
        while ($x <= 7) {
            if ($this->selectedMonth->format('n') === $this->current->format('n')) {
                $this->currentDays();
            } else {
                $this->calendar .= "\t\t" . '<td class="fade">' . $this->current->format("j") . '</td>' . "\n";
            }

            $this->current->modify("+1 day");
            $x += 1;
        }
        $this->calendar .= "\t</tr>\n";
    }

    protected function heading() {
        $this->monthlyChange = new DateTime($this->current->format("F j, Y"));
        $this->monthlyChange->modify("-1 month");
        $this->prev = $this->monthlyChange->format("Y-m-d");
        $this->monthlyChange->modify("+2 month");
        $this->next = $this->monthlyChange->format("Y-m-d");
        /* Create heading for the calendar */
        $this->calendar .= "\t<tr>\n";
        $this->calendar .= "\t\t" . '<th class="tableHeading" colspan="7">';
        $this->calendar .= '<a data-pos="prev" class="prev-left" href="' . $this->pageName . '-' . $this->prev . '"><</a>';
        $this->calendar .= $this->current->format('F Y');
        $this->calendar .= '<a data-pos="next" class="next-right" href="' . $this->pageName . '-' . $this->next . '">></a>';
        $this->calendar .= "</th>\n";
        $this->calendar .= "\t</tr>\n";
    }

    protected function display($pageName) {
        $this->pageName = $pageName;
        /* Create the table */
        $this->calendar .= '<table class="calendar">' . "\n";
        $this->heading($this->pageName);
        /* Create days of the week heading (columns) */
        $this->calendar .= "\t<tr>\n";
        for ($x = 0; $x <= 6; $x++) {
            $this->calendar .= "\t\t<th>" . $this->smallDays[$x] . "</th>\n";
        }
        $this->calendar .= "\t</tr>\n";

        /* Generate Actual Days of the Week */
        $this->current->modify("last sun of previous month");

        /*
         * Output 7 rows (49 days) guarantees an even calendar.
         */
        $num = 1;
        while ($num < 7) {
            $this->drawDays();
            $num += 1;
        }

        /* Close the HTML tags */
        return $this->calendar .= "</table>\n";
    }

    public function generateCalendar(string $pageName = "index") {
        return $this->display($pageName);
    }

}

Except for the top part where I declare my variables (some of them not being even used) the code I think is pretty good and is free to use, modify or just look at. I know it's written in OOP, but it might be of some value?

You can see this can in action at https://www.pepster.com/

Sign In

PHP Calendar with data from MYSQL

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information