Show title in calender PHP

Question

I have php to display the calender and another php to check database to see bookings, If there is a booking on certain day it displays "Booked". In database i have column for date of booking and title. How could i make it to display Title from the database instead of "Booked"?

This is to display calender.

<?php
$monthNames = Array("January", "February", "March", "April", "May",
"June", "July", "August", "September", "October", "November", "December");
?>

<?php
  if (!isset($_REQUEST["month"]))
   $_REQUEST["month"] = date("n");
  if (!isset($_REQUEST["year"]))
   $_REQUEST["year"] = date("Y");
?>

 <?php
   $cMonth = $_REQUEST["month"];
   $cYear = $_REQUEST["year"];
   $prev_year = $cYear;
   $next_year = $cYear;
   $prev_month = $cMonth-1;
   $next_month = $cMonth+1;
   if ($prev_month == 0 )
  {
        $prev_month = 12;
      $prev_year = $cYear - 1;
  }
  if ($next_month == 13 )
   {
     $next_month = 1;
     $next_year = $cYear + 1;
   }

     require("BookingsDB.php");
     $myBookingsDB = new BookingsDB();
     $bookings = $myBookingsDB->getMonthlyBookings($cMonth,$cYear);
    ?>

 <div align="left">
 <table width="400" border="5" align="left" id="calendar">
 <tr align="center">
 <td bgcolor="#999999" style="color:#FFFFFF"><table width="100%" border="0"
  cellspacing="0" cellpadding="0">
  <tr>
  <td width="50%" align="left"><a href="<?php echo $_SERVER["PHP_SELF"]
 . "?month=". $prev_month . "&year=" . $prev_year; ?>"
 style="color:#FFFFFF">Previous</a></td>
 <td width="50%" align="right"><a href="<?php echo $_SERVER["PHP_SELF"]
 . "?month=". $next_month . "&year=" . $next_year; ?>"
 style="color:#FFFFFF">Next</a></td>
 </tr>
 </table></td>
 </tr>
 <tr>
 <td align="center"><table width="100%" border="2" cellpadding="2"
 cellspacing="2">
 <tr align="center">
 <td colspan="7" bgcolor="#999999" style="color:#FFFFFF"><strong> <?php
 echo $monthNames[$cMonth-1].' '.$cYear; ?></strong></td>
 </tr>
 <tr>
 <td align="center" bgcolor="#999999" style="color:#FFFFFF">
 <strong>S</strong></td>
 <td align="center" bgcolor="#999999" style="color:#FFFFFF">
 <strong>M</strong></td>
 <td align="center" bgcolor="#999999" style="color:#FFFFFF">
 <strong>T</strong></td>
 <td align="center" bgcolor="#999999" style="color:#FFFFFF">
 <strong>W</strong></td>
 <td align="center" bgcolor="#999999" style="color:#FFFFFF">
 <strong>T</strong></td>
 <td align="center" bgcolor="#999999" style="color:#FFFFFF">
 <strong>F</strong></td>
 <td align="center" bgcolor="#999999" style="color:#FFFFFF">
 <strong>S</strong></td>
 </tr>

 <?php

 require("connection.php");
    $con=mysqli_connect("$mysql_host","$mysql_user","$mysql_password","$mysql_database");

 // Check connection
 if (mysqli_connect_errno())
 {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }

 $sql= "select activityEvent.activityTitle from activityEvent";
 $result = mysqli_query($con,$sql);

 $timestamp = mktime(0,0,0,$cMonth,1,$cYear);
 $maxday = date("t",$timestamp);
 $thismonth = getdate ($timestamp);
 $startday = $thismonth['wday'];
 $today = getdate();


 for ($i=0; $i<($maxday+$startday); $i++)
  {
    if(($i % 7) == 0 )
  {
        echo "<tr> ";
  }

 if($i < $startday)
  {
        echo "<td></td> ";
  }
 else
  {
     $day = $i - $startday + 1;
     $thisDate = new DateTime("$cYear-$cMonth-$day");
     $jsEvent[] = "document.getElementById('trigger" . $i . "').onclick = function() {showForm()};";
     echo "<td align='center' valign='middle' height='20px'><a href='#'  id='trigger" . $i . "'>". ($i - $startday + 1) . "</a>";
     foreach ($bookings as $bookingDate)
    {
        if ($thisDate==$bookingDate)
        {
            echo "Booked";
            while($row = mysqli_fetch_array($result))
               {

                echo $sql= "select activityEvent.activityTitle from activityEvent";

               }

        }
    }

    echo "</td>";

}
if(($i % 7) == 6 )
{
     echo "</tr> ";
}
}

mysqli_close($con);
 ?>

 <script type="text/javascript">
<?php foreach($jsEvent as $event)
   {
      echo $event;
   } 
 ?>
function showForm(){
  document.getElementById('timeslots').style.display="block";
 };
 </script>
</table></td>
 </tr>
</table>

This PHP checks booked dates in the database.

 <?php
 class BookingsDB
 {
   private $bookings = array();
   private $con;

   public function BookingsDB()
 {
     require("connection.php");
     $this->con = mysqli_connect($mysql_host,$mysql_user,$mysql_password,$mysql_database);



 }

   public function getMonthlyBookings($thisMonth,$thisYear)
 {
     $sql = "SELECT date,activityTitle FROM activityEvent WHERE YEAR(date) = $thisYear AND MONTH(date) = $thisMonth";
    $result = mysqli_query($this->con,$sql);
    while($row=mysqli_fetch_array($result))
        {
         $thisBooking = $row['date'];
         $thisBookingDateTime = new DateTime($thisBooking);
         array_push($this->bookings,$thisBookingDateTime);
        }
     return $this->bookings;
    }

  public function close()
   {
     mysqli_close($this->con);
   }
 }
 ?>

Answer 1

<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
    <head>
        <meta charset="UTF-8">
        <title></title>
        <style>
            .red
            {
                color: red
            }
            .green
            {
                background-color: aquamarine;
            }
            table
            {
                text-align: center;
            }
            td
            {
                height: 60px;
                width: 100px;
            }
            .opcity
            {
                background-color: rgba(0,0,0,.2);
            }
        </style>       
    </head>
    <body>
        <?php
        $Cur_day = date('d');
        $Cur_Month = date('m');
        $Cur_Year = date('Y');
        $op = '';
        if (isset($_REQUEST['month']) || isset($_REQUEST['year']) && isset($_REQUEST['stday']))
        {
            $Mo = $_REQUEST['month'];
            $Year = $_REQUEST['year'];
            $query_date = $Year . '-' . $Mo . '-21';
            if (isset($_REQUEST['opra']))
            {
                $str = $_REQUEST['opra'];
                if ($str == 'iny')
                {
                    $op = '+1 year';
                }
                if ($str == 'dey')
                {
                    $op = '-1 year';
                }
                if ($str == 'inm')
                {
                    $op = '+1 month';
                }
                if ($str == 'dem')
                {
                    $op = '-1 month';
                }
                if ($str == 'dem' || $str == 'inm' || $str == 'dey' || $str == 'iny')
                {
                    $query_date1 = date('Y-m-01', strtotime($op, strtotime($query_date)));
                    $lastDay = date('t', strtotime($op, strtotime($query_date)));
                    $year = date('Y', strtotime($op, strtotime($query_date)));
                    $month = date('n', strtotime($op, strtotime($query_date)));
                    $Month_Name = date('M', strtotime($op, strtotime($query_date)));
                    $pre_month = date('t', strtotime('-1 month', strtotime($year . '-' . $month . '-21')));
                }
                if ($str == 'No')
                {
                    $query_date1 = date('Y-m-01', strtotime($query_date));
                    $lastDay = date('t', strtotime($query_date));
                    $year = date('Y', strtotime($query_date));
                    $month = date('n', strtotime($query_date));
                    $Month_Name = date('M', strtotime($query_date));
                    $pre_month = date('t', strtotime('-1 month', strtotime($year . '-' . $month . '-21')));
                }
            }
            ?> 
            <input type="hidden" name="txt_year" id="txt_year1" value="<?php echo $year; ?>">
            <input type="hidden" name="txt_month1" id="txt_month1" value="<?php echo $month; ?>">
            <table border="1">    
                <tr>
                    <td><a href="javascript:deyear()" name="deyear"><<</a></td>
                    <td><a href="javascript:demonth()"><</a></td>
                    <td colspan="3"><?php echo $Month_Name . "-" . $year; ?></td>
                    <td><a href="javascript:incmonth()">></a></td>
                    <td><a href="javascript:incyear()">>></a></td>
                </tr>
                <tr>

                    <?php
                    echo '<tr>';
                    for ($i = 1; $i <= 7; $i++)
                    {
                        if ($_REQUEST['stday'] == 'Sun')
                        {
                            $j_val = 0;
                            echo "<th>" . date("l", gmmktime(0, 0, 0, 11, $i, 2015));
                            echo '</th>';
                        } else
                        {
                            $j_val = 1;
                            echo "<th>" . date("l", gmmktime(0, 0, 0, 6, $i, 2015));
                            echo '</th>';
                        }
                    }
                    echo '</tr>';
                    ?>
                </tr>
                <?php
                $Conn = new mysqli("localhost", "root", "", "demo");
                $firstDays = mktime(0, 0, 0, date($month), 1, date($year));
                $weekday = date('N', $firstDays);
                $i = 0;
                $j = 0;
                $date = 1;
                $Count = 0;
                $Count1 = 0;
                $next_mo = 1;

                function Holiday($day1, $month1, $year1)
                {
                    global $Conn;
                    $result = $Conn->query("SELECT * FROM holiday");
                    while ($row = mysqli_fetch_assoc($result))
                    {
                        if ($row['day'] == $day1 && $row['month'] == $month1 && $row['year'] == $year1)
                        {
                            return $row['title'] . "<br>";
                        }
                    }
                }
                for ($i = 0; $i < 6; $i++)
                {
                    echo '<tr>';
                    $pre_month = $pre_month - ($weekday - $j_val);
                    for ($j = $j_val; $j <= 6 + $j_val; $j++)
                    {
                        if ($Count < $weekday - $j_val && ($weekday != 7 || $_REQUEST['stday'] == 'Mon'))
                        {
                            echo "<td class='opcity'>" . ( ++$pre_month) . "</td>";
                            $Count++;
                        } else
                        {
                            if ($date <= $lastDay)
                            {
                                if ($Cur_day == $date && $Cur_Month == $month && $Cur_Year == $year)
                                {
                                    if (date("l", gmmktime(0, 0, 0, $month, $date, $year)) == 'Sunday')
                                    {
                                        echo '<td class = "green red">';
                                        echo Holiday($date, $month, $year);
                                    } else
                                    {
                                        echo '<td class = "green">';
                                        echo Holiday($date, $month, $year);
                                    }
                                } else
                                {
                                    if (date("l", gmmktime(0, 0, 0, $month, $date, $year)) == 'Sunday')
                                    {
                                        echo '<td class = "red">';
                                        echo Holiday($date, $month, $year);
                                    } else
                                    {
                                        echo '<td >';
                                        echo Holiday($date, $month, $year);
                                    }
                                }
                                echo $date;
                                echo '</td>';
                                $date++;
                                if ($date == $lastDay)
                                {
                                    $i = 7;
                                }
                            } else
                            {
                                echo "<td class='opcity'>" . $next_mo++ . "</td>";
                            }
                        }
                    }
                    echo '</tr>';
                }
            }
            ?>
        </table>
    </body>
</html>
//<Ajax_cal.php

<!DOCTYPE html>
<!--
To change this license header, choose License Headers in Project Properties.
To change this template file, choose Tools | Templates
and open the template in the editor.
-->
<html>
    <head>
        <meta charset="UTF-8">
        <title></title>
        <script src="JS/jquery-1.11.3.min.js" type="text/javascript"></script>
        <script>
            function Caleonload()
            {
                $('#month').val('<?php echo date("n"); ?>');
                $('#year').val('<?php echo date('Y'); ?>');
                Opretion('No');
            }
            function Cal(month)
            {
                $('#month').val(month);
                Opretion('No');
            }
            function Cal1(year)
            {
                $('#year').val(year);
                Opretion('No');
            }
            function Opretion(Op)
            {
                var st_day = $('input[type="radio"][class="rd_sun_mon"]:checked').val();
                var Mo = $('#month').val();
                var Year = $('#year').val();
                var Op1 = Op;
                $.post("AJAX/Ajax_Cal.php",
                        {
                            month: Mo,
                            year: Year,
                            opra: Op1,
                            stday: st_day
                        },
                function (data)
                {
                    $('#Show_Cal').html(data);
                    var txt_year = $('#txt_year1').val();
                    $("#year").html('');
                    for (var y = parseInt(txt_year) - 5; y < parseInt(txt_year) + 5; y++)
                    {
                        $("#year").append('<option value="' + y + '">' + y + '</option>');
                    }
                    $('#year').val(txt_year);
                    $('#month').val($('#txt_month1').val());
                }
                );
            }
            function incyear()
            {
                Opretion('iny');
            }
            function deyear()
            {
                Opretion('dey');
            }
            function incmonth()
            {
                Opretion('inm');
            }
            function demonth()
            {
                Opretion('dem');
            }
        </script>
    </head>
    <body onload="Caleonload()">       
        <select name="month" id="month" onchange="Cal(this.value)" size="1">            
            <option value="1">January</option>
            <option value="2">February</option>
            <option value="3">March</option>
            <option value="4">April</option>
            <option value="5">May</option>
            <option value="6">June</option>
            <option value="7">July</option>
            <option value="8">August</option>
            <option value="9">September</option>
            <option value="10">October</option>
            <option value="11">November</option>
            <option value="12">December</option>
        </select>    
        <select name="year" id="year" onchange="Cal1(this.value)" size="1">
            <option value="2013">2013</option>
            <option value="2014">2014</option>
            <option value="2015">2015</option>
            <option value="2016">2016</option>
        </select>    
        Starting Day :
        <input type="radio" name="rd_sun_mon" class="rd_sun_mon" value="Sun" checked="" onchange="Opretion('No')">Sunday
        <input type="radio" name="rd_sun_mon" class="rd_sun_mon" value="Mon" onchange="Opretion('No')">Monday
        <div id="Show_Cal"></div>
    </body>
</html>

DATA BASE PHP MY ADDMIN: CREATE TABLE IF NOT EXISTS holiday ( id int(5) NOT NULL AUTO_INCREMENT, day int(4) NOT NULL, month int(4) NOT NULL, year int(6) NOT NULL, title varchar(20) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Answer 2

In addition to @SimonGuichard's answer, I suggest that you return an associative array from the getMonthlyBookings function that contains both the date and the activityTitle. This way, you wouldn't have to select the title from the database again when it needs to be displayed.

public function getMonthlyBookings($thisMonth,$thisYear) {
    $sql = "SELECT date,activityTitle FROM activityEvent WHERE YEAR(date) = $thisYear AND MONTH(date) = $thisMonth";
    $result = mysqli_query($this->con,$sql);
    while($row=mysqli_fetch_array($result)) {
        $this->bookings[] = array('date'=>new DateTime($row['date']), 'activityTitle'=>$row['activityTitle']);
    }
    return $this->bookings;
}

Then in the script that displays the calendar, you should modify it this way:

$day = $i - $startday + 1;
$thisDate = new DateTime("$cYear-$cMonth-$day");
$jsEvent[] = "document.getElementById('trigger" . $i . "').onclick = function() {showForm()};";
echo "<td align='center' valign='middle' height='20px'><a href='#'  id='trigger" . $i . "'>". ($i - $startday + 1) . "</a>";

// $bookings is an associative array of the form array('0'=>array('date'=>'date_value', 'activityTitle'=>'activityTitle_value'))
foreach ($bookings as $key => $value) {

    if ($thisDate->format('Y-m-d') === $value['date']->format('Y-m-d')) {
        // this prints out the activity title
        echo $value['activityTitle'];
    }
}

Answer 3

I think your problem is that is that you need to put the format in order to compare two Datetime :

if ($thisDate->format('Y-m-d')==$bookingDate->format('Y-m-d'))
    {
        echo "Booked";
        ....
    }