Hello, I want to get topic's id and topic's subject. I know the table, messages, I know what I need is the subject's of the minimum ID_MSG for every ID_TOPIC, but I don't know how make this.
I only need the SQL syntax, nothing more.
If you didn't understand, I will explain better. I'm writing a little mod, and I need to get all topic's URL and subject, to make a link for them, like a forum.
Thank you
SELECT
t.ID_TOPIC, t.numReplies, t.locked, t.numViews, t.isSticky, t.ID_POLL,
" . ($user_info['is_guest'] ? "0" : "IFNULL(lt.logTime, IFNULL(lmr.logTime, 0))") . " AS isRead,
t.ID_LAST_MSG, ml.posterTime AS lastPosterTime, ml.modifiedTime AS lastModifiedTime,
ml.subject AS lastSubject, ml.icon AS lastIcon, ml.posterName AS lastMemberName,
ml.ID_MEMBER AS lastID_MEMBER, IFNULL(meml.realName, ml.posterName) AS lastDisplayName,
t.ID_FIRST_MSG, mf.posterTime AS firstPosterTime, mf.modifiedTime AS firstModifiedTime,
mf.subject AS firstSubject, mf.icon AS firstIcon, mf.posterName AS firstMemberName,
mf.ID_MEMBER AS firstID_MEMBER, IFNULL(memf.realName, mf.posterName) AS firstDisplayName,
LEFT(ml.body, 384) AS lastBody, LEFT(mf.body, 384) AS firstBody, ml.smileysEnabled AS lastSmileys,
mf.smileysEnabled AS firstSmileys
FROM {$db_prefix}topics AS t, {$db_prefix}messages AS ml, {$db_prefix}messages AS mf
LEFT JOIN {$db_prefix}members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN {$db_prefix}members AS memf ON (memf.ID_MEMBER = mf.ID_MEMBER)" . ($user_info['is_guest'] ? '' : "
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 = $board AND lmr.ID_MEMBER = $ID_MEMBER)"). "
WHERE t.ID_BOARD = $board
AND ml.ID_MSG = t.ID_LAST_MSG
AND mf.ID_MSG = t.ID_FIRST_MSG
ORDER BY
" . (!empty($modSettings['enableStickyTopics']) ? 't.isSticky' . ($fake_ascending ? '' : ' DESC') . ',
' : '') . $_REQUEST['sort'] . ($ascending ? '' : ' DESC') . "
LIMIT $start, $maxindex
Do you mean this?
MessageIndex.php:411-601 will help you
OK, I didn't remember that ;) A little difficult query :D
Too data and too dificult for me :( I need something easier, because my script will run using only SSI.php, and most and I have not most variables. I need something easier...
I managed at last to get it. It wasn't so dificult as I thouguht. Here is:
$request = db_query("
SELECT ID_MSG, {$db_prefix}messages.ID_TOPIC, {$db_prefix}messages.ID_BOARD,
{$db_prefix}messages.subject, {$db_prefix}messages.posterName, {$db_prefix}topics.ID_FIRST_MSG
FROM {$db_prefix}messages, {$db_prefix}topics
WHERE ID_MSG=ID_FIRST_MSG
ORDER BY ID_TOPIC ASC ", __FILE__, __LINE__);