MyBB Community Forums

I've been meaning to do this for a while and we get a lot of requests of what query to run to do something, so, here's your ultimate guide on which queries do what, and how to run them.

What is an SQL Query??
Basically, they're commands that you run in a database manager such as PHPMyAdmin which effect the entries in the database, usually to mass edit something or change many entries to the same thing.

How do I write them??
Well, there's 2 ways I would suggest looking at. To demonstrate, I'll give an example of setting the admin postcount to 1.

UPDATE `mybb_users` SET `postnum` = '1' WHERE `uid` = '1'

or

UPDATE mybb_users SET postnum=1 WHERE uid=1

This will update the mybb_users table. It will set the postnum (post count) column to 1, where the uid (user ID) is 1. See how everything in bold there is in the query, in that order?? Now, when you run this, it will hopefully say that one table row has been affected, the user with the uid of 1, which will normally be you, the root admin.

Also notice that with these 2 example, the layout is different. One has and ' with spaces between each bit, and one doesn't. Generally speaking, either style will work, but watch out; if you use the first way, you must make sure you sue the correct or '. Database tables or column names must have a `, and the information that is to be changed must have a '. SQL query functions are case in- sensetive, so using UPDATE/update, SET/set, WHERE/where will still work fine, whether it's in capitals or not.

To specify an actual database in a query, you could do this:

UPDATE `database_name`.`mybb_users` SET `postnum` = '1' WHERE `uid` = '1'

See how the database name itself also has ` around it, and how there is a . between the database name and table name.

What can go wrong??

When running queries, make sure you intend to do what they will change. The changes made cannot be undone. If you run a query that sets everybody's email address to the same thing, you will kick yourself as you'll get ACP errors and you won't be able to undo it. This is a classic example of what can go wrong.

UPDATE `mybb_users` SET `email` = '[email protected]' WHERE `uid` = '1'

This will set the email of the user with uid 1 to [email protected]. Now, as we have specified the uid, it will only affect that one row, the row where the uid = 1. However, if you do this...

UPDATE `mybb_users` SET `email` = '[email protected]'

... oops!! You've just set every single email entry to [email protected], as you didn't specify which row to change, so it's just changed them all. This is bad news for you for obvious reasons. You'll get erros with users in the ACP and users won't get lost password emails, subscription emails, etc, etc.

In some cases however, as we will see below, it's ok to miss the WHERE setting. This can be done if we really do want to set every single entry to the same thing, for example, removing all avatars.

Ok, so what ones might I need??

Most of the queries you will ever need to use to sort a problem will be update queries, which, as the name suggests, just update entries rather than add or remove any, or that edit the actual database or table structure in any way. However there are some other types that we may use.

Below are some of what I personally consider to be important ones to know, or common ones that are requested. Please note that some of these will need additional actions for the full effect to be seen, for example, template changes may be needed.

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 reset a lost/forgotten to 'test' (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 (change the uid entry if the uid in need of changing isn't 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'

Here, the table prefix I have used is mybb_, and unless you changed this when you installed your forum, this will be the same for you. To check, either open ./inc/config.php and check the table prefix setting, or look in your database and see what all the table names start with. You'll need to correct prefix for the queries to work.

That's great, but how do I run these??

To be able to run these, you will need to have access to a database manager such as PHPMyAdmin. When you are in, you should see a page like this, with a list of databases on the left:

[attachment=12580]

When you click your database name, you will then see a page like this, with a list of all of your tables:

[attachment=12581]

Click the tab called 'SQL'. You will now see a page with a text box; this is where we enter our query.

[attachment=12582]

Hit 'Go', and it will run. You'll now see a confirmation message:

[attachment=12583]

If you see an error saying there was an error in the query, make sure that you copied it correctly, and ask the person who gave you the query to correct it if it wasn't one from the list here. If one of the queries given here doesn't work, try and use the second version given at the start of the thread.

Also, it is highly recommended that you do not run a query unless you are totally sure what it will do, that you know it is correct, or you trust the person who gave it to you. SQL queries can be like playing with fire, make one small error, and you could be in trouble.

I hope this has been helpful to some people. If you have anything that you want clarification on, or that you want added, let me know

sweet, just what i was looking for, stated in a recent post

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

PHP Code:

UPDATE mybb_users SET signature = ''

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

PHP Code:

UPDATE mybb_users SET usertitle = ''
__________________________________

what would need to be done to get rid of it out of template and out users control panel ?
___________________________________
but over all ... great TUTORIAL Bro

What would be the query for me to switch all users themes to the same one?

We celebrate holiday's and I have a lot of themes on one board. Sometimes I want to switch themes for everyone for a day or so and if they're overriding the "use default" setting doing a simple default theme switch won't work.

You can force a theme on users in the ACP, it's one of the options when you click Options next to a theme.

How to insert post icons in Thread where is set No icon with SQL queries?

UPDATE `mybb_threads` SET `posticon` = 'X' WHERE `posticon` = '';

I think that's right. Change X to the ID of the post icon, found at the end of the URL when you edit the posticon in the ACP.

Correction:

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

Thank you, Matt.

(2009-09-21, 09:09 PM)MattRogowski Wrote: [ -> ]You can force a theme on users in the ACP, it's one of the options when you click Options next to a theme.

But is it permanent that way or they can change at any time thereafter? I want them to be able to revert if they want after the "event" or holiday.

Yep, forcing basically runs a query to set people to a theme of your choice, they're not bound to that theme, just changes their setting