Talk:SQL Manager

Contents 1 Repairing MySQL tables 2 SQL to remove "Updated on" messages 3 Make all users visible 4 Forums users are posting to 5 Top rated topics 6 Topics with most views 7 Topics with most replies 8 Who voted? 9 Delete profile spammers

Repairing MySQL tables

If you get an error: "Table ... is marked as crashed and should be repaired", run this SQL statement for each crashed table:

REPAIR TABLE fud30_index;

SQL to remove "Updated on" messages

To remove the [Updated on: Fri, 11 June 2010 04:58] messages below posts for a particular user:

UPDATE fud_msg set update_stamp = 0, updated_by = 0 WHERE updated_by = (SELECT id FROM fud_users where login = 'naudefj');

Make all users visible

Run this statement to disable "Invisible Mode" for all users:

UPDATE fud30_users SET users_opt = users_opt & ~32768;

Forums users are posting to

To see to what forums a particular users post messages to:

SELECT f.name, count(*) FROM fud_msg m
LEFT JOIN fud_thread t ON m.thread_id = t.id
LEFT JOIN fud_forum  f ON t.forum_id  = f.id
WHERE m.poster_id = (SELECT id FROM fud_users where alias = 'admin')
GROUP BY f.name
ORDER BY count(*) DESC;

Top rated topics

To get a list of topics that received the highest ratings and most votes:

SELECT subject, rating, n_rating 
FROM fud_thread t
INNER JOIN fud_msg m ON t.root_msg_id = m.id
WHERE n_rating > 0
ORDER BY rating DESC, n_rating DESC
LIMIT 10

Topics with most views

SELECT subject, views
FROM fud_thread t
INNER JOIN fud_msg m ON t.root_msg_id = m.id
WHERE views > 0
ORDER BY views DESC
LIMIT 10

Topics with most replies

SELECT subject, replies
FROM fud_thread t
INNER JOIN fud_msg m ON t.root_msg_id = m.id
WHERE replies > 0
ORDER BY replies DESC
LIMIT 10

Who voted?

See who voted for what on a poll:

SELECT p.name AS "Poll", u.login AS "User", po.name as "Option"
FROM fud_poll_opt_track pt
INNER JOIN fud_users u ON pt.user_id = u.id
INNER JOIN fud_poll p ON pt.poll_id = p.id
INNER JOIN fud_poll_opt po ON pt.poll_id = po.poll_id AND pt.poll_opt = po.id
WHERE p.name = 'Does FUDforum support polls?';

Delete profile spammers

To remove users without posts and a URL in the profile (likely profile spammers):

DELETE FROM fud_users
WHERE posted_msg_count = 0
AND LOWER(home_page) LIKE 'http%';