The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
|
|||||||
|
Using the vBulletin Database Class
Alan @ CIT
Join Date: Nov 2004
Posts: 625
South UK
06-21-2006, 10:00 PM
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: PHP Code:
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: PHP Code:
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:
Example: PHP Code:
Example: PHP Code:
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: PHP Code:
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: PHP Code:
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: PHP Code:
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: PHP Code:
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: PHP Code:
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: PHP Code:
Example: PHP Code:
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 )
|
|
#22
09-25-2008, 12:54 PM
|
|||
|
|||
Great Tutorial, it explained a wide area for me
thanx a lot
|
|
#23
10-09-2009, 03:47 PM
|
|||
|
|||
|
very useful article...tnx
|
|
#24
11-19-2009, 06:38 PM
|
|||
|
|||
|
you can use global $db; too an work with $db-> ... instead $vbulletin->db-> ...
But i have another question: Where can i find all aissigned users of a ticket? In my db in pt_issueassign is only one id of all assigned users. For Example - i have 3 users assigned, see all listed on top of the issue, but in the pt_issueassign is only one id. Not special id, think random. Is it me? It would very helpfully, if anyone can helb me to find all assigned users in the db. Edit: Oooh i forgot to say, it is a table from the project tools. Wrong Board - Sorry for the mistake - Thank you, MJ PS: please sorry for my english
|
|
#26
03-20-2010, 01:14 AM
|
|||
|
|||
|
Quote:
PHP Code:
|
|
#27
05-14-2010, 06:44 AM
|
|||
|
|||
|
Hi,
Can somebody please tell how can i add my own tables to Vbulletin database? any other solution other than accessing phpmyadmin? i actually need to make my own pages with some add,delete and search functionality. thanks
|
|
#29
08-26-2010, 04:49 PM
|
|||
|
|||
|
Can someone tell me how to find only forum posts? My first thought is to do a join between the forum and post table, with a where statement of where title='Main Forum'. But that seems like a waste (string comparison) - as well as what if someone changes the name of the forum? Is there some bit some where that tells me that a forum is a forum (as opposed to blogs, etc.)?
Thanks!
|
«
Previous Thread
|
Next Thread
»
|
|
All times are GMT. The time now is 02:30 AM.