|
vBulletin and mySQL
by
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: PHP Code:
// ######################### REQUIRE BACK-END ############################
2. global.php is executed Near the very top of the file /root/global.php you'll find this bit of code: PHP Code:
// #############################################################################
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 http://us4.php.net/manual/en/ref.mysql.php - 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 php.net about unbuffered query's
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(); From php.net:
- 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: PHP Code:
$query = $DB_site->query($sqltext);
PHP Code:
$array = $DB_site->query_first($sqltext);
Usage in vBulletin: $DB_site->data_seek(); Runs native php function: mysql_data_seek(); Description from php.net:
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 php.net:
Usage within vBulletin: $DB_site->insert_id(); Runs native php function: mysql_insert_id(); Description from php.net:
- 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. Log 27. Jan 2005 at 12:32 am - First version posted 2. March 2005 at 1:23 am - Spell Check/Cleaned up text |