Go Back   vb.org Archive > vBulletin Article Depository > Read An Article > vBulletin 3 Articles
Reload this Page vBulletin and mySQL
FAQ Community Calendar Today's Posts Search

Reply
Page 2 of 3 1 2 3
 
Thread Tools
vBulletin and mySQL
Brad
Join Date: Nov 2001
Posts: 4,765

 

Show Printable Version Email this Page Subscription
Brad Brad is offline 01-26-2005, 10:00 PM
[high] - Intro[/high]

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.

[high] - How vBulletin connects to mySQL[/high]

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 ############################
require_once('./global.php'); 
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:

PHP Code:
// #############################################################################
// Start initialisation
require_once('./includes/init.php'); 
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.

[high]- Understanding and working with db_mysql.php[/high]

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

[high]- db_mysql.php function overview[/high]

- 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

Quote:
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();

From php.net:

Quote:
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:

PHP Code:
$query = $DB_site->query($sqltext);
$array = $DB_site->fetch_array($query);
$DB_site->free_result($query); 
If simply becomes

PHP Code:
$array = $DB_site->query_first($sqltext); 
- function data_seek

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

Runs native php function: mysql_data_seek();

Description from php.net:

Quote:
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 php.net:

Quote:
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 php.net:

Quote:
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.

Log

27. Jan 2005 at 12:32 am - First version posted
2. March 2005 at 1:23 am - Spell Check/Cleaned up text
Reply With Quote
  #12  
Old 03-02-2005, 04:39 AM
Reeve of shinra's Avatar
Reeve of shinra Reeve of shinra is offline
 
Join Date: Oct 2001
Location: NYC
Posts: 1,896
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
im sorry im only now seeing this. Nice stuff in here.
Reply With Quote
  #13  
Old 03-09-2005, 10:11 PM
Dark_Wizard Dark_Wizard is offline
 
Join Date: Nov 2001
Location: North Carolina
Posts: 1,251
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Nice stuff Brad....definitely needed for those looking for more info...
Reply With Quote
  #14  
Old 03-09-2005, 11:14 PM
KanyeWest's Avatar
KanyeWest KanyeWest is offline
 
Join Date: Dec 2004
Location: Mohegan Lake Ny
Posts: 102
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
magnificent work brad very easy to read :lick:
Reply With Quote
  #15  
Old 04-07-2005, 02:23 PM
jugo jugo is offline
 
Join Date: Feb 2004
Location: Reading your emails.
Posts: 573
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
DUDE....You better become a girl, cause I'm gonna Marry you!!!

Thanks!!!!!
Reply With Quote
  #16  
Old 04-07-2005, 09:54 PM
Carnage Carnage is offline
 
Join Date: Jan 2005
Location: uk
Posts: 760
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
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?
Reply With Quote
  #17  
Old 04-21-2005, 05:18 PM
Brad Brad is offline
 
Join Date: Nov 2001
Posts: 4,765
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Quote:
Originally Posted by jugo
DUDE....You better become a girl, cause I'm gonna Marry you!!!

Thanks!!!!!
Thanks , I don't think I will get married anytime soon tho

I am going to update this thread sometime in the next few days with examples and a section for shutdown queries (seem to be getting lots of questions about them).
Reply With Quote
  #18  
Old 05-20-2005, 07:31 AM
Stachel Stachel is offline
 
Join Date: Jun 2004
Location: US
Posts: 68
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Brad.loo, this is incredibly helpful.

Stachel
Reply With Quote
  #19  
Old 06-30-2005, 12:05 PM
akanevsky akanevsky is offline
 
Join Date: Apr 2005
Posts: 3,972
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Good job man, excellent article!
Reply With Quote
  #20  
Old 08-20-2005, 11:40 AM
havefun's Avatar
havefun havefun is offline
 
Join Date: Jan 2002
Location: Spain & Austria
Posts: 83
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
very interesting. thank you very much!
Reply With Quote
  #21  
Old 08-20-2005, 04:37 PM
m0nde's Avatar
m0nde m0nde is offline
 
Join Date: Mar 2005
Location: Toronto, ON Canada
Posts: 204
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Very well written article.

Um, you may want to spell check that again and replace "dose" with "does".

<--- runs away and hides.

- Sid
Reply With Quote
Reply
Page 2 of 3 1 2 3

« Previous Thread | Next Thread »

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
Forum Jump


All times are GMT. The time now is 06:42 PM.

Contact Us - Archive - Top

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.05360 seconds
  • Memory Usage 2,331KB
  • Queries Executed 25 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (4)bbcode_php
  • (6)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)modsystem_article
  • (1)navbar
  • (4)navbar_link
  • (120)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (2)pagenav_pagelink
  • (11)post_thanks_box
  • (11)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (11)post_thanks_postbit_info
  • (10)postbit
  • (11)postbit_onlinestatus
  • (11)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 
Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete