[Guide] MySQL Usage in XenForo & some notes

[SNN_imported]

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:

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

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]

Excellent stuff! Thank you for sharing. Going through this later - I will probably actually learn something.