Finding all sticky posts

[kgroneman]

I've got a lot of forums and a lot of moderators. Sometimes they forget about a sticky post and it gets out of date and needs to be unstuck.

This sql query will show all sticky posts: SELECT * FROM `vb_thread` WHERE sticky = 1

However, it doesn't give me the information in easily readable format I'm looking for. What I want is a query that will show me:

• The specific forum
• The title of the sticky post
• The author of the post

Would someone that knows a lot more about querying be able to help me refine my query to show that information? I would be very appreciative.

Thanks in advance. - Kim

[Dave]

Pretty simple to do:

PHP Code:

SELECT forum.title, thread.title, thread.postusername
FROM vb_thread AS thread
INNER JOIN vb_forum AS forum on thread.forumid = forum.forumid
WHERE thread.sticky = 1 


[kgroneman]

Pretty simple to do:

Simple for you anyway. Thanks!

I tried that and it gives me the thread.title and the thread.postusername, but not the forum.title. Can it easily be tweaked a bit to list the forum too?

[Dave]

It should already list that, try:

PHP Code:

SELECT forum.title AS forumtitle, thread.title AS threadtitle, thread.postusername 
FROM vb_thread AS thread 
INNER JOIN vb_forum AS forum on thread.forumid = forum.forumid 
WHERE thread.sticky = 1 


[kgroneman]

Yes! Thank you. It works as I had hoped. :up:

--------------- Added [DATE]1504196589[/DATE] at [TIME]1504196589[/TIME] ---------------

One more question if I may. I added the option to sort by date:

PHP Code:

SELECT dateline, thread.postusername, thread.title AS threadtitle, forum.title AS forumtitle, forum.forumid  
FROM vb_thread AS thread
INNER JOIN vb_forum AS forum on thread.forumid = forum.forumid
WHERE thread.sticky = 1
ORDER BY dateline 


Is there a way to have it display a readable date instead of the unix timestamp such as YYYY-MM-DD ?

[Simon Lloyd]

Try this

SELECT FROM_UNIXTIME(dateline), thread.postusername, thread.title AS threadtitle, forum.title AS forumtitle, forum.forumid 
FROM vb_thread AS thread 
INNER JOIN vb_forum AS forum on thread.forumid = forum.forumid 
WHERE thread.sticky = 1 
ORDER BY FROM_UNIXTIME(dateline)

[kgroneman]

Thanks Simon and Dave. You've just made managing sticky posts across a lot of forums a lot easier. I sincerely appreciate the time you've taken to assist me. :up: :up: :up: