Bernd
05-28-2009, 06:38 PM
I have hardly worked with queries and I am trying to adjust a mod to show the first attachment of the first post of a specific forum.
$query_attachments = $vbulletin->db->query_read("
SELECT thread.threadid, thread.firstpostid, thread.forumid, attachment.extension, attachment.postid, attachment.attachmentid
FROM " . TABLE_PREFIX . "attachment
INNER JOIN " . TABLE_PREFIX . "thread
ON thread.firstpostid=attachment.postid
WHERE (attachment.extension = 'jpg' OR attachment.extension = 'gif' OR attachment.extension = 'png') AND thread.forumid=6 AND dateline < " . TIME() ."
ORDER BY dateline DESC LIMIT 5");
The basic idea is the following.
-thread.threadid can be used to link to a specific post using a hyperlink.
-thread.firstpostid has to match attachment.postid. Hopefully, this will return the threads with attachments.
-using the where clause, i want to restrict the returns to forum ID 6 only.
I have only tried simple queries in the past (first use of inner join) and I am wondering if the general idea for this query is sound. Any help or hints is appreciated.
$query_attachments = $vbulletin->db->query_read("
SELECT thread.threadid, thread.firstpostid, thread.forumid, attachment.extension, attachment.postid, attachment.attachmentid
FROM " . TABLE_PREFIX . "attachment
INNER JOIN " . TABLE_PREFIX . "thread
ON thread.firstpostid=attachment.postid
WHERE (attachment.extension = 'jpg' OR attachment.extension = 'gif' OR attachment.extension = 'png') AND thread.forumid=6 AND dateline < " . TIME() ."
ORDER BY dateline DESC LIMIT 5");
The basic idea is the following.
-thread.threadid can be used to link to a specific post using a hyperlink.
-thread.firstpostid has to match attachment.postid. Hopefully, this will return the threads with attachments.
-using the where clause, i want to restrict the returns to forum ID 6 only.
I have only tried simple queries in the past (first use of inner join) and I am wondering if the general idea for this query is sound. Any help or hints is appreciated.