SQL query question - returning threads by forum id

[ne_one]

I have a WordPress plugin that retrieves the most recent vB threads. However, it's not quite working as expected and the developer has moved on.

The plugin has a parameter to limit the scope by passing a list of forum ids. When the parameter isn't specified, all forums are considered and data is returned. When populated with known forum ids, it doesn't return any data.

Here's the query:

SELECT t.threadid, t.lastposterid, t.lastposter, FROM_UNIXTIME(t.lastpost) as lastposttime, t.replycount, t.views, t.title, u.avatarid, u.avatarrevision, p.pagetext, NOT ISNULL(av.userid) as hascustomavatar FROM vb4_thread AS t INNER JOIN vb4_forum AS f ON f.forumid = t.forumid INNER JOIN vb4_user AS u ON u.userid = t.lastposterid INNER JOIN vb4_post AS p ON p.postid = t.lastpostid LEFT JOIN vb4_customavatar AS av ON av.userid = u.userid WHERE t.replycount != 0 AND f.forumid IN ( 1,2,3 ) AND visible="1" ORDER BY t.lastpost DESC LIMIT 10


The SQL syntax appears to be ok. Is the forum qualifier correct?

Thanks for your help!

[Replicant]

There is a typo in your query. visible should be t.visible
SELECT t.threadid, t.lastposterid, t.lastposter, FROM_UNIXTIME(t.lastpost) as lastposttime, t.replycount, t.views, t.title, u.avatarid, u.avatarrevision, p.pagetext, NOT ISNULL(av.userid) as hascustomavatar FROM vb4_thread AS t INNER JOIN vb4_forum AS f ON f.forumid = t.forumid INNER JOIN vb4_user AS u ON u.userid = t.lastposterid INNER JOIN vb4_post AS p ON p.postid = t.lastpostid LEFT JOIN vb4_customavatar AS av ON av.userid = u.userid WHERE t.replycount != 0 AND f.forumid IN ( 1,2,3 ) AND t.visible="1" ORDER BY t.lastpost DESC LIMIT 10

From what I can tell with a couple test runs, your forum numbers 1,2,3 don't have threads in them. If you specify forumid's with threads, it has a return. This query selects all forums that have threads.

SELECT t.threadid, t.lastposterid, t.lastposter, FROM_UNIXTIME(t.lastpost) as lastposttime, t.replycount, t.views, t.title, u.avatarid, u.avatarrevision, p.pagetext, NOT ISNULL(av.userid) as hascustomavatar FROM thread AS t INNER JOIN forum AS f ON f.forumid = t.forumid INNER JOIN user AS u ON u.userid = t.lastposterid INNER JOIN post AS p ON p.postid = t.lastpostid LEFT JOIN customavatar AS av ON av.userid = u.userid WHERE t.replycount != 0 AND f.forumid in (select forumid from thread group by forumid) AND t.visible="1" ORDER BY t.lastpost DESC LIMIT 10;


As to the accuracy of the query, I could check that as my VB4 forum is a private VB5 > VB4 test/development environment. The easiest way to test it would be to post 11 new threads in different forums and run the query. The first thread you posted shouldn't come up in the query.