Register Members List Search Today's Posts Mark Forums Read

Article Options
vBulletin and mySQL
Join Date: Nov 2001
Posts: 4,765

by Brad Brad is offline 27 Jan 2005

- Intro

vBulletin uses a database for storage of data, everything from your users accounts, posts, and admin settings are stored here. The database is vital to the operation of your forum, in fact without it vBulletin won't even install. vBulletin (hence forth referred to as vB) relys on a database because it allows you to have a lot of room to grow and scale, when compared to flat files database storage is quicker in most cases when dealing with a lot of data.

Even though the need for a database existed long before vB became popular or even existed, us small timers where stuck with our flat file solutions because we bought hosting from a company that did not offer a database solution, could not afford our own server, or could not afford the software to power a database on a server we owned. This all changed a few years back when mySQL starting begin included on many or all packages most hosting companies offered. Because mySQL and php are both free to use, work very well with one anther, work on linux, mac, windows, and are relatively easy to 'pick up' on. This combo allows you to code and host very powerful scripts that can handle many users at once, and free should fit right into anyone's budget .

As I mentioned one of the reasons mySQL and php are so powerful is that they work well with each other. Lets face it, what good is storage if we can't modify and/or retrieve it 'on the fly'. Php allows you to 'look' for data based on user actions (clicking a link to a thread, reading the forum FAQ, viewing who's online page etc.) Modify existing data based on user action (changing profile, editing post) or insert new data (posting a new thread, registration of user account, uploading an avatar for the first time, sending a PM).

The normal vBulletin admin will almost never have to modify a mySQL query or manage the database by hand. But then again this is not a forum for the normal vB admin is it?

Odd's are in your time here you will install a hack that will make a modification to your mySQL database, or maybe even add a few extra queries to existing vB php files. Or maybe you've learned some php but don't understand the way vBulletin talks to mySQL. It is very important when working with vBulletin to rely on its internal mySQL functions rather then the native php ones. Altho you *can* use native php mySQL functions it can cause headaches down the line. Outside of native vB php files you can get away with it, but even then if your including global.php you may want to think twice about that.

- How vBulletin connects to mySQL

1. A vBulletin .php file is called by a client via a web browser, we will use /root/index.php as an example.

Near line 55 of the file index.php you'll see this bit of code:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

This 'includes' the contents of the file /root/global.php and executes the code within the file. Global.php is an important part of vBulletin, it dose things like checking global permissions, loading the templates needed for the page, verify's ip of user (for bans), verify's the clients browser etc. One important thing it dose is call the file init.php

2. global.php is executed

Near the very top of the file /root/global.php you'll find this bit of code:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

This includes the contents of the file /root/includes/init.php and executes the code. init.php is the 'initialize' file for vBulletin, it is as important if not more important then global.php. This file determines what type of php environment vBulletin is running in, and dose a lot 'clean up' work based on the environment vB is running in. Most of what it dose is not important to this text, the one this it dose that we are worried about is getting a connection to the mySQL database.

init.php will include the file /root/includes/db_mysql.php, this file is a collection of functions that are used to talk to mySQL.

Based on the info submitted in /root/includes/config.php this file runs a mysql_connect command and if the database connection is good it will start building the requested page.

So now we know how vBulletin connects to mySQL, but we know we shouldn't use native mySQL functions to talk to mySQL when working within vBulletin files. But why exactly? Well remember that file db_mysql.php I mentioned above? Well this is where it really comes into play.

- Understanding and working with db_mysql.php

Simply put, db_mysql saves you a lot of work and time. If you use the native functions you will be witting a lot of error checking code for things db_mysql can handle on the fly. If you use db_mysql you spend more time witting clean SQL, instead of witting php code to handle errors when that SQL may fail (and it will).

All is not lost, you didn't learn the native php functions for nothing. db_mysql is built upon these functions, so just think of db_mysql as a cleaner, faster, more efficient way of talking with mysql.

Below is a basic overview of everything db_mysql can do, all functions are based on the native php functions documented here

- db_mysql.php function overview

- function connect

Usage within vBulletin files: $DB_site->connect($server, $username, $password, $usepconnect);

What it dose:

