Showing Events in Calendar

[dmhall0]

I am trying to populate my even calendar with, well, events that are stored in a mysql table.

I came across the following code and modified the query to work in my case.  The code used mysql_query(), but I am using mysqli_query(), and for some reason it gets to that point and dies, but mysqli_error() doesn't even run; I just get "Error1:"

 

If I take out the query part and only build the calendar it work perfectly.

 

Does mysqli_query() not work inside a for loop?

 

This hit my database for every day of the month, is there a better way?

I also thought about running a query to pull all results then doing array_filter() inside the for loop for each day.  Does this even work?  I can't seem to figure it out.

 

.....
for($list_day = '1'; $list_day <= $days_in_month; $list_day++) {
    if($list_day == $today && $month == $nowmonth && $year == $nowyear) {
      $calendar.= '<td class="calendar-day-today">';
      } else {
        $calendar.= '<td class="calendar-day">';
        }
            /* add in the day number */
        $todaydate = $year.'-'.$month.'-'.$list_day;
        $calendar.= '<div class="day-number" onclick=window.location.href="viewday.php?name='.$_SESSION['username'].'&id='.$todaydate.'">'.$list_day.'</div>';    

       $y = 0;
       if(strlen($x) == '1') {$x = $y.$x; }
       
    
/** QUERY THE DATABASE FOR AN ENTRY FOR THIS DAY !!  IF MATCHES FOUND, PRINT THEM !! **/
  $todaydate = $year.'-'.$month.'-'.$list_day;
  $query = "SELECT * FROM health WHERE '$todaydate' >= event_start AND '$todaydate' <= event_end";
  $results = mysqli_query($dbc, $query) or die ('Error1: '.mysqli_error($dbc));
            if (mysqli_num_rows($results) > '0') {
              while($row = mysqli_fetch_array($results)){ 
              extract($row);
......

 

I am open to any other suggestions also.

Thanks for the help!

[requinix]

What do you get if you print out $query?

[dmhall0]

When I print my query I get:

SELECT * FROM health WHERE '2012-01-03' >= event_start AND '2012-01-03' <= event_end;

Which when I run it in MySQL it work perfectly.

[requinix]

Are you using mysqli_connect()? Are you sure $dbc is an open connection?

[PFMaBiSmAd]

This hit my database for every day of the month, is there a better way?

 

Yes, you should not have a select query inside of a loop and execute it 28-31 times.

 

The best way is to execute one query that gets the data for the current year/month all at once. You would then pre-proecess the data and put it into an array where the array index are the YYYY-MM-DD date values and the array data is your $row array for that date value.

 

Then, as you loop through the days in the calendar you are trying to display, you use your existing $todaydate value to check for any matching data and to retrieve the corresponding data from the array.

 

 

[dmhall0]

PFMaBiSmAd

 

Thanks for the info.  Could you help with the code structure for that?

 

Thanks again!

[PFMaBiSmAd]

Sure -

 

<?php
// fake data for testing purposes
$fake[] = array('id'=>1,'event_start'=>'2011-12-13','event_end'=>'2012-01-01'); // starts before the current year/month
$fake[] = array('id'=>2,'event_start'=>'2012-01-03','event_end'=>'2012-01-15'); // entirely within the current year/month
$fake[] = array('id'=>3,'event_start'=>'2012-01-25','event_end'=>'2012-02-03'); // ends after the current year/month
$fake[] = array('id'=>4,'event_start'=>'2011-12-29','event_end'=>'2012-03-01'); // spans the current year/month
$year = date('Y');
$month = date('m');
// end of test values

$first_day = date('Y-m-d',mktime(0,0,0,$month,1,$year)); // first day of current year/month
$last_day = date('Y-m-t',mktime(0,0,0,$month,1,$year)); // last day of current year/month

$query = "SELECT * FROM health WHERE '$year-$month' BETWEEN EXTRACT(YEAR_MONTH FROM event_start) AND EXTRACT(YEAR_MONTH FROM event_end) ORDER BY id";
//if(!$results = mysqli_query($dbc, $query)){
if(false){ // skip the above mysqli statement for testing purposes
// query failed
die("Query failed: $query<br />Error: " .mysqli_error($dbc));
} else {
// query worked
$data = array(); // array to hold the dates/data for each date (empty if no matching data)
//if(mysqli_num_rows($results)){
if(true){ // skip the above mysqli statement for testing purposes
	// at least one matching row
	//while($row = mysqli_fetch_assoc($results)){
	foreach($fake as $row){ // use fake data, in place of the above mysqli statement, for testing purposes
		// have event_start and event_end dates. need to expand for the days during the current $year/$month
		$date = $row['event_start']; // get start date for loop
		$end = $row['event_end']; // copy of the end date
		unset($row['event_start']); unset($row['event_end']); // remove these two unneeded pieces of data from the $row array
		while($date <= $end){ // loop over the days for this row
			if($date >= $first_day && $date <= $last_day){ // filter dates for event only within the current year/month
				$data[$date][] = $row; // save the entire $row array as an array of arrays for this date (allows for multiple events on any day)
			}
			$date = date('Y-m-d',strtotime("$date + 1 day")); // produce next date
		}
	}
}
}

//echo '<pre>',print_r($data,true),'</pre>'; // display the data array for testing...

// using the data -
$todaydate = $first_day; // first day of current year/month
while($todaydate <= $last_day){
if(isset($data[$todaydate])){
	// there is at least one event on this date
	echo "$todaydate-<br />"; // output date for testing...
	foreach($data[$todaydate] as $row){
		// $row is now the original fetched data for each event on the $todaydate date
		echo "{$row['id']}<br />"; // display id for testing...
	}
} else {
	// no even on this date
}
$todaydate = date('Y-m-d',strtotime("$todaydate + 1 day")); // produce next date
}

 

[dmhall0]

Wow.  That looks intense.  Since then I have been trying something else; but not sure if its breaking php "rules" as it doesn't seem to be working.

 

I already have the looping of the month's days working, which is my $todaydate variable.  So this portion - other than the query - will be inside that loop.

 

$query4 = "SELECT * FROM u_raceschedule WHERE username = '" . $_SESSION['username'] . "'";
  $allraces = mysqli_query($dbc, $query4) or die("Error 4: ".mysqli_error($dbc));
if (mysqli_num_rows($allraces) > 0) {
  	$races = array();
  while ($row = mysqli_fetch_array($allraces)) {
      array_push($races, $row['race_date']);
    }
    foreach ($races as $race) {
      if ($race == $todaydate) {
      	$calendar.= '<a href="races_edit.php?id=' . $row['raceid'] . '"><font size = 2pt>' . $row['race_name'] . '</font></a>';
      }
    }
  }

 

It doesn't return anything.  Ideas as to what is wrong?

 

Thanks!

[PFMaBiSmAd]

Do you have php's error_reporting set to E_ALL and display_errors set to ON in your master php.ini so that php will report and display all the errors it detects?

[dmhall0]

Err... no.

How do I do that?

Sorry, still new to all this coding stuff.

[PFMaBiSmAd]

Find your master php.ini file (it's usually in your main folder where php is installed), edit those two settings, save the file, and restart your web server to get the changes to take effect.

 

You can find the php.ini that php is using by making a php script with a phpinfo statement in it and browse to the url of the file you just made. The Loaded Configuration File value in the resulting output is the php.ini that php is using.

 

After making the changes, check using the phpinfo script that those two settings actually got changed.

[dmhall0]

So yes everything actually is turned on and set to show errors.

When I run the code I don't get any errors.

With all that code in there as is it the entire file loads properly, just doesn't populate the events.

I display the array of results from the query and that all looks correct.

My table structure is correct.  The "race_date" field is set to date and I am comparing it against the same format.

 

No clue.

 

Does my code look like it should work properly?

[PFMaBiSmAd]

Best guess is the format of the $todaydate value doesn't exactly match the data or your data contains some non-printing/white-space characters.

 

Your original posted code had a snippet of code that appears it was part of some logic that added a leading zero to the day, along with a printout of your query that showed the $todaydate value had a format that could be used in date comparisons. Since you haven't posted enough of your actual current code that reproduces the problem or examples of what your data is or what you saw in front of you when you displayed the data (your idea of what is a date or what is the same format might not be the same as everyone else's, including mysql/php), it's not possible to actually tell what might be going on with your code and your data.

[dmhall0]

Here is all my code.  Like I say.  It builds the calendar just fine and skips completely over the query.

When I print the query it is correct.

When I print_r() the array it looks correct also.

 

As to the potential date format issue... my link on the day number works perfectly that redirects to another page.

 

// Race schedule data
  $query4 = "SELECT * FROM u_raceschedule WHERE username = '" . $_SESSION['username'] . "'";
  $allraces = mysqli_query($dbc, $query4) or die("Error 4: ".mysqli_error($dbc));
/* draws a calendar */
function draw_calendar($month,$year){ 
    /* draw table */
    $calendar = '<table cellpadding="0" cellspacing="0" class="calendar" align="center">';

    /* table headings */
    $headings = array('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday', 'Totals');
    $calendar.= '<tr class="calendar-row"><td class="calendar-day-head">'.implode('</td><td class="calendar-day-head">',$headings).'</td></tr>';

    /* days and weeks vars now ... */
    $running_day = date('w',mktime(0,0,0,$month,1,$year)-1);
    
    $days_in_month = date('t',mktime(0,0,0,$month,1,$year));
    $days_in_this_week = 1;
    $day_counter = 0;
    $dates_array = array();
    $today = date('j');
    $nowmonth = date('n');
    $nowyear = date('Y');
  
    /* 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"> </td>';
        $days_in_this_week++;
    }
   $x = 01;
   
    /* keep going with days.... */
    for($list_day = '1'; $list_day <= $days_in_month; $list_day++) {
    if($list_day == $today && $month == $nowmonth && $year == $nowyear) {
      $calendar.= '<td class="calendar-day-today">';
      } else {
        $calendar.= '<td class="calendar-day">';
        }
            /* add in the day number */
        $todaydate = $year.'-'.$month.'-'.$list_day;
        $calendar.= '<div class="day-number"><a href="diary_day.php?id='.$todaydate.'">'.$list_day.'</div>';    

/** QUERY THE DATABASE FOR AN ENTRY FOR THIS DAY !!  IF MATCHES FOUND, PRINT THEM !! **/
  // enter races
  if (mysqli_num_rows($allraces) > 0) {
  	$races = array();
  while ($row = mysqli_fetch_array($allraces)) {
      array_push($races, $row['race_date']);
    }
    // Insert empty question rows into the u_profile table, one per question
    foreach ($races as $race) {
      if ($race == $todaydate) {
      	$calendar.= '<strong>Race</strong>';
      }
    }
  }
//Above here is end of event code

        $calendar.= str_repeat('<p> </p>',2);    
        $calendar.= '</td>';
        if($running_day == 6)  {
            $calendar.= '</tr>';
            if(($day_counter+1) != $days_in_month) {
                $calendar.= '<tr class="calendar-row">';
            }
            $running_day = -1;
            $days_in_this_week = 0;
        }
        $days_in_this_week++; $running_day++; $day_counter++;
              } /* for loop ending */
    /* finish the rest of the days in the week */
    if($days_in_this_week < {
        for($x = 1; $x <= (8 - $days_in_this_week); $x++){
            $calendar.= '<td class="calendar-day-np"> </td>';
        }
    }

    /* final row */
    $calendar.= '</tr>';

    /* end the table */
    $calendar.= '</table>';
    
    /* all done, return result */
    return $calendar;
}

 

Thanks everyone for the help!  Really appreciate it.

[kamal213]

Theres a slight problem with the code below @PFMaBiSmAd it doesnt quiet work for

$fake[] = array('id'=>3,'event_start'=>'2012-01-25','event_end'=>'2012-02-03'); // ends after the current year/month

It gets to the 2012-01-31 and stops there which is a problem as it's suppose to print the 2012-02-01, 2012-02-02, 2012-02-03.

 

How can we get it to print events which start in one month and end in another.

 

Thanks

 

Sure -

 

<?php
// fake data for testing purposes
$fake[] = array('id'=>1,'event_start'=>'2011-12-13','event_end'=>'2012-01-01'); // starts before the current year/month
$fake[] = array('id'=>2,'event_start'=>'2012-01-03','event_end'=>'2012-01-15'); // entirely within the current year/month
$fake[] = array('id'=>3,'event_start'=>'2012-01-25','event_end'=>'2012-02-03'); // ends after the current year/month
$fake[] = array('id'=>4,'event_start'=>'2011-12-29','event_end'=>'2012-03-01'); // spans the current year/month
$year = date('Y');
$month = date('m');
// end of test values

$first_day = date('Y-m-d',mktime(0,0,0,$month,1,$year)); // first day of current year/month
$last_day = date('Y-m-t',mktime(0,0,0,$month,1,$year)); // last day of current year/month

$query = "SELECT * FROM health WHERE '$year-$month' BETWEEN EXTRACT(YEAR_MONTH FROM event_start) AND EXTRACT(YEAR_MONTH FROM event_end) ORDER BY id";
//if(!$results = mysqli_query($dbc, $query)){
if(false){ // skip the above mysqli statement for testing purposes
// query failed
die("Query failed: $query<br />Error: " .mysqli_error($dbc));
} else {
// query worked
$data = array(); // array to hold the dates/data for each date (empty if no matching data)
//if(mysqli_num_rows($results)){
if(true){ // skip the above mysqli statement for testing purposes
	// at least one matching row
	//while($row = mysqli_fetch_assoc($results)){
	foreach($fake as $row){ // use fake data, in place of the above mysqli statement, for testing purposes
		// have event_start and event_end dates. need to expand for the days during the current $year/$month
		$date = $row['event_start']; // get start date for loop
		$end = $row['event_end']; // copy of the end date
		unset($row['event_start']); unset($row['event_end']); // remove these two unneeded pieces of data from the $row array
		while($date <= $end){ // loop over the days for this row
			if($date >= $first_day && $date <= $last_day){ // filter dates for event only within the current year/month
				$data[$date][] = $row; // save the entire $row array as an array of arrays for this date (allows for multiple events on any day)
			}
			$date = date('Y-m-d',strtotime("$date + 1 day")); // produce next date
		}
	}
}
}

//echo '<pre>',print_r($data,true),'</pre>'; // display the data array for testing...

// using the data -
$todaydate = $first_day; // first day of current year/month
while($todaydate <= $last_day){
if(isset($data[$todaydate])){
	// there is at least one event on this date
	echo "$todaydate-<br />"; // output date for testing...
	foreach($data[$todaydate] as $row){
		// $row is now the original fetched data for each event on the $todaydate date
		echo "{$row['id']}<br />"; // display id for testing...
	}
} else {
	// no even on this date
}
$todaydate = date('Y-m-d',strtotime("$todaydate + 1 day")); // produce next date
}

[PFMaBiSmAd]

The point of the code is that it displays information for one month at a time. By setting the $month and $year variables, you can get and display information for any calendar month that you want. The sample code simply sets them to the current month in the current year. That's why the $month and $year variables are being set in code that is between the // fake data for testing purposes and // end of test values comments.

 

If you want to display more than one month calendar on a page at one time, either modify the code to accept a starting month and year and an ending month and year or put the code into a function and call it for each month/year that you want to display on one page.

[kamal213]

Done that and it now works! Thanks for that! Amazing peace of coding by the way took a while but i finally got it