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 3 of 3 12 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
  #22  
Old 09-06-2005, 11:20 PM
White_Snake's Avatar
White_Snake White_Snake is offline
 
Join Date: Jul 2005
Location: Guadalajara Mexico
Posts: 100
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
this one would be great for vB 3.5.0
Reply With Quote
  #23  
Old 09-13-2005, 06:28 AM
Brad Brad is offline
 
Join Date: Nov 2001
Posts: 4,765
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Quote:
Originally Posted by White_Snake
this one would be great for vB 3.5.0
Data management has changed a bit in vB 3.5, there are a lot of new options offered. I plan on doing one of these for 3.5 but I will wait until gold is out as the code should be more stable then, there are still a few things in the 3.5 code that may change or have things added (see class_dbalter.php for a good example of that ).
Reply With Quote
  #24  
Old 09-13-2005, 05:41 PM
White_Snake's Avatar
White_Snake White_Snake is offline
 
Join Date: Jul 2005
Location: Guadalajara Mexico
Posts: 100
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Quote:
Originally Posted by Brad
Data management has changed a bit in vB 3.5, there are a lot of new options offered. I plan on doing one of these for 3.5 but I will wait until gold is out as the code should be more stable then, there are still a few things in the 3.5 code that may change or have things added (see class_dbalter.php for a good example of that ).
0ka i will chech that, thanks for the info
Reply With Quote
  #25  
Old 01-23-2006, 10:28 AM
HaMaDa4eVeR's Avatar
HaMaDa4eVeR HaMaDa4eVeR is offline
 
Join Date: Jun 2004
Location: Sudan
Posts: 107
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
nice topic
I've some questions
- How do I creat new table with 2 felds , sure with vBulletin functions and classes
example:-
PHP Code:
CREATE TABLE `mytable` (
`field1` TEXT NOT NULL ,
`field2` TEXT NOT NULL 
) TYPE = MYISAM ; 
- Add data into fields there
- read the data from the fields there
REMEMBER I knew the answer of all questions in PHP-MySQL syntax but i want to know in vBulletin classes and function , because I want to use it in my [Product] for vB 3.5
thanks
Reply With Quote
  #26  
Old 06-05-2007, 02:40 PM
bobdell01 bobdell01 is offline
 
Join Date: May 2007
Posts: 7
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
This has been driving me nuts for a couple hours...

It seems that $db->fetch_array($result) returns an associative array by default.

To return both an associative and numerically indexed array, use a result type of 0.

eg: $db->fetch_array($result,0)
Reply With Quote
  #27  
Old 08-15-2007, 11:32 PM
G00SE9 G00SE9 is offline
 
Join Date: Jun 2007
Location: TX
Posts: 6
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
We keep getting this error from vb 3.6.7pl1. Our host is half way around the world (not the best setup, but it was cheap...maybe we know why now....), which I'm sure isn't helping things, but this is really driving me crazy. We receive this at least 15-20 times/day.
We have less than 100 members and normally, no more than 25 logged in. Vbulletin has not
been modded extensively, except for a shoutbox, anonymizer, and vbimagehost.
The database is approximately 17MB at this time.
===============================================
Database error in vBulletin :

The first is a mysql_connect, the second is a mysql_pconnect; we receive both.

mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
/home/customersn/http_docs/forum/includes/class_core.php on line 274

OR:

mysql_pconnect() [<a href='function.mysql-pconnect'>function.mysql-pconnect</a>]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
/home/customersn/http_docs/forum/includes/class_core.php on line 274

(3 different sources of the errors are shown here)

MySQL Error :
Error Number :
Date : Thursday, August 16th 2007 @ 12:37:16 AM

Script : http://www.customersnw.com/forum/vbshout.php
Referrer : http://www.customersnw.com/forum/index.php

Script : http://www.customersnw.com/forum/login.php?do=login
Referrer : http://www.customersnw.com/forum/showthread.php?t=821

Script : http://customersnw.com/forum/search.php?searchid=8541
Referrer : http://customersnw.com/forum/index.php

IP Address : 2XX.X5.1X9.X8
Username :
Classname : vB_Database
================================================== ==
This is my first vb installation and "administration".....I'm totally lost here. But every error
is exactly the same regarding all the information in the actual error message ( mysql error message at the beginning of this post (can't connect or pconnect..../var/lib/mysql/mysql.sock).
And, our actual domain name IS NOT www.customersnw.com, it's customersnw.com.
One other thing of significance is that the database was imported from a phpbb 2.x site.
We have had the hosting company check the my.cnf file and supposedly the file contents are correct & the path to mysql.sock is correct.
A client section was also added: [client]
socket=/var/lib/mysql/mysql.sock


Any comments or constructive suggestions regarding this error would be appreciated.
Thank you.
Reply With Quote
  #28  
Old 06-02-2009, 09:35 AM
powerful_rogue powerful_rogue is offline
 
Join Date: Jan 2007
Location: Kent
Posts: 603
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default
Am I right in presuming this article is still valid with 3.8?
Reply With Quote
Reply
Page 3 of 3 12 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:16 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.05354 seconds
  • Memory Usage 2,307KB
  • Queries Executed 22 (?)
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
  • (5)bbcode_php
  • (7)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
  • (8)post_thanks_box
  • (8)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (8)post_thanks_postbit_info
  • (7)postbit
  • (8)postbit_onlinestatus
  • (8)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