Attempts to connect to mysql database.
Uses mysql_connect(); function for connection if $usepconnect is set to 0.
Uses mysql_pconnect(); function for connection if $usepconnect is set to 1.
If $password is blank will attempt to connect without one (example: mysql_connect($server, $username)

If connection is made $DB_site->select_db(); is called and database name defined in config.php is used, returns true

If connection is not made $DB_site->halt(); is called, returns false

You should never have to use this function, assuming you are including global.php in the page.

- function affected_rows

Usage within vBulletin files: $DB_site->affected_rows();

What it dose:

Runs this native php function: mysql_affected_rows();

Returns the number of rows affected by the last UPDATE, INSERT, or DELETE query.

- function geterrdesc

Usage within vBulletin files: $DB_site->geterrdesc();

Runs this native php function: mysql_error();

Returns a description of any errors in the last mysql operation, if no error occurred a blank string is returned

- function geterrno

Usage within vBulletin files: $DB_site->mysql_geterrno();

Runs this native php function: mysql_errno();

Returns the error number from the last mySQL operation, if no error occurred 0 is returned

- function select_db

Usage within vBulletin files: $DB_site->select_db();

Runs this native php function: mysql_select_db();

Returns true if database was selected without error. Returns false on failure with $DB_site->halt(); error message.

- function query_unbuffered

Usage within vBulletin files: $DB_site->query_unbuffered();

Runs this native php function: mysql_query($sqltextstring, 'mysql_unbuffered_query');

Important information from about unbuffered query's

mysql_unbuffered_query() sends a SQL query query to MySQL, without fetching and buffering the result rows automatically, as mysql_query() dose. On the one hand, this saves a considerable amount of memory with SQL queries that produce large result sets. On the other hand, you can start working on the result set immediately after the first row has been retrieved: you don't have to wait until the complete SQL query has been performed. When using multiple DB-connects, you have to specify the optional parameter link_identifier.

Note: The benefits of mysql_unbuffered_query() come at a cost: You cannot use mysql_num_rows() and mysql_data_seek() on a result set returned from mysql_unbuffered_query(). You also have to fetch all result rows from an unbuffered SQL query, before you can send a new SQL query to MySQL.
- function shutdown_query

Usage within vBulletin files: $DB_site->shutdown_query();

This function allows you to store certain queries in memory to be executed at the end of the scripts life. Some server do not support this function so shutdown_query will execute the queries right away if it can not store them and run them at the end of the scripts execution.

It will return $DB_site->query(); if the server dose not support shutdown functions, otherwise the submitted query is added to the $shutdownqueries array to be run at a later time.

- function query

Usage within vBulletin: $DB_site->query();

Runs this native php function: mysql_query();


Return Values

For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a resource on success, and FALSE on error.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success and FALSE on error.
Note that $DB_site->halt is called upon error.

- function fetch_array

Usage within vBulletin files: $DB_site->fetch_array();

Runs this native php function: mysql_fetch_array();

Returns an array of data based on the fetched row, false on failure.

- function free_result

Usage within vBulletin: $DB_site->free_result();

Runs this native php function: mysql_free_result();

Clears up memory used by prior mysql operations whose results we no longer need

- function query_first

Usage within vBulletin: $DB_site->query_first();

This function is a great one for saving time while coding. What is dose is query the database with $DB_site->query():, it then runs the results through $DB_site->fetch_array and returns the array based on the data in the first row retrieved by the query. It also runs $DB_site->free_result(); to save some memory.

Basically it saves you from having to write this all the time:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

If simply becomes

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

- function data_seek

Usage in vBulletin: $DB_site->data_seek();

Runs native php function: mysql_data_seek();

Description from

mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to mysql_fetch_row() would return that row.

Returns TRUE on success or FALSE on failure.
- function num_rows

Usage within vBulletin: $DB_site->num_rows();

Runs native php function: mysql_num_rows();

Returns the number of rows in the result set of a query. Only works with SELECT query types.

- function num_fields

Usage within vBulletin: $DB_site->num_fields();

Runs native php function: mysql_num_fields();

Returns the number of fields in the result set.

- function field_name

Usage within vBulletin: $DB_site->field_name();

Runs native php function: mysql_field_name();

Description of function from

mysql_field_name() returns the name of the specified field index. result must be a valid result identifier and field_index is the numerical offset of the field.

Note: field_index starts at 0.

e.g. The index of the third field would actually be 2, the index of the fourth field would be 3 and so on.

Note: Field names returned by this function are case-sensitive.
- function insert_id

Usage within vBulletin: $DB_site->insert_id();

Runs native php function: mysql_insert_id();

Description from

mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query using the given link_identifier. If link_identifier isn't specified, the last opened link is assumed.
This function is used in places like inserting new threads. When the thread table has new data inserted a new id is generated by mysql, this id might be needed for other queries for things like the posting cache. When you need to reference things by a common id number over many tables this function will become very useful to you.

- function close

Usage within vBulletin: $DB_site->close();

Runs native php function: mysql_close();

Closes connection to database server, in most cases this is called automatically by php when the script is done executing

- function print_query

Usage within vBulletin: $DB_site->print_query();

Prints out the last executed query between <pre></pre> html mark-up.

- function escape_string

Usage within vBulletin: $DB_site->escape_string();

Runs native php function: mysql_escape_string();

Escapes a string so it is safe to use with $DB_site->query();

- function halt

Usage within vBulletin: $DB_site->halt();

This handles any errors vBulletin might have when talking with mysql. If an error happens this function prepares a report and sends it to the admin's e-mail address. It will also log an error report if certain settings in the admin cp are turned on and configured properly. Last but not least it will present the client with an error message html page similar to a 404 explaining what happened. If the client is using an admin account a text area is also displayed that contains the same info that was sent to the admin e-mail and error log.

I hope this help someone in some way. I will try to do more of these time willing, the only problem is figuring out exactly what newbies have trouble understanding. A lot of people are asking for a 'how to' guide to vBulletin modification but an all in one guide is impossible for one person to do in a timely manner, and even then I don't think you could cover everything.


27. Jan 2005 at 12:32 am - First version posted
2. March 2005 at 1:23 am - Spell Check/Cleaned up text

Last edited by Brad.loo; 02 Mar 2005 at 10:47..
Views: 21699
Reply With Quote
Old 27 Jan 2005, 15:54
Natch's Avatar
Natch Natch is offline
Join Date: Nov 2002
Great work!

^^ Understatement of the century

... is the Admin @ - Official Community for a cult Vehicle based FPS - the only place for support!
... also he's Penfold to Dark_Wizard's Danger Mouse in development of vBWar
{DaniWeb: Learn HOW-TO mod_rewrite your vB board}
Reply With Quote
Old 27 Jan 2005, 17:11
Xenon's Avatar
Xenon Xenon is offline
Join Date: Oct 2001
Real name: Stefan Kaeser
compressed opinion: wow
Reply With Quote
Old 28 Jan 2005, 00:44
Erwin's Avatar
Erwin Erwin is offline
Join Date: Jan 2002
An awesome summary and good reference.
Reply With Quote
Old 28 Jan 2005, 03:50
Brad Brad is offline
Join Date: Nov 2001
Thanks guys, honestly it can be improved, ill try to finish this one up later this weekend.
Reply With Quote
Old 30 Jan 2005, 14:42
miz miz is offline
Join Date: Mar 2003
hmm can you explain in other words (simpler) what the shoutdown query is used for ?
Reply With Quote
Old 30 Jan 2005, 17:28
Guy G Guy G is offline
Join Date: Nov 2004
Very good...

dose = does right?
Reply With Quote
Old 01 Mar 2005, 10:30
sabret00the's Avatar
sabret00the sabret00the is offline
Join Date: Jan 2003
Real name: sabe
Originally Posted by miz
hmm can you explain in other words (simpler) what the shoutdown query is used for ?
if you have a script with 10 queries the first being a shutdown_query the shutdown_query will be the 10th query run and not the first. independant of query positions within the code.
UNDER CONSTRUCTION: Confessionary Hack

Make me a Hack of The Month author: GRPS: Groups Commune
make my hacks, they'll make you famous

unless it's a security concern, please do not pm/im me for support unless you're willing to pay.
Reply With Quote
Old 01 Mar 2005, 12:52
very nice!

/me adds this to his favorites
Reply With Quote
Old 02 Mar 2005, 05:11
Brad Brad is offline
Join Date: Nov 2001
Thank you for the kind works once again. I will go back through this and spell check and add some better useage info.
Reply With Quote
Old 02 Mar 2005, 05:39
Reeve of shinra's Avatar
Reeve of shinra Reeve of shinra is offline
Join Date: Oct 2001
im sorry im only now seeing this. Nice stuff in here.
Reply With Quote
Old 09 Mar 2005, 23:11
Dark_Wizard Dark_Wizard is offline
Join Date: Nov 2001
Real name: Bob
Nice stuff Brad....definitely needed for those looking for more info...
Reply With Quote
Old 10 Mar 2005, 00:14
KanyeWest's Avatar
KanyeWest KanyeWest is offline
Join Date: Dec 2004
Real name: A
Thumbs up Amazing....

magnificent work brad very easy to read :lick:
Reply With Quote
Old 07 Apr 2005, 15:23
jugo jugo is offline
Join Date: Feb 2004
DUDE....You better become a girl, cause I'm gonna Marry you!!!

__________________ - Bringing back the Game -

Website Hosting - Game Server Hosting -
Teamspeak Status Block for vBAdvanced / PHP-Nuke / php Include

PM me if you need hacks installed or simple template modifications...I'm cheap..
Reply With Quote
Old 07 Apr 2005, 22:54
Carnage Carnage is offline
Join Date: Jan 2005
very nice; just a quick q thou, what does mysql_escape_string do presisly... i normally use addslashes to make input safe for passing to the database. is taht basically its function or does it do other things as well?

If you want/need to contact me, best thing is to pm me on that site, username Carnage.
Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Article Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

New To Site? Need Help?

All times are GMT. The time now is 05:04.

Layout Options | Width: Wide Color: