I've been experimenting all night with trying different parameters of ORDER BYs and GROUP BYs to get the database to return my events in the right order, but for some reason anytime I add events in next year it's a crapshoot as to what order they display in....
Why does THIS code from SSI.php not work? (I only changed the DESC to ASC because I want them in order from today forward.) A similar thing happens if I just change my calendar to diplay upcoming events farther away than 30 days.
anyway here's the code- the same as everyone else's minus the DESC after ORDER BY
Lainaa// Show the most recent events.
function ssi_recentEvents($max_events = 7, $output_method = 'echo')
{
global $db_prefix, $user_info, $scripturl, $modSettings, $txt, $sc;
// Find all events which are happening in the near future that the member can see.
$request = db_query("
SELECT
cal.ID_EVENT, DAYOFMONTH(cal.eventDate) AS day, cal.title, cal.ID_MEMBER, cal.ID_TOPIC,
cal.ID_BOARD, t.ID_FIRST_MSG
FROM {$db_prefix}calendar AS cal, {$db_prefix}boards AS b, {$db_prefix}topics AS t
WHERE cal.eventDate >= '" . strftime('%Y-%m-%d', forum_time(false)) . "'
AND cal.ID_TOPIC = t.ID_TOPIC
AND cal.ID_BOARD = b.ID_BOARD
AND $user_info[query_see_board]
ORDER BY cal.eventDate ASC
LIMIT $max_events", __FILE__, __LINE__);
that's the basics. Even this one does not diplay the dates in the correct order in my SSIexamples file.
if you want, you can my "live" results at www.scgarrison.net/index.php but I am using a slightly modified version that also spits out the dates before the event title.
It's the same basic query as above, but with the dates you can see- everything in the months of November and December are fine but in next year, 2/4/06 is listed before 1/7/06.
anyone know why?
-cris
well I figured out one clue
if I echo at this point
Lainaa// Find all events which are happening in the near future that the member can see.
$request = db_query("
SELECT
cal.ID_EVENT, DAYOFMONTH(cal.eventDate) AS day, cal.eventDate, year(cal.eventDate) AS year, MONTH(cal.eventDate) AS month, DAYOFWEEK(cal.eventDate) AS weekday, cal.title, cal.details, cal.ID_MEMBER, cal.ID_TOPIC,
cal.ID_BOARD, t.ID_FIRST_MSG
FROM {$db_prefix}calendar AS cal, {$db_prefix}boards AS b, {$db_prefix}topics AS t
WHERE cal.eventDate >= '" . strftime('%Y-%m-%d', forum_time(false)) . "'
AND cal.ID_TOPIC = t.ID_TOPIC
AND cal.ID_BOARD = b.ID_BOARD
/*AND $user_info[query_see_board]*/
ORDER BY cal.eventdate ASC
LIMIT $max_events", __FILE__, __LINE__);
$return = array();
$duplicates = array();
while ($row = mysql_fetch_assoc($request))
{
if ($row['weekday'] == "1") {$dayword=Sunday;}
elseif ($row['weekday'] == "2") {$dayword=Monday;}
elseif ($row['weekday'] == "3") {$dayword=Tuesday;}
elseif ($row['weekday'] == "4") {$dayword=Wednesday;}
elseif ($row['weekday'] == "5") {$dayword=Thursday;}
elseif ($row['weekday'] == "6") {$dayword=Friday;}
else {$dayword=Saturday;}
if ($row['year'] > 2009) $shortyear = (($row['year'])-2000);
else $shortyear="0";
if ($row['year'] < 2010) $lastdigit = (($row['year'])-2000);
echo 'test' ,$row['eventDate'],'<br>';
the dates are in the correct order
displays:
test2005-11-20
test2005-12-03
test2005-12-04
test2006-01-07
test2006-02-04
test2006-02-15
test2006-07-19so it's somewhere in the subsequent code that things are being re-ordered....hmmm....
somwhere in here:
Lainaa// Check if we've already come by an event linked to this same topic with the same title... and don't display it if we have.
if (!empty($duplicates[$row['title'] . $row['ID_TOPIC']]))
continue;
// Censor the title.
censorText($row['title']);
$return[$row['day']][] = array(
'id' => $row['ID_EVENT'],
'title' => $row['title'],
'popup' => $row['details'],
'day' =>$row['day'],
'weekday' =>$dayword,
'month' =>$row['month'],
'year' =>$shortyear,
'lastdigit' =>$lastdigit,
/*'can_edit' => allowedTo('calendar_edit_any') || ($row['ID_MEMBER'] == $ID_MEMBER && allowedTo('calendar_edit_own')),*/
'modify_href' => $scripturl . '?action=post;msg=' . $row['ID_FIRST_MSG'] . ';topic=' . $row['ID_TOPIC'] . '.0;calendar;eventid=' . $row['ID_EVENT'] . ';sesc=' . $sc,
'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0',
'link' => '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0">' . $row['title'] . '</a>',
'is_last' => false
);
// Let's not show this one again, huh?
$duplicates[$row['title'] . $row['ID_TOPIC']] = true;
}
mysql_free_result($request);
foreach ($return as $mday => $array)
$return[$mday][count($array) - 1]['is_last'] = true;
if ($output_method != 'echo' || empty($return))
return $return;
// Well the output method is echo.
/* echo '
<span style="color: #' . $modSettings['cal_eventcolor'] . ';">' . $txt['calendar4'] . '</span> ';*/
foreach ($return as $mday => $array)
foreach ($array as $event)
{
//only display the items as links if not a guest.
if ($context['user']['is_guest'])
{
echo '
<a href="index.php" title="' . $event['popup'] . '">' . $event['weekday'],' '. $event['month'],'/'. $event['day'],'/'. $event['year'], $event['lastdigit'],'<br>'. $event['title']. '<br><br></a>' /*. (!$event['is_last'] ? ', ' : '')*/;
}
else
{
echo '
<a href="' . $event['href'] . '" title="' . $event['popup'] . '">' . $event['weekday'],' '. $event['month'],'/'. $event['day'],'/'. $event['year'], $event['lastdigit'],'<br>'. $event['title']. '<br><br></a>' /*. (!$event['is_last'] ? ', ' : '')*/;
}
}
}
displays:
Sunday 11/20/05
Doo Dah Parade?
Saturday 12/3/05
Toys for Tots - Tustin Toys R Us with OCSWS
Sunday 12/4/05
FIDM Dressing a Galaxy Fan Day
Saturday 2/4/06
Star Wars Mardi Gras Charity Ball and Pageant
Saturday 1/7/06
READ/OC Main Place Mall
Wednesday 2/15/06
Comic-Con Hotel Reservatons Begin!
Wednesday 7/19/06
Comic-Con Internationalnote that 1/7 and 2/6 are now out of order....
weird.
well, I'd really appreciate some feedback now :-[
here's why:
I got it to work the way I want. the thing is, in order to do this I had to completely modify the function removing A TON of code.
I just want to make sure that I didn't remove anything vital. I
think I only removed superfluous arrays and variables that handled things I didn't necessarily want or need, but you never know.
please take a moment to look if you have time :)
this much simplified function
Lainaa
/***************************dbEvents query**********************************/
// Show the next 7 upcoming events with dates in ASCENDING order even to guests.
function ssi_dbEvents($max_events = 7, $output_method = 'echo')
{
global $context,$db_prefix, $user_info, $scripturl, $modSettings, $txt, $sc;
// Find all events which are happening in the near future that the member can see.
$request = db_query("
SELECT
cal.ID_EVENT, DAYOFMONTH(cal.eventDate) AS day, cal.eventDate, year(cal.eventDate) AS year, MONTH(cal.eventDate) AS month, DAYOFWEEK(cal.eventDate) AS weekday, cal.title, cal.details, cal.ID_MEMBER, cal.ID_TOPIC,
cal.ID_BOARD, t.ID_FIRST_MSG
FROM {$db_prefix}calendar AS cal, {$db_prefix}boards AS b, {$db_prefix}topics AS t
WHERE cal.eventDate >= '" . strftime('%Y-%m-%d', forum_time(false)) . "'
AND cal.ID_TOPIC = t.ID_TOPIC
AND cal.ID_BOARD = b.ID_BOARD
GROUP BY cal.title /*use this to show only 1st date of multi day events*/
ORDER BY cal.eventdate ASC
LIMIT $max_events", __FILE__, __LINE__);
$return = array();
while ($row = mysql_fetch_assoc($request))
{
//change the day number info from the database into a word
if ($row['weekday'] == "1") {$dayword=Sunday;}
elseif ($row['weekday'] == "2") {$dayword=Monday;}
elseif ($row['weekday'] == "3") {$dayword=Tuesday;}
elseif ($row['weekday'] == "4") {$dayword=Wednesday;}
elseif ($row['weekday'] == "5") {$dayword=Thursday;}
elseif ($row['weekday'] == "6") {$dayword=Friday;}
else {$dayword=Saturday;}
//set the year to only display the last 2 digits
$shortyear = substr($row['year'], -2);
//set the link info for echo
$hyperlink = $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0';
//Censor the title.
censorText($row['title']);
//dont display links to guests
if ($context['user']['is_guest'])
{
echo '
<a href=" " title="' . $row['details'] . '">' . $dayword,' '. $row['month'],'/'. $row['day'],'/'. $shortyear, '<br>'. $row['title']. '<br><br></a>' ;
}
else
{
echo '
<a href="' . $hyperlink . '" title="' . $row['details'] . '">' . $dayword,' '. $row['month'],'/'. $row['day'],'/'. $shortyear, '<br>'. $row['title']. '<br><br></a>' ;
}
}
mysql_free_result($request);
if ($output_method != 'echo' || empty($return))
return $return;
}
/******************************end dbevents******************************************/
displays this (at leaset currently- it will change with today's date or when new events are added obviously)
Sunday 11/20/05
Doo Dah Parade?
Saturday 12/3/05
Toys for Tots - Tustin Toys R Us with OCSWS
Sunday 12/4/05
FIDM Dressing a Galaxy Fan Day
Saturday 1/7/06
READ/OC Main Place Mall
Saturday 2/4/06
Star Wars Mardi Gras Charity Ball and Pageant
Wednesday 2/15/06
Comic-Con Hotel Reservatons Begin!
Wednesday 7/19/06
Comic-Con International Preview Night!thanks for looking.
-cris
so maybe I posted this in the wrong forum? Or should I take the sound of crickets as a sign that everything looks OK?
Despite the fact that the function I re-wrote works exactly as I want it to now, I really butchered the original code so If I left anything important out, I don't wat to find out the hard way! :o
LMK
thanks
-cris