1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. 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

Discussion in 'Development Tutorials [Archive]' started by Facepalmx2, Oct 7, 2010.

  1. Facepalmx2

    Facepalmx2 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?
    $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.
     
  2. Floris

    Floris Guest

    Thumbsup! Excellent write out :)
     
  3. Blandt

    Blandt Well-Known Member

    errrrrrr bad memories :D
     
    Facepalmx2 likes this.
  4. AWJunkies

    AWJunkies Active Member

    Nice write up should help some people get started in the right direction
     
  5. dmnkhhn

    dmnkhhn Active Member

  6. Facepalmx2

    Facepalmx2 Active Member

    Thank you dmnkhhn, added the Models reference in the first post. I already had two different documentations saved in the first post :)
     
  7. Kier

    Kier XenForo Developer Staff Member

    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
         */
     
  8. Floris

    Floris Guest

    That's kinda above my head. So I can totally use that documentation :p
     
  9. Facepalmx2

    Facepalmx2 Active Member

    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)
     
  10. Paul M

    Paul M Well-Known Member

    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] ?
     
  11. Blandt

    Blandt Well-Known Member

    now I'm confused :confused: (Well I was.. and still am :D)
     
  12. dmnkhhn

    dmnkhhn Active Member

    Confusing. :eek:

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

    Facepalmx2 Active Member

    Yes, from the code Kier had posted.
    Ask questions, get answers. :p
     
    Blandt likes this.
  14. Facepalmx2

    Facepalmx2 Active Member

    I do believe so. Looks right.
    :)
     
  15. feldon30

    feldon30 Well-Known Member

    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.
     
    DarkSign likes this.
  16. dmnkhhn

    dmnkhhn Active Member

    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. :)
     
  17. Kier

    Kier XenForo Developer Staff Member

    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.
     
    Cyb3r likes this.
  18. Facepalmx2

    Facepalmx2 Active Member

    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()?
     
  19. Jaxel

    Jaxel Well-Known Member

    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
     
  20. dmnkhhn

    dmnkhhn Active Member

    I think this is the reason:
     

Share This Page