[Guide] MySQL Usage in XenForo & some notes

Status
Not open for further replies.

SNN_imported

OMG Member
Joined
Oct 7, 2010
Messages
19
Just a little helpful guide for those who haven't picked up on MySQL Queries in XenForo, or need a little help.

There are a few methods, they're mainly based off of the Zend Framework API. (Located here)

How do I make $db work in my product?
$db = XenForo_Application::get('db');

Fetching the first row that matches
$db->fetchRow() -- returns a simple array of the row you had selected.
Example Usage:
$db = XenForo_Application::get('db');
$query_results = $db->fetchRow("SELECT username,email FROM `xf_user`");
print_r($query_results);

Example Output:
Array (​
[username] => Facepalmx2​
[email] => myemail@example.com​
)​

Fetching all rows that match
$db->fetchAll() -- returns an array of arrays of users.
Example Usage:
$q = $db->fetchAll("SELECT * FROM `xf_user` LIMIT 3");
print_r($q);

Example Output:
Array​
(​
[0] => Array​
(​
[username] => Facepalmx2​
[email] => someemail@example.com​
)​



[1] => Array​
(​
[username] => dv90​
[email] => some@inter.net​
)​


[2] => Array​
(​
[username] => Skynet​
[email] => sucha@troll.er​
)​



)​

Writing Queries
$db->query() -- returns info / object of Zend_Db_Statement_Mysqli.
Example Usage:
$db->query("CREATE TABLE `xf_noms` (
`orderid` int(11) primary key auto_increment,
`customerid` int(11) default '0' ");

Output is an Zend_Db_Statement_Mysqli object. Not of any use to the common developer. If you're truly interested in this, use print_r() to print the array.

Why can't I use mysql_num_rows()?
XenForo uses the Zend Framework, which reworks some of the mysql stuff, to make it (somewhat) easier.
In order to replicate mysql_num_rows (vB equivalent: $db->num_rows($x)) simply use count($queryResults);
count() is a built-in php feature to count the number of items in an array. This should only be used when using $db->fetchAll().

count() -- returns the amount of items in an array (used for $db->fetchAll())
Example Usage:
$query = $db->fetchAll("SELECT * FROM `xf_user`");

Example Output:
3​

Using the output of $db->fetchAll()
Normally, you would use while($row = $db->fetch_array($query)) in vBulletin. Here in XenForo, I do believe the only way is to use foreach(). (Please correct me if I'm wrong :))
Example Code:
PHP:
			$query = $db->fetchAll("select username,is_banned from `xf_user`");
			foreach($query AS $rowName => $results)
			{
				print $rowName . " => <pre>";
				print_r($results);
				print "</pre><br />";
			}
Example Output:
Code:
0 =>
Array
(
    [username] => Facepalmx2
    [is_banned] => 0
)

1 =>
Array
(
    [username] => dv90
    [is_banned] => 0
)

2 =>
Array
(
    [username] => Skynet
    [is_banned] => 0
)

This shows the rowname => row itself. You can use this as (based on the code) $results['username'] or $results['is_banned'] in your code.

Specifics: Models
When using a Model, instead of duplicating the db class, you should use $this->_getDb();
Example:

$q = $this->_getDb()->fetchRow("SELECT * FROM `xf_user`");
instead of...
$q = $db->fetchRow("SELECT * FROM `xf_user`");
Thanks to dmnkhhn for the information on models!

If you feel I should add anything, or see a mistake, simply tell me and I will fix it. I do not use anything other than these (commonly anyways), so I may be missing something obvious, or something that people use often. :)

The full Zend Framework documention for the Db class is located here: http://www.framework.zend.com/manual/en/zend.db.html
Note: There may be *some* differences, but it is just about the same for XenForo.
 

Floris

I'm just me :) Hi.
Staff member
Joined
Jan 1, 2001
Messages
60,096
Excellent stuff! Thank you for sharing. Going through this later - I will probably actually learn something.
 
Status
Not open for further replies.
Top