subtato
Active member
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?
Fetching the first row that matches
$db->fetchRow() -- returns a simple array of the row you had selected.
Example Usage:
Example Output:
Fetching all rows that match
$db->fetchAll() -- returns an array of arrays of users.
Example Usage:
Example Output:
Writing Queries
$db->query() -- returns info / object of Zend_Db_Statement_Mysqli.
Example Usage:
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:
Example Output:
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:
Example Output:
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!
This shows the rowname => row itself. You can use this as (based on the code) $results['username'] or $results['is_banned'] in your code.
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.
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 />";
}
Code:
0 =>
Array
(
[username] => Facepalmx2
[is_banned] => 0
)
1 =>
Array
(
[username] => dv90
[is_banned] => 0
)
2 =>
Array
(
[username] => Skynet
[is_banned] => 0
)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`");
This shows the rowname => row itself. You can use this as (based on the code) $results['username'] or $results['is_banned'] in your code.
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.
(Well I was.. and still am 
