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.