Query help please

I have this function in my sub.php

function yyRecentTopics ()
{
   global $settings, $scripturl, $txt, $censored, $db_prefix, $num_recentTopics, $username;
   global $imagesdir, $img, $ID_MEMBER;
   global $recentBoard, $recents_chars;

   define('YaBBSE_ssi', 1);
   $recent_counter = 0;
   $count = 300;

   if ($username == 'Guest')
   {
      echo '<center><font color="#FF0000"><b>Attenzione</b></font><br><font size="1">Per utilizzare questa<br>funzionalità devi registrarti<br><br></font></center>';
      return;
   }

   //Limit recent topics to 24 hours. Uncomment the following line for more speed
   $timeLimit = 72 * 60 * 60;
   $request = mysql_query("
      SELECT m.ID_MSG
      FROM {$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}boards AS b,{$db_prefix}categories AS c
      WHERE m.ID_MSG = t.ID_LAST_MSG 
         AND b.ID_BOARD=t.ID_BOARD
         AND c.ID_CAT=b.ID_CAT
         AND (FIND_IN_SET('$settings[7]', c.memberGroups) != 0 OR c.memberGroups='' OR '$settings[7]' LIKE 'Administrator' OR '$settings[7]' LIKE 'Global Moderator')
         " . ($timeLimit > 0 ? 'AND m.posterTime>' . (time() - $timeLimit) : '') . "
         AND m.icon != 'moved.gif'
      ORDER BY posterTime DESC
      ") or database_error(__FILE__, __LINE__);
   //LIMIT 0, $count;

   $messages = array();
   while ($row = mysql_fetch_array($request))
      $messages[] = $row['ID_MSG'];
   
   if (count($messages))
   {
      $request = mysql_query("
         SELECT m.posterTime, m2.subject, m.ID_TOPIC, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, m.icon, t.numReplies, t.ID_BOARD, t.ID_FIRST_MSG, b.name AS bName, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead
         FROM {$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}boards as b, {$db_prefix}messages AS m2
            LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
            LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
            LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=t.ID_BOARD AND lmr.ID_MEMBER=$ID_MEMBER)
         WHERE m.ID_MSG IN (" . implode(',', $messages) . ")
            AND t.ID_TOPIC=m.ID_TOPIC
            AND b.ID_BOARD=t.ID_BOARD
            AND m2.ID_MSG=t.ID_FIRST_MSG
         ORDER BY m.posterTime DESC;") or database_error(__FILE__, __LINE__);
   
   if (mysql_num_rows($request) > 0)
      {
         while ($row = mysql_fetch_array($request)) 
         {
            $new = ($row['isRead'] >= $row['posterTime'] || $row['isMarkedRead'] >= $row['posterTime'] ? false : true);
            if (!$new || $username == 'Guest')
            {
               $new = '';
            }
            else
            {
               $new = '<a href="' . $scripturl . '?board=' . $row['ID_BOARD'] . ';action=display;threadid=' . $row['ID_TOPIC'] . ';start=new"><img border="0" src="graphics/new.gif" align="baseline" width="10" height="10"></a>';
            }
            if ($row['ID_MEMBER'] != -1)
            {
               $euser = urlencode($row['posterName']);
               $dummy = "<a href=\"$scripturl?action=viewprofile;user=$euser\">$row[posterDisplayName]</a>";
            }
            else
               $dummy = $row['posterName'];

            $sub = $row['subject'];
            $sub = ucfirst(strtolower($sub));

            $fullsub = $sub;
            if (strlen($sub) > $recents_chars)
                  $sub = substr($sub, 0, $recents_chars) . '...';

            $recent_board = '<a href="' . $scripturl . '?board=' . $row['ID_BOARD'] . '">' . $row['bName'] . '</a>';
            $recent_icon = '<img src="' . $imagesdir . '/' . $row[icon] . '.gif" alt="' . $row['icon'] . '" border="0" />';
            
            $recent_poster = $dummy;
            $recent_new = $new;
            $recent_time = timeformat($row['posterTime']);
            $recent_caticon = '<img border="0" src="caticons/' . $row['ID_BOARD'] . '.gif" align="baseline" width="10" height="10">';

            CensorTxt($recent_topic);

            $recent_topic = '<a href="' . $scripturl . '?board=' . $row['ID_BOARD'] . ';action=display;threadid=' . $row['ID_TOPIC'] . ';start=new" title=" ' . $fullsub . '">' . $sub . '</a>';
            echo '<font size="1">' . $recent_caticon . ' <b>' . $recent_topic . '</b></font><br>';
            $recent_counter++;

            if ($recent_counter > 50) break;
         }
      }
   }

   if ($recent_counter == 0)
   {
      echo '<center><font color="#FF0000"><b>Attenzione</b></font><br><font size="1">Hai letto tutti i posts degli<br>ultimi tre giorni<br><br></font></center>';
   }
}

How i can search for only unreaded post and sticky using querys ?

Simply I need two new functions but I am a beginner in mysql querys: yyRecentTopicsUnreaded and yyRecentTopicsSticky.

Now I scan for recent post and verify if is unreaded or stick, but obviusly isn't opitimized.

Thank's to all !

	Welcome, Guest. Please Login or Register. May 14, 2024, 01:55:43 PM

News: SMF is the next generation in forum software, almost completely re-written from the ground up, make sure you don't fall for cheap imitations that suffer from feature bloat!