Register Members List Search Today's Posts Mark Forums Read

Reply
 
Article Options
Using the vBulletin Database Class
Alan @ CIT
Join Date: Nov 2004
Posts: 625

South UK
by Alan @ CIT Alan @ CIT is offline 22 Jun 2006
Rating: (1 vote - 4.00 average)

Note: This tutorial assumes that you are familier with PHP and SQL, and will introduce you to the vBulletin way of running SQL commands

Using the vBulletin Database Class

Introduction

Like most large web-based software, vBulletin includes a Database Abstraction Layer. This allows you to read and write to databases without having to use database-specific functions such as mysql_query, or pgsql_query. The database abstraction layer allows you to run SQL without having to worry about what database server is being used as it will all be handled in the background.

This article is a brief introduction to the vBulletin Database class which handles all database abstraction within vBulletin and the add-ons that you create.

Accessing the Database functions

When vBulletin loads any page, the database object is created and stored in the $db variable. This object contains all of the functions that you will use to access the vBulletin database.

Note: You can also access the $db variable as $vbulletin->db, but for readability, I will refer to it as $db for the rest of this article.

Table Prefix

We'll start with the most important part of reading and writing data within vBulletin, the TABLE_PREFIX constant. As you've probably noticed, you can choose a string to prefix all of your database tables within vBulletin. By default, this is "vb_". So your users table would be called "vb_user", your posts table "vb_post" and so on.

It is important that you remember that not everyone will be using the same prefix (if any) as you, so hard-coding "vb_" into your script will not work for a lot of users.

Luckily, vBulletin provides the TABLE_PREFIX constant for us to use. TABLE_PREFIX should be fairly self-explanatory, it contains the table prefix for the vBulletin database tables. For example, if in your config.php, you set the table prefix to be "vb36_", then TABLE_PREFIX would contain "vb36_". TABLE_PREFIX is set automaticly when vBulletin runs so will be available in every vBulletin page.

Example:

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

As you can see in this example, we escape out of our SQL query string to include the TABLE_PREFIX constant. This is vitally important in every query that you run! If you leave it out of a query, your script will likely break for a lot of users.

For ease of reading, I will be leaving the TABLE_PREFIX constant out of my example queries below, but you should not!

Selecting Data

Almost every addon will need to read some data from a database table at some point. vBulletin provides the query_read() function for this purpose.

Example:

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

query_read() takes the SQL that you wish to execute as its paramater, and returns a database result set containing the results. This is the equivilent to mysql_query()

Handling the Result Set

As query_read() returns a database result set, rather than an array, we will need a function to read the result set and provide us with an array which we can then use. vBulletin provides a few functions which will do the job, namely:
  • fetch_field()
  • fetch_row()
  • fetch_array()
We will be concentrating on the last function, fetch_array() as that is the one you will find yourself using day-to-day.

Example:

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

fetch_array() takes a result set as it's paramater and returns an array with the current row. Because it will only return 1 row at a time, you will need to use it in a while() loop if you are fetching more than 1 row.

Example:

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

As you can see, each time fetch_array() is run within the while() loop, it moves on to the next row in the result set.

Selecting a single row

If you know that you will just be selecting a single row of data from your table (ie, a users details, or a single forum post), then vBulletin provides a handy function called query_first() which will not only run your SQL query, but also return the row as an array for you.

Example:

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

In this example, you can see that query_first() takes your SQL query as it's paramater and returns an array, rather than a result set. The query_first() function is handy when you know that you will only be selecting a single row from the table.

Writing to the Database

At some point, it is likely you will need to save some data to the database, or update an existing table with some changed data. To do this, vBulletin provides the query_write() function.

Example:

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

As you can see, query_write() takes the SQL statement as its paramater.

Another useful function when writing to the database is the affected_rows() function. This will tell us how many rows where affected by the last INSERT, UPDATE or REPLACE query.

Example:

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

This function takes no paramaters as it only works with the last write query that was performed, and will return the number of rows affected.

Fetching the last Auto-Increment number

If you have ever used PHP's MySQL functions, you'll likely be aware of the mysql_insert_id() function. When you have written a new row to a table that contains an Auto Increment field, mysql_insert_id() will return the Auto-Increment number for the new row.

Thankfully, vBulletin provides us with the insert_id() function which does the same job.

Example:

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

This function takes no paramaters and will return the most recent Auto-Increment field.

Handling Errors

