MoneyBoard - Money Making Forums

Full Version: [Tutorial] Common SQL queries and how to run them
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
[Tutorial] Common SQL queries and how to run them
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.

PHP Code:
UPDATE `mybb_users` SET `postnum` = '1' WHERE `uid` = '1'

or

PHP Code:
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:

PHP Code:
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.

PHP Code:
UPDATE `mybb_users` SET `email` = 'email@domain.com' WHERE `uid` = '1'

This will set the email of the user with uid 1 to email@domain.com. 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...

PHP Code:
UPDATE `mybb_users` SET `email` = 'email@domain.com'

... oops!! You've just set every single email entry to email@domain.com, 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):
PHP Code:
UPDATE `mybb_users` SET `classicpostbit` = '1' WHERE `classicpostbit` = '0'

To force the Horizontal Postbit (requires template changes to remove option to change again):
PHP Code:
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):
PHP Code:
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):
PHP Code:
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):
PHP Code:
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):
PHP Code:
UPDATE `mybb_forums` SET `allowtratings` = '1' WHERE `allowtratings` = '0'

Make all invisible people visible (requires template changes to remove option to change again):
PHP Code:
UPDATE `mybb_users` SET `invisible` = '0' WHERE `invisible` = '1'

Remove all avatars (requires settings/template changes to remove option to change again):
PHP Code:
UPDATE `mybb_users` SET `avatar` = ''

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` = ''

Set all people to recieve admin emails (requires template changes to remove option to change again):
PHP Code:
UPDATE `mybb_users` SET `allownotices` = '1' WHERE `allownotices` = '0'

Set all people to recieve PMs (requires template changes to remove option to change again):
PHP Code:
UPDATE `mybb_users` SET `receivepms` = '1' WHERE `receivepms` = '0'

Activate all members awaiting activation:
PHP Code:
UPDATE `mybb_users` SET `usergroup` = '2' WHERE `usergroup` = '5'

Delete all members awaiting activation:
PHP Code:
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:



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



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



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



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
Good list of basic queries,

~Hostpro
(06-16-2012 10:13 AM)Hostpro Wrote: [ -> ]Good list of basic queries,

~Hostpro

Thanks :D
Glad to see you find this helpful.
Reference URL's