Event Show Problem

[SkyRanger]

I am having a problem showing events on my calendar.

 

foreach ($days as $day) {
       echo "<td class=\"calendar_days\">$day</td>\n";
}


for ($count=0; $count < (6*7); $count++) {
       $dayArray = getdate($start);
       if (($count % 7) == 0) {
               if ($dayArray["mon"] != $month) {
                       break;
               } else {
                       echo "</tr><tr>\n";
               }
       }
       if ($count < $firstDayArray["wday"] || $dayArray["mon"] != $month) {
               echo "<td class=\"caltd\"> </td>\n";
       } else {
               $chkEvent_sql = "SELECT event_title FROM calendar_events WHERE month(event_start) = '".$month."' AND dayofmonth(event_start) = '".$dayArray["mday"]."' AND year(event_start) = '".$year."' AND client='$loggedin' or adminpost='All' ORDER BY event_start";
   $chkEvent_res = $mysqli->query($chkEvent_sql) or die($mysqli->error.__LINE__);


if ($chkEvent_res->num_rows > 0) {
$event_title = "<br/>";
while ($ev = mysqli_fetch_array($chkEvent_res)) {
$event_title .= "".stripslashes($ev["event_title"])."<br/>";
}
mysqli_free_result($chkEvent_res);
} else {
$event_title = "";
}

   $currentm = date("n");
   $currentd = date("j");
   $currenty = date("Y");

   $bgc = '';  // default no bgcolor
if ($dayArray['mday'] == $currentd) {
 $bgc = ' bgcolor="#d0e4eb"'; 
} elseif (!empty($event_title)) {
 // Set event color
 $bgc = ' bgcolor="#b47a42"';
}


if ($month == $currentm && $firstDayArray['year'] == $currenty) {
   echo '<td' . $bgc . "  height=\"74\" valign=\"top\" class=\"caltd\"><center><a class=\"calnum\" href=\"javascript:eventWindow('event.php?m=".$month."&d=".$dayArray["mday"]."&y=$year');\">".$dayArray["mday"]."</a><br/>".$event_title."</center></td>\n";
} else {
   echo "<td height=\"74\" valign=\"top\" class=\"caltd\"><center><a class=\"calnum\" href=\"javascript:eventWindow('event.php?m=".$month."&d=".$dayArray["mday"]."&y=$year');\">".$dayArray["mday"]."</a><br/>".$event_title."</center></td>\n";		  
	     }

               unset($event_title);

               $start += ADAY;
       }
}

 

The problem I am having is if the $loggedin user posts an event on his/her calendar it works no problem. When I post an event for all under adminpost it shows the event everyday in the calendar.

[SkyRanger]

Can anybody see the problem I am having as to why an event is showing on all days with the query that I am running?

[mweldan]

how about run the query of $chkEvent_sql manually on phpmyadmin or from cli.

 

don't really understand. would appreciate if you can clarify.

 

why have to check if client is logged in

 

and adminpost field is for what?

[SkyRanger]

Each client has there own calendar. The $loggedin is just the username that is pulled from the session to put against the query to pull there events out of the database. The adminpost is any event the admin posts to the clients that will show on the users calendar.

 

 

For some reason it is the "or adminpost='All'" causing all the problems and not sure why because when I remove this the clients events show with no problems but when I put the or in the All posts show on every single day.

Edited February 20, 2013 by SkyRanger

[Mikey]

Split it into two queries. Remove the OR, and have a separate query for your adminpost stuff and then do things based on that.

[SkyRanger]

Sounds good to me, but not sure how I would go about that without screwing up the whole calendar with the 2 different $event_title outputs due to that the $event_title is the main array to create the table for events:

 

if ($chkEvent_res->num_rows > 0) {
$event_title = "<br/>";
while ($ev = mysqli_fetch_array($chkEvent_res)) {
$event_title .= "".stripslashes($ev["event_title"])."<br/>";
}
mysqli_free_result($chkEvent_res);
} else {
$event_title = "";
}

       $currentm = date("n");
       $currentd = date("j");
       $currenty = date("Y");

       $bgc = '';  // default no bgcolor
if ($dayArray['mday'] == $currentd) {
 $bgc = ' bgcolor="#d0e4eb"'; 
} elseif (!empty($event_title)) {
 // Set event color
 $bgc = ' bgcolor="#b47a42"';
}
if ($month == $currentm && $firstDayArray['year'] == $currenty) {
       echo '<td' . $bgc . "  height=\"74\" valign=\"top\" class=\"caltd\"><center><a class=\"calnum\" href=\"javascript:eventWindow('event.php?m=".$month."&d=".$dayArray["mday"]."&y=$year');\">".$dayArray["mday"]."</a><br/>".$event_title."</center></td>\n";
} else {
       echo "<td height=\"74\" valign=\"top\" class=\"caltd\"><center><a class=\"calnum\" href=\"javascript:eventWindow('event.php?m=".$month."&d=".$dayArray["mday"]."&y=$year');\">".$dayArray["mday"]."</a><br/>".$event_title."</center></td>\n";            
                        }

[Mikey]

Well if you ran two queries, you can then check if your adminpost query returns results.

 

If it does, perhaps you could do something like array_merge it into your main array and that way it wont appear on every day?

[SkyRanger]

ok, thanks Mikey, will give that a try

[DavidAM]

SELECT event_title 
FROM calendar_events 
WHERE month(event_start) = '".$month."' 
AND dayofmonth(event_start) = '".$dayArray["mday"]."' 
AND year(event_start) = '".$year."' 
AND client='$loggedin' 
or adminpost='All' 
ORDER BY event_start";

 

You need to group the two conditions of the OR clause using parenthesis. Your problem is that the query above will return all events where the date matches AND the client matches; OR all events with adminpost = 'All'.

 

Try this

SELECT event_title 
FROM calendar_events 
WHERE month(event_start) = '".$month."' 
AND dayofmonth(event_start) = '".$dayArray["mday"]."' 
AND year(event_start) = '".$year."' 
AND ( client='$loggedin' 
   or adminpost='All' ) 
ORDER BY event_start";

 

Note: If event_start is a DATE (or DATETIME) column (and it SHOULD be), and you have an index on it (and you SHOULD), that query will never use the index. You would be better served to use:

event_start = '" . $year . '-' . $month . '-' . $dayArray["mday"] . "'"

if it is a DATE column. If it is DATETIME, you need to use a little different condition.

 

Note (2): You should avoid running queries in a loop. If you are doing a full month (or even week) calendar, then select all of the data for the time period, load it in an array and THEN build the output. Running queries in a loop uses a lot of unnecessary resources communicating with the database and will take longer than it should.

[SkyRanger]

Holy Crap it worked, thank you DavidAM never thought of putting the () around them, have been fighting with this all day and that is the one thing I never tried. Yeah and the event_start sounds like a better way of doing that. Thank you.

 

And on Note 2, That is a good idea, thank you. Will rewite the code for that.