Why does SSI_recentEvents list items out of chronological order?

Started by kimncris, November 15, 2005, 02:51:51 AM

Previous topic - Next topic

kimncris

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

Quote// 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

kimncris

well I figured out one clue

if I echo at this point

Quote// 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-19


so it's somewhere in the subsequent code that things are being re-ordered....hmmm....

somwhere in here:
Quote// 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'],'&nbsp;'.  $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'],'&nbsp;'.  $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 International


note that 1/7 and 2/6 are now out of order....




weird.



kimncris

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
Quote
/***************************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,'&nbsp;'.  $row['month'],'/'. $row['day'],'/'. $shortyear, '<br>'.  $row['title']. '<br><br></a>' ;

                                                           }

                                                           else

                                                          {

                                                             echo '
            <a href="' . $hyperlink . '" title="' . $row['details'] . '">' . $dayword,'&nbsp;'.  $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

kimncris

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

Advertisement: