Tanbir.NET

Tanbir Ahmed Official

MyBB useful SQL Query List

mybb sql

Reset admin usergroup post count to zero (Assuming the admins group ID is “4”):

UPDATE `mybb_users` SET `postnum` = '0' WHERE `usergroup` = '4'

To force the Classic Postbit (requires template changes to remove option to change again):

UPDATE `mybb_users` SET `classicpostbit` = '1' WHERE `classicpostbit` = '0'

To force the Horizontal Postbit (requires template changes to remove option to change again):

UPDATE `mybb_users` SET `classicpostbit` = '0' WHERE `classicpostbit` = '1'

To set a lost/forgotten password to ‘test’ for a user (requires you to change X to the uid of the user this will apply to):

UPDATE `mybb_users` SET `password` = '098f6bcd4621d373cade4e832627b4f6', `salt` = '' WHERE `uid` = 'X'

To put you back into the Admin usergroup if you get changed to a different group (Assuming that the admins user ID is “1”):

UPDATE `mybb_users` SET `usergroup` = '4' WHERE `uid` = '1'

Turn off thread ratings in all forums (requires you to also rebuild the ‘forums’ cache in the ACP):

UPDATE `mybb_forums` SET `allowtratings` = '0' WHERE `allowtratings` = '1'

Turn on thread ratings in all forums (requires you to also rebuild the ‘forums’ cache in the ACP):

UPDATE `mybb_forums` SET `allowtratings` = '1' WHERE `allowtratings` = '0'

Make all invisible people visible (requires template changes to remove option to change again):

UPDATE `mybb_users` SET `invisible` = '0' WHERE `invisible` = '1'

Remove all avatars (requires settings/template changes to remove option to change again):

UPDATE `mybb_users` SET `avatar` = ''

Remove all sigs (requires template changes to remove option to change again):

UPDATE `mybb_users` SET `signature` = ''

Remove all custom usertitles (requires settings/template changes to remove option to change again):

UPDATE `mybb_users` SET `usertitle` = ''

Set all people to recieve admin emails (requires template changes to remove option to change again):

UPDATE `mybb_users` SET `allownotices` = '1' WHERE `allownotices` = '0'

Set all people to recieve PMs (requires template changes to remove option to change again):

UPDATE `mybb_users` SET `receivepms` = '1' WHERE `receivepms` = '0'

Activate all members awaiting activation:

UPDATE `mybb_users` SET `usergroup` = '2' WHERE `usergroup` = '5'

Delete all members awaiting activation:

DELETE FROM `mybb_users` WHERE `usergroup` = '5'

Sets everyone to automatically receive an email when they receive a private message:

UPDATE `mybb_users` SET `pmnotify` = '1';

Places all users with a post count of less than 100 into a specific usergroup (Change the user group ID to suit):

UPDATE `mybb_users` SET `usergroup` = '1' WHERE `postnum` < '100';

Sets all your forums closed threads to open:

UPDATE mybb_threads SET closed='' WHERE closed='1'

Displays all your users email addresses in phpmyadmin:

SELECT `email` FROM `mybb_users`;

Removes a user profile field (Change the XX to the profile field ID):

UPDATE mybb_userfields SET fidXX='';

Delete all users from a group (Edit the group ID):

DELETE FROM `mybb_users` WHERE `usergroup` = '1';

Displays last 5 posts created, in phpmyadmin:

SELECT * FROM (mybb_threads t, mybb_posts p) WHERE p.tid=t.tid ORDER BY p.dateline DESC LIMIT 5

Displays last 5 threads created, in phpmyadmin:

SELECT * FROM mybb_threads ORDER BY dateline DESC LIMIT 5

Force Default Icons on threads without Thread Icons (Change “X” with the thread icon ID):

UPDATE `mybb_threads` SET `icon` = 'X' WHERE `icon` = '' OR `icon` = '0';

Display the quick reply box for all users:

UPDATE `mybb_users` SET `showquickreply` = '1'

Turn on redirection pages for all users:

UPDATE `mybb_users` SET `showredirect` = '1'

Set all users to receive a notice when they receive a PM:

UPDATE `mybb_users` SET `pmnotice` = '1' WHERE `pmnotice` = '0'

Delete as users private messages Change “X” to the with the users ID:

DELETE FROM mybb_privatemessages WHERE uid='1'

Author: Tanbir A.

Tanbir is an engineer by profession, technologist, hobbyist photographer, graphics & web designer with avg knowledge in coding, SEO specialist, and blogger at TechWacky. His favorite topics to write about are tech, hacks, tutorials, arts & design and food. You can also find him on Twitter and Facebook!