MySQL Usage in XenForo & some notes

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:

Mã:

$db = XenForo_Application::get('db');
$query_results = $db->fetchRow("SELECT username,email FROM `xf_user`");
print_r($query_results);

Example Output:

Mã:

Array (
[username] => Alucar
[email] => myemail@vietxf.org
)

Fetching all rows that match
$db->fetchAll() -- returns an array of arrays of users.

Example Usage:

Mã:

$q = $db->fetchAll("SELECT * FROM `xf_user` LIMIT 3");
print_r($q);

Example Output:

Mã:

Array
(
[0] => Array
(
[username] => Alucar
[email] => someemail@vietxf.org
)
 
[1] => Array
(
[username] => admin
[email] => some@vietxf.org
)
 
[2] => Array
(
[username] => Skynet
[email] => sucha@troll.er
)
)

Writing Queries
$db->query() -- returns info / object of Zend_Db_Statement_Mysqli.

Example Usage:

Mã:

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

Mã:

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

Mã:

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

Mã:

0 =>
Array
(
    [username] => Alucar
    [is_banned] => 0
)
 
1 =>
Array
(
    [username] => admin
    [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:

Mã:

$q = $this->_getDb()->fetchRow("SELECT * FROM `xf_user`");

instead of...

Mã:

$q = $db->fetchRow("SELECT * FROM `xf_user`");

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.