• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.
  • This forum has been archived. New threads and replies may not be made. All add-ons/resources that are active should be migrated to the Resource Manager. See this thread for more information.

MySQL Queries - How to use $db in XenForo & small notes

Facepalmx2

Active member
#1
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
)
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.
 

Kier

XenForo Developer
Staff member
#7
If anyone has the inclination, some documentation of XenForo_Model -> fetchAllKeyed() would be a good addition to this thread.
PHP:
	/**
	 * Fetches results from the database with each row keyed according to preference.
	 * The 'key' parameter provides the column name with which to key the result.
	 * For example, calling fetchAllKeyed('SELECT item_id, title, date FROM table', 'item_id')
	 * would result in an array keyed by item_id:
	 * [$itemId] => array('item_id' => $itemId, 'title' => $title, 'date' => $date)
	 *
	 * Note that the specified key must exist in the query result, or it will be ignored.
	 *
	 * @param string SQL to execute
	 * @param string Column with which to key the results array
	 * @param mixed Parameters for the SQL
	 *
	 * @return array
	 */
 

Facepalmx2

Active member
#9
If anyone has the inclination, some documentation of XenForo_Model -> fetchAllKeyed() would be a good addition to this thread.
-snip of php code-
Hmmmm. Will have to look at this, thanks Kier :D For now, gotta head off :p
So something like $db->fetchAllKeyed("SELECT * FROM `xf_user`", "userid"); would return user_id => array();? (going off of the docs included)
 

Paul M

Well-known member
#10
So, if I understand it correctly, it returns an array where

array[1] = array of data for userid 1
array[2] = array of data for userid 2
array[543] = array of data for userid 543

So to get userid 2's username you would use array[2][username] ?
 

dmnkhhn

Active member
#12
Confusing. :eek:

PHP:
$users = $db->fetchAllKeyed("SELECT * FROM `xf_user`", "userid");
$username_user1 = $users[1]['username'];
?
 

feldon30

Well-known member
#15
Ok, let's say I do a MySQL query to bring back the first 5 posts from the post table. Let's assume that over the years, posts have been added and deleted so that if I do
SELECT id, deleted FROM post LIMIT 5;

I might get a variable $results with these contents:
Code:
0 => Array (
     [id] => 20493
     [deleted] => 0
)
1 => Array (
     [id] => 20494
     [deleted] => 0
)
2 => Array (
     [id] => 20497
     [deleted] => 1
)
3 => Array (
     [id] => 20501
     [deleted] => 1
)
4 => Array (
     [id] => 20503
     [deleted] => 0
)
Ok, how do I use that? Well, I can't just use $results[20497]. In fact, the structure of $results is pretty useless, as I have to write a function to step through $results to figure out which part of the array refers to post #20497 (which turns out to be $results[2]). Unless I want to work on the list of posts in the order they were read out of the database, this is an inefficient way to approach a MySQL database.

What happens if I use XF's cool fetchAllKeyed('SELECT id, deleted FROM post LIMIT 5', 'id'); ? Now the data comes back in this form:
Code:
20493 => Array (
     [id] => 20493
     [deleted] => 0
)
20494 => Array (
     [id] => 20494
     [deleted] => 0
)
20497 => Array (
     [id] => 20497
     [deleted] => 1
)
20501 => Array (
     [id] => 20501
     [deleted] => 1
)
20503 => Array (
     [id] => 20503
     [deleted] => 0
)
Now if I need the contents of post id #20503, I just read $results[20503]. Couldn't be simpler. This beats having to write a function to iterate through the $results array every time I want to figure out which post is which.
 

dmnkhhn

Active member
#16
Not to forget that you can easily use PHP's array sorting functions because they key actually has a real value instead of a serial number. :)
 

Kier

XenForo Developer
Staff member
#17
Need a few corrections:
PHP:
$userModel->fetchAllKeyed('SELECT * FROM xf_user WHERE user_group_id = ?', 'user_id', 3);
Returns an array of all users with user_group_id = 3, with each entry keyed by the id of the user.

Note that fetchAllKeyed() is a method of XenForo_Model, not the DB class.
 

Facepalmx2

Active member
#18
Need a few corrections:
PHP:
$userModel->fetchAllKeyed('SELECT * FROM xf_user WHERE user_group_id = ?', 'user_id', 3);
Returns an array of all users with user_group_id = 3, with each entry keyed by the id of the user.

Note that fetchAllKeyed() is a method of XenForo_Model, not the DB class.
I couldn't figure out the output, or how to parse it anyways. I'm used to just using print_r() on the arrays I need to read data from. Is there some other way I'm supposed to read data from a function such as fetchAllKeyed()?
 

Jaxel

Well-known member
#19
I can't seem to get fetchAllKeyed working... this is my code:

Code:
<?php

class EWRporta_Model_Templates extends XenForo_Model
{
	public function parseTemplates($text)
	{
		$templates = $this->_getDb()->fetchAllKeyed("
			SELECT template_content AS content
				FROM EWRporta_templates
		", 'template_name');

		print_r($templates);
		return $text;
	}
}

Returns this error:

Fatal error: Call to undefined method Zend_Db_Adapter_Mysqli::fetchAllKeyed() in /library/EWRporta/Model/Templates.php on line 7