FAQ: What you need to know about running queries.

[Marco van Herwaarden]

Sometimes you will need to run a query. Running a query is done to change the data in one of the tables in your database, or to change the strusture of the database like adding a table or adding a column to a table.

There are many different qys a query can be ran manually, some of them are:

• Using a Server Panel Tool like phpMyAdmin. See for specifics on using phpMyAdmin: Running a Query using phpMyAdmin
• Running the character based mysql client on a SSH/Telnet connection.
• Using the buildin functionallity of your AdminCP

I will focus on the use of the AdminCP possibility in this FAQ.

How to get started running queries from AdminCP.
Before you will be able execute queries from your AdminCP you need to setup permissions for yourself to do so. Permission to execute a query from AdminCP is granted through your "includes/config.php" file.

In this file find the line that start with:

PHP Code:

$canrunqueries = 


Behind the equal sign you will find a list of userid's seperated by a comma for those who have permission to execute queries from AdminCP. Add your own userid to this list.

Starting the SQL Tool in AdminCP
To start the Query tool login to your AdminCP->Import & Maintenance->Execute SQL Query
You will see a page with the following fields:

• Automatic Query - Here you can select one of the predefined queries to manage some of the memberdata.
• Manual Query - In this field you can enter a manual Query statement. This is where you will be doing most of your custom Query statements. Mind you that there can only be a single statement processed at a time.
• Results to Show Per Page - Here you can set how many result lines you want to show on a page, if you are running a query that output information.

After you selected an Automatic Query or entered a Manual Query statement, you press Continue to execute the selection. If you are submitting a query that change things in your database, you will first receive a warning before the statement is really executed.

Table Prefix
A lot of times you will find instructions to replace a Table Prefix before running a query. The Table Prefix is defined in your "includes/config.php" with the line:

PHP Code:

$tableprefix = ''; 


If there is nothing between the quotes, you are using an empty Table Prefix (i.e. no prefix), otherwise the text between the quotes is your Table Prefix.

If you are using a Table Prefix, all tablenames in your database will be prefixed with this string.
Example, if you are using an empty Table Prefix, the 'user' table will be named 'user' in the database. If you are using the Table Prefix 'vb3_', then that same table would be named 'vb3_user'.

Table Prefixes are normal only used if you need to load more then one instance of vBulletin into a single database, or if there might be naming conflicts with another application that used the same database.

How to change Table Prefix when instructed to run a query
A lot of times you will be instructed to run a query like this:
[sql]SELECT * FROM _TABLE_PREFIX_user;[/sql]
In this case you would need to replace the '_TABLE_PREFIX_' with your Table Prefix. If you don't use a Table Prefix this means you would need to remove this text, otherwise replace it with the defined Table Prefix.

Example using no Table Prefix and when using the Table Prefix os 'vb3_', the upper would be one of the following:
[sql]SELECT * FROM user;
SELECT * FROM vb3_user;[/sql]

How to know where one statement ends and the next begin.
SQL Query statements always end with a semicolon ';'. If you are instructed to run multiple queries at a time, input each statement seperate in the Manual Query input box and press Continue.

Example instructions:
execute the follwing statements (EXAMPLE!):
[sql]UPDATE USER
SET email = 'funny@laughs.com'
WHERE userid = 2;
SELECT * FROM user;[/sql]
This example consist of 2 statements, each to be run after the other. First you would run:
[sql]UPDATE USER
SET email = 'funny@laughs.com'
WHERE userid = 2;[/sql]
After that is completed, you would run:
[sql]SELECT * FROM user;[/sql]



Sorry good movie with Angela Jolie on now, will continue tomorrow. All remarks are welcomed.

[filburt1]

SQL Query statements always end with a semicolon ';'.

Note that when running queries individually in most clients, including, most importantly, vB's MySQL wrapper class, you do not need to include--and often should not include--the semicolon.

[Marco van Herwaarden]

True that a lot of clients don't require the ending semicolon. Wasn't sure about vB's wrapper, but didn't know there where also clients where you should not use a closing semicolon.

Will do some test and update tomorrow.

[T3MEDIA]

how would I write a SQL querry to change custom profile field x to 1 or 2 (on or off) or what ever options there is for that custom profile field.

example... profile field 31 has changed it used to default at 'slow' (first option) now the field has 'really slow' 'slow' and 'normal' but you want to default everyone at 'normal' and let all new users start at 'really slow' (that is easy. just defalut that in c.p.f manager)

the reseting is the confusing part for me.

[tehste]

oooh...
UPDATE _prefix_userfield SET field31='normal' WHERE field31='slow'
(no guarantees)

[Reeve of shinra]

I've noticed that vbulletin itself and certain hacks have a way of automatically specificing the table prefix in variables... can you share an example of that.

Like if $foo = user;
What would be the be the right way of writing
$foo = _table_prefix_user;

[tehste]

I dont think you can use TABLE_PREFIX in the queries (least not in PhpMyAdmin)
Maybe it would depend it the query thing in the ACP processes php which I don't think it does - hence the one query limit, which is a shame.

[T3MEDIA]

Originally Posted by tehste

oooh...
UPDATE _prefix_userfield SET field31='normal' WHERE field31='slow'
(no guarantees)

Sweet thanks now I can adjust my profiles. You know I was told from (someone else) that to do this was a hack...

wow.

Thanks tehste.