getting events from mysql database and placing on calendar

Question

I have a php calendar that displays exactly how I want. Now I need to get events stored in a mysql database and place them on the calendar. I thought the code I have did that, but I can't figure out what is wrong.

The calendar code:

    <?php

    $db = mysql_connect("***", "***", "***") or die("Could not connect.");
    $link = mysql_select_db("***",$db);


  /* draws a calendar */
  function draw_calendar($month,$year,$events = array()){

/***********************************************CONTROLS************************************************/
    /* date settings */
    $month = (int) ($_GET['month'] ? $_GET['month'] : date('m'));
    $year = (int)  ($_GET['year'] ? $_GET['year'] : date('Y'));

    if($month < 10){
            $month = str_pad($month, 2, '0', STR_PAD_LEFT);
        }
    /* select month control */
    $select_month_control = '<select name="month" id="month">';
    for($x = 1; $x <= 12; $x++) {
      $select_month_control.= '<option value="'.$x.'"'.($x != $month ? '' : ' selected="selected"').'>'.date('F',mktime(0,0,0,$x,1,$year)).'</option>';
    }
    $select_month_control.= '</select>';
    /* select year control */
    $year_range = 7;
    $select_year_control = '<select name="year" id="year">';
    for($x = ($year-floor($year_range/2)); $x <= ($year+floor($year_range/2)); $x++) {
      $select_year_control.= '<option value="'.$x.'"'.($x != $year ? '' : ' selected="selected"').'>'.$x.'</option>';
    }
    $select_year_control.= '</select>';
    /* "next month" control */
    $next_month_link = '<a href="?month='.($month != 12 ? $month + 1 : 1).'&year='.($month != 12 ? $year : $year + 1).'" class="control">Next Month >></a>';
    /* "previous month" control */
    $previous_month_link = '<a href="?month='.($month != 1 ? $month - 1 : 12).'&year='.($month != 1 ? $year : $year - 1).'" class="control"><<   Previous Month</a>';
    /* bringing the controls together */
    $controls = '<form method="get">'.$select_month_control.$select_year_control.'&nbsp;<input type="submit" name="submit" value="Go" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$previous_month_link.'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$next_month_link.' </form>';
    echo $controls;

/*******************************************START CALENDAR*******************************************/
    /* draw table */
    $calendar = '<table cellpadding="0" cellspacing="0" class="calendar">';
    /* table headings */
    $headings = array('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday');
    $calendar.= '<tr class="calendar-row"><td class="calendar-day-head">'.implode('</td><td class="calendar-day-head">',$headings).'</td></tr>';
    /* days and weeks vars */
    $running_day = date('w',mktime(0,0,0,$month,1,$year));
    $days_in_month = date('t',mktime(0,0,0,$month,1,$year));
    $days_in_this_week = 1;
    $day_counter = 0;
    $dates_array = array();
    /* row for week one */
    $calendar.= '<tr class="calendar-row">';
    /* print "blank" days until the first of the current week */
    for($x = 0; $x < $running_day; $x++):
        $calendar.= '<td class="calendar-day-np">&nbsp;</td>';
        $days_in_this_week++;
    endfor;
    /* keep going with days */
    for($list_day = 1; $list_day <= $days_in_month; $list_day++):
        if($list_day == date("d") && $month == date("n") && $year == date("Y")) {
            $calendar.= '<td class="calendar-day current">';
        }
        else {
            $calendar.= '<td class="calendar-day">';
        }
        /* add in the day number */
        if($list_day < 10){
            $list_day = str_pad($list_day, 2, '0', STR_PAD_LEFT);
        }
        $calendar.= '<div class="day-number">'.$list_day.'</div>';

        /******************************************CHECK FOR 

EVENTS*******************************/
            $event_day = $year.'-'.$month.'-'.$list_day;
            if(isset($events[$event_day])) {
                foreach($events[$event_day] as $event) {
                  $calendar.= '<div class="event">'.$event['title'].'</div>';
                }
            }
            else {
                $calendar.= str_repeat('<p>&nbsp;</p>',2);
            }
            /*****************************************************************************************/

            $calendar.= '</div></td>';  
            if($running_day == 6):
                $calendar.= '</tr>';
                if(($day_counter+1) != $days_in_month):
                    $calendar.= '<tr class="calendar-row">';
                endif;
                $running_day = -1;
                $days_in_this_week = 0;
            endif;
            $days_in_this_week++; $running_day++; $day_counter++;
        endfor;
        /* finish the rest of the days in the week */
        if($days_in_this_week < 8):
            for($x = 1; $x <= (8 - $days_in_this_week); $x++):
                $calendar.= '<td class="calendar-day-np">&nbsp;</td>';
            endfor;
        endif;
        /* final row */
        $calendar.= '</tr>';
        /* end the table */
        $calendar.= '</table>';
        /** DEBUG **/
      $calendar = str_replace('</td>','</td>'."\n",$calendar);
      $calendar = str_replace('</tr>','</tr>'."\n",$calendar);
        /* all done, return result */
        return $calendar;
      }

    /* get all events for the given month */
    $events = array();
    $query = "SELECT Name, DATE_FORMAT(Date,'%Y-%m-%d') AS Date FROM events WHERE Date LIKE '$year-$month'";
    $result = mysql_query($query) or die('cannot get results!');
    while($row = mysql_fetch_assoc($result)) {
      $events[$row['Date']][] = $row;
    }

    echo '<h2 style="float:left; padding-right:30px;">'.date('F',mktime(0,0,0,$month,1,$year)).' '.$year.'</h2>';
    echo '<div style="float:left;">'.$controls.'</div>';
    echo '<div style="clear:both;"></div>';
    echo draw_calendar($month,$year,$events);
    echo '<br /><br />';

