MySQL query to show hidden threads in unpostable forums

[zylstra]

My situation is described here, http://www.vbulletin.com/forum/showthread.php?p=1299918#post1299918. I don't know MySQL. Would someone show me the query to accomplish this?

Thanks.

[zylstra]

OK, since you guys haven't responded yet I'll rewrite the question here. :)

We've recently turned some boards into "categories" and put sub forums under them for people to post in. They CANNOT post in the category itself.

We have hundreds of forums. Do you know the SQL query that will show us these posts?

[Analogpoint]

select * from threads where forumid=XX

XX is the id for the forum that you turned into a category. If you want a fancier SQL statement, such as sorting the threads in a certain order or pulling the threads from more than one forum at a time, let me know.

[zylstra]

Thank you for helping. You did not address what I am looking for. Let me try to explain. We changed many forums from letting members post in them to not allowing them to post in them. I do not know the IDs of these forums. I mean I could find all of them, but there are many, and I am looking for only the ones that have posts in them. They are what I am looking for: the numbers (or names) of the forums that contain posts that members cannot post in.
SELECT forumid FROM forum WHERE threadcount > 0
AND allowed_to_post = 0 or something like that.

[Analogpoint]

Ok, I understand you now, but I'm not familiar enough with the db schema & bitfield structure to help you. The query would be a join on the forum and forumpermission tables.


select forum.* from forum, forumpermission where forum.forumid=forumpermission.forumid and forum.threadcound > 0 and forumpermission.forumpermissions=?? order by forum.title


The only thing I'm not sure about is where the ?? are. I believe that field is a bitfield that stores the permissions.

[zylstra]

Analogpoint, we're getting there. I've found the bitfield responsible for the forum options. It does not have to do with permissions. It's all in forum.options:<group name="forumoptions">
<bitfield name="active">1</bitfield>
<bitfield name="allowposting">2</bitfield>
<bitfield name="cancontainthreads">4</bitfield>
<bitfield name="moderatenewpost">8</bitfield>
<bitfield name="moderatenewthread">16</bitfield>
<bitfield name="moderateattach">32</bitfield>
<bitfield name="allowbbcode">64</bitfield>
<bitfield name="allowimages">128</bitfield>
<bitfield name="allowhtml">256</bitfield>
<bitfield name="allowsmilies">512</bitfield>
<bitfield name="allowicons">1024</bitfield>
<bitfield name="allowratings">2048</bitfield>
<bitfield name="countposts">4096</bitfield>
<bitfield name="canhavepassword">8192</bitfield>
<bitfield name="indexposts">16384</bitfield>
<bitfield name="styleoverride">32768</bitfield>
<bitfield name="showonforumjump">65536</bitfield>
<bitfield name="warnall">131072</bitfield>
</group>
If I have consolidated my forums so that the only option values that shouldn't contain threads are 85699 and 89795 what should the MySQL code be?

Thanks!

Analogpoint, I figured it out:SELECT forum.* FROM forum
WHERE (options = 85699 OR options = 89795)
AND replycount > 0
ORDER BY title