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:

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:

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 1504196589 at 1504196589 ---------------

One more question if I may. I added the option to sort by date:
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 thisSELECT 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: