Query: show img attachment of every first post.

[Bernd]

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.

[Bernd]

nobody?

[kalisekj]

Good Luck Bernd , I am also trying to show first attached image as my post icon and getting no replies or ideas even though this would truly expand VB to multiple new markets if it can do this.

Markets that could benefit from this:
Adult Sites
Fan Based Sites
Merchants selling Products
Movie Web Sites
Photo or Image Sites

[Lynne]

Don't you want to put something in there to grab it just for a specific threadid? AT what point are you running this query? And what is the expected result here?

[Bernd]

Originally Posted by Lynne

Don't you want to put something in there to grab it just for a specific threadid? AT what point are you running this query? And what is the expected result here?

Sorry for the late reply, had tons of work to do.
Basically, what I am trying to create is a cmps module that displays the first image of every thread in a specific forum (e.g. latest 5 results) . The query will be running on every frontpage request, as it will be displaying the latest resources available in a specific forum (basically threads with binaries and image attachments).

Once fully working, I will post the modifications in the mod thread so that everybody will benefit from it.