?>

My database table is called events and the fields are id, Name, Description, Date, Time, and Place.

What do I need to fix in order to get the events to display on the calendar?

Edit: Here is the echoed query: SELECT Name, DATE_FORMAT(Date,'%Y-%m-%d') AS Date FROM events WHERE Date LIKE '2013-02'

What are the displayed errors ? Do you have displaying errors activated ? — Lucian Depold, Feb 09 '13 at 21:16
There doesn't seem to be an error. The result of querying the database seems to just be empty. — PBwebD, Feb 09 '13 at 21:19
(Trust me, you will see this all over SO over the next year or so...) [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Amelia, Feb 09 '13 at 22:05

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

Try modifying your select statement, using the following query:

SELECT Name, DATE_FORMAT(Date,'%Y-%m-%d') AS Date 
   FROM events 
 WHERE Date LIKE '$year-$month%'

Additionaly, you are using deprecated MySQL php functions. Take a look at the following links:

Why shouldn't I use mysql_* functions in PHP?

What could i use instead of mysql_ functions?

Prepated Statements

Prepared Statements with MySQLi

edited May 23 '17 at 10:32

Community

1
1

answered Feb 09 '13 at 21:57

Mateus Schneiders

4,853
3
20
40

Is this something I can change easily? Are there parallel functions I can replace them with or would I have to rewrite the code completely? – PBwebD Feb 09 '13 at 22:15
Yes, you could use MySQLi functions instead, i edited the answer with a link to the Php Manual. – Mateus Schneiders Feb 09 '13 at 22:25
Since I started in mysql_, I'll get it working in that and then transfer everything over. For now, the variables for $year and $month do seem to be populated. If I echo the select statement, the variables are replaced with the correct values. I placed a dummy event that should be showing up, so I have to figure out why it's not returning the value that I gave it. – PBwebD Feb 09 '13 at 22:29
Could you update the question posting the echoed select statement and the resulted set of records. – Mateus Schneiders Feb 09 '13 at 22:36
I added the echoed select statement, but the array didn't give anything. I used while ($row = mysql_fetch_assoc($result)) { echo $row["id"]; echo $row["Name"]; echo $row["Date"]; } to check what was in the array. – PBwebD Feb 09 '13 at 22:57
Can you run the query on the database? I think the `WHERE Like '2013-02'` statement will not work, try `WHERE Like '2013-02%'` – Mateus Schneiders Feb 09 '13 at 22:58
The test event date is 2013-02-20 – PBwebD Feb 09 '13 at 22:59
ok excellent. The event was echoed. Now I just have to get it to show up in the correct place in the calendar. That's another battle I suppose. At least I got the two to communicate to each other. – PBwebD Feb 09 '13 at 23:00

score 0 · Answer 2 · answered Mar 14 '13 at 19:43

Please add below code before your query. Also look into the query for changes. Its work...Tested

$year=$_REQUEST['year'];
$month=$_REQUEST['month'];
$month = sprintf("%02s", $month);
//echo $year;
$query = "SELECT Name, DATE_FORMAT(Date,'%Y-%m-%d') AS Date FROM events WHERE Date LIKE '$year-$month-%'";

getting events from mysql database and placing on calendar

2 Answers2