Customizing SMF > SMF Coding Discussion

smf database question: messages

(1/1)

PS4DEV:
ok, with this code i can output all messages,  which includes topics , and it´s replies:

--- Code: ---/***** MESSAGE *****/

$posts_result = $smcFunc['db_query']('', '
   SELECT m.poster_name, m.poster_time, m.id_msg, m.id_member, m.subject, m.body, m.id_topic, b.name, b.id_board, u.avatar, g.online_color,' . ($user_info['is_guest'] ? '1 AS is_read, 0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) >= m.id_msg_modified AS is_read,
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') . '
   FROM {db_prefix}messages AS m
   LEFT JOIN {db_prefix}boards AS b ON (m.id_board = b.id_board)
   LEFT JOIN {db_prefix}members AS u ON (m.id_member = u.id_member)
   LEFT JOIN {db_prefix}membergroups AS g ON (g.id_group = CASE WHEN u.id_group = 0 THEN u.id_post_group ELSE u.id_group END)
   ' . (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = m.id_topic AND lt.id_member = ' . $user_info['id'] . ')
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board AND lmr.id_member = ' . $user_info['id'] . ')' : '') . '   
   WHERE {query_see_board} AND m.approved=1
   ORDER BY m.id_msg DESC
   ');
$posts = array();
while ($row_posts = $smcFunc['db_fetch_assoc']($posts_result))
{

  global $memberContext;
  loadMemberData($row_posts['id_member']);
  loadMemberContext($row_posts['id_member']);

   $posts[] = array(
      'topic_id' => $row_posts['id_topic'],
  'msg_id' => $row_posts['id_msg'],
                  'board' => $row_posts['name'],
  'id' => $row_posts['id_member'],
  'username' => '<a style="font-weight: bold; text-decoration: none;">' . $row_posts['poster_name'] . '</a>',
  'subject' => '' . $row_posts['subject'] . '',
  'body' => '' .$row_posts['body'] .'',  
  'avatar' => $row_posts['avatar'] == '' ? $memberContext[$row_posts['id_member']]['avatar']['href'] : (stristr($row_posts['avatar'], 'http://') ? $row_posts['avatar'] : $modSettings['avatar_url'] . '/' . $row_posts['avatar']),
  'board' => $row_posts['name'],
  'idboard' => $row_posts['id_board'],  
  'time' => timeformat($row_posts['poster_time'], '%d %b %y'),
  'new' => !empty($row_posts['is_read'])
   );
}
$smcFunc['db_free_result']($posts_result);

--- End code ---

My Question is:  how can i check , which of those messages was a topic , and which was a reply to this topic ?

I looked at the database and there doesnt seem something like "replies" .  there i can only find "topics" and "messages"

Xenocidius:
SMF separates messages and topics so that they can easily be split, merged and linked to.

The topics table contains a field called 'id_first_msg' which contains the ID of the first message in a topic, the 'topic' message. So to find out whether a message is the first in a topic, you can use SELECT 'id_first_msg' FROM {db_prefix}topics WHERE id_topic={id_topic field in messages table} and see whether it is equal to the message id that you are checking, or something of the sort.

PS4DEV:
ah ok, i had to try a bit until it worked, thank you very much (:

Navigation

[0] Message Index