vBulletin provides 2 functions that allow us to see if any errors have occured when we run our SQL. These are error() and errno().

$db->error() will return the Error Text for the most recent database operation.
$db->errno() will return the Error Number for the most recent database operation.

By default, if an SQL error occurs, vBulletin will display an error page with details of the SQL error on it. You can prevent this by using the hide_errors() function. When using this, be sure to perform your own manual error checking.

You can show the error page again by using the show_errors() function.

Freeing up Memory

vBulletin will destroy all of your result sets once the page has loaded. However, if you are running queries that are returning a lot of rows, you should unset the result set yourself once you are finished with it to free up memory.

vBulletin provides the free_result() function for this purpose.

Example:

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

free_result() takes the result set as it's paramater

Cleaning User Input

Most of us need to to run some form of SQL query that includes data submitted by the user. When using this data, you should never assume that it matches the data you have told the user to provide, as not all users are as honest as us

Thankfully, vBulletin provides us with some functions that will clean input for us. escape_string() and escape_string_like() being 2 of them.

escape_string() does exactly what it says on the tin. It will escape (usually using backslashes, although some Database Servers use a different method) any string value that you parse it.

Example:

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

escape_string_like() does exactly the same, but should be used when you are using the LIKE statement in your query.

Example:

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

Important! You should never use addslashes() in your SQL queries. addslashes() was not designed to escape SQL strings, and doesn't do a particularly good job at doing it. Always use escape_string() or escape_string_like() to make strings safer


Conclusion

To sum up, vBulletin provides you with functions to perform all common SQL tasks, without you having to worry about which database system is being used.

You should always use the vBulletin provided functions rather than database specific functions, as not everyone will be using the same database server as you. What's that you say? Only you will be using the script and you use MySQL? ok, but what happens 2 years down the line when you decide to switch to MySQLi, or PostgreSQL? Do you really want to have to go through your script replacing all the functions?

Good luck using your new found knowledge of the vBulletin Database Abstraction Layer, and remember: If you get stuck, just ask! Knowledge sharing is what vBulletin.org is all about!


(Note: If you want to reproduce this article anywhere, I have no objections, but I do request that you give me credit for writing it, and a PM letting me know would be appreciated )
Views: 28710
Reply With Quote
Comments
  #2  
Old 22 Jun 2006, 14:58
noppid noppid is offline
 
Join Date: Mar 2003
Nice work. That will certainly help folks that need the reference.
__________________
Add a photo gallery garage to your auto forum and get noticed by your members. Find out how at the vBulletin Computer Help Forum.
An informed rider makes their first destination the motorcycle forum at rider info.
Reply With Quote
  #3  
Old 15 Aug 2006, 02:06
AN-net's Avatar
AN-net AN-net is offline
 
Join Date: Dec 2003
for free_result is the result set the query or the fetch_array?

Last edited by AN-net : 15 Aug 2006 at 02:22.
Reply With Quote
  #4  
Old 19 Oct 2006, 00:55
Razasharp's Avatar
Razasharp Razasharp is offline
 
Join Date: Feb 2005
Thanks Alan, this is a VERY helpful article!

Would it be possible for you to give us a small example please? Examples make it easier for us (me!) to understand

Perhaps you could show us what code we'd use if we wanted to call the latest 5 threads a user has posted in three different forums, and display them in 3 seperate blocks (to be shown on the members profile page).

e.g. it would display something like:

Here are the user's lastest 5 threads posted in forum10

-Users thread 1 Title
date posted

-Users thread 2 Title
date posted

-Users thread 3 Title
date posted

-Users thread 4 Title
date posted

-Users thread 5 Title
date posted


Here are the user's lastest 5 threads posted in forum15

-Users thread 1 Title
date posted

-Users thread 2 Title
date posted

-Users thread 3 Title
date posted

-Users thread 4 Title
date posted

-Users thread 5 Title
date posted


Here are the user's lastest 5 threads posted in forum16

-Users thread 1 Title
date posted

-Users thread 2 Title
date posted

-Users thread 3 Title
date posted

-Users thread 4 Title
date posted

-Users thread 5 Title
date posted
Sorry if I'm being cheeky! I couldv'e posted which bits I (think I) know, but going back and forth might clog up this thread which I didn't want to do really.
__________________
Reply With Quote
  #5  
Old 04 Dec 2006, 06:53
wahooka wahooka is offline
 
Join Date: Nov 2006
thanks for the great tutorial.

i'm trying to use the db on a non-vb page...

this is my call which is causing the error:

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

i am getting the error:

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

any ideas on how to fix this?
Reply With Quote
  #6  
Old 04 Dec 2006, 08:45
Dismounted's Avatar
Dismounted Dismounted is offline
 
Join Date: Jun 2005
Real name: Hanson
You can only use these variables on a vBulletin powered page.
__________________
View My Modifications
29 Releases and Counting... Latest Modification: dmActivityStream - vBookie Integration (4.x)

Please do not PM me to ask for support - please use the relevant thread or forum.
Reply With Quote
  #7  
Old 04 Dec 2006, 13:05
Kungfu Kungfu is offline
 
Join Date: Dec 2005
Originally Posted by wahooka View Post
thanks for the great tutorial.

i'm trying to use the db on a non-vb page...

this is my call which is causing the error:

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

i am getting the error:

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

any ideas on how to fix this?
make sure to include global, this will start the database connection and gives you the ability to use the classes. otherwise you need to do it using your database functions, ex: mysql_query instead of that. For mysql there is no query_first type if function. then use $row = mysql_fetch_array( $result );

when you query you can just do LIMIT 1 if you to. Either way it should work ok.


great tut, i never even thought about different database issues. Im working on a script myself and used the database escape function instead of vbs, im gonna change that.

Last edited by Kungfu : 04 Dec 2006 at 13:11.
Reply With Quote
  #8  
Old 05 Dec 2006, 04:07
wahooka wahooka is offline
 
Join Date: Nov 2006
wow after a long time i figured it out...

i was doing:

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

instead of

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

the db is not accessed with a $db so that was my issue.... took quite a bit of debugging to figure that simple mistake out.

and btw you can use query_first with mysql

thanks for the help
Reply With Quote
  #9  
Old 05 Dec 2006, 22:54
Kungfu Kungfu is offline
 
Join Date: Dec 2005
Originally Posted by wahooka View Post
wow after a long time i figured it out...

i was doing:

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

instead of

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

the db is not accessed with a $db so that was my issue.... took quite a bit of debugging to figure that simple mistake out.

and btw you can use query_first with mysql

thanks for the help
errr wow, i didnt even see that either.
Reply With Quote
  #10  
Old 07 Dec 2006, 04:41
aragorn_reborn aragorn_reborn is offline
 
Join Date: Nov 2006
How do we get the number of rows returned by a select operation?
Reply With Quote
  #11  
Old 07 Dec 2006, 04:55
Paul M's Avatar
Paul M Paul M is offline
 
Join Date: Sep 2004
Real name: Paul Marsden
$vbulletin->db->num_rows($resourceid);
__________________


Lead Developer, vBulletin.Org & vBulletin.Com
Please do not PM me about custom work - I no longer undertake any.

Note: I will not answer support questions via e-mail or PM - please use the relevant thread or forum.
Cable Forum - DigiGuide
Reply With Quote
  #12  
Old 22 Jan 2007, 05:03
Billspaintball's Avatar
Billspaintball Billspaintball is offline
 
Join Date: Sep 2003
Real name: Bill
Oh yeah!
More of the puzzel becomes clear.
Thanks Alan
Reply With Quote
  #13  
Old 12 Mar 2007, 18:55
Jelmertjee Jelmertjee is offline
 
Join Date: Oct 2006
great article, this is certainly very useful for anyone trying to customize Vbulletin or making an entire mod, thanks!
Reply With Quote
  #14  
Old 02 Jul 2007, 14:12
Blue182 Blue182 is offline
 
Join Date: Apr 2007
Nice article!

I wrote a function which uses the $vbulletin->db object. But I always get
Fatal error: Call to a member function on a non-object in ...

I also included the global.php using require_once('./global.php');
But why I can't use the object inside a function?
Reply With Quote
  #15  
Old 02 Jul 2007, 14:23
Danny.VBT's Avatar
Danny.VBT Danny.VBT is offline
 
Join Date: Oct 2004
Edit: Sorry, thought this was the datamanager class...

Where are you using this? And what is the code you are using?
__________________
while($coffeeMachine->supply($Danny)) { $Danny->run(); }

Twitter Blog
Reply With Quote
Reply


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


New To Site? Need Help?

All times are GMT. The time now is 00:16.

Layout Options | Width: Wide Color: