1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Unmaintained How to read and write into the database (with a page) 1.0

I'll teach you how to read and write to the database using XenForo.

  1. Fuhrmann
    Step 1 - Setting the table

    To start we must know what are the fields that we use to read/write in the database. That is, the fields that will store the information we want to read later.

    I'll start setting the table name, fields and types of each:

    Table:
    xf_simple_text

    Fields:

    simple_id -> The autoincrement field of each row.
    simple_text -> This is the field that will contain our written text. Type: varchar; Lenght: 200;
    simple_date -> This is the date when we write the text. Type: INT; Lenght: 10;



    - Whenever you need to create a table in the database, follow a pattern. Put the name of your add-on along with the prefix of the other tables in your XenForo.
    - The more precise you define the types of each field in your table, the better the performance.


    Step 2 - Creating the installer

    When creating an add-on for XenForo you should follow a standard structure, both in the creation of folders such as the naming of the classes.

    All files in our add-on should be created in the folder forumroot/library/. This folder will also contain all other add-ons you have installed on your forum.

    First of all, let's create a folder for our add-on. "But where?" You must be wondering. Inside the folder forumroot/library/. So start by creating a folder with the name of our add-on: "SimpleText".

    This is the skeleton of the folder (note that I'm just listing the folder XenForo for reference):

    forumroot
    --library
    ----SimpleText -> our new folder!
    ----XenForo


    Now that we've created the folder, we must create a file called Installer.php that will contain two methods: install() that creates the table and uninstall() it will delete this table (when you uninstall).

    forumroot
    --library
    ---SimpleText
    -----Installer.php -> Our new file!
    ---XenForo

    We have our first folder and file in PHP. But what do we do with it? See the next step.



    Q. Why use the name Installer? And why create this file? What's the use?
    A. The Installer.php will be our installation file for our add-on. You can put whatever you want. "Installme.php", "MyFileInstall.php" or previously "ThisFileDoNotHaveAName.php" (not recommended). But for better understanding, simply use "Installer.php", so we know what it is about this file. By creating an add-on, we have the option of selecting a class for installation (and method) and a class to uninstall (and method). So every time someone install/uninstall your add-on, this class / method is called. As this simple add-on uses a table in the database to record and read the data, we will use this property of the add-on to tell XenForo that: "XenForo when installing my add-on please find a class and call the method SimpleText_Installer::install(), ok?"



    Step 3 - Installer.php

    Installer.php within the file will have two functions: install() and uninstall(). Respectively, the two will be used to install and uninstall our add-on.
    So, open the file Installer.php and we will begin to develop by creating a class:


    PHP:
    <?php
    class SimpleText_Installer
    {
    }
    ?>
    Note that we use another pattern we should follow: SimpleText_Installer. That says something to you?

    Yes! Take a look:

    SimpleText -> The name of our directory.
    Installer -> The name of our file.

    Therefore, we will always follow this pattern. If we had a structure like this:

    library
    ---SimpleText -> folder
    ----Installer -> folder
    -----Installer.php -> file
    ---XenForo


    How would the class name of our file Installer.php?

    Answer: SimpleText_Installer_Installer


    Look:

    SimpleText -> Folder!
    Installer -> Folder!
    Installer -> File!


    So I always follow this structure. Over the next file you will get more used to it.

    Back to our installation file, we will now create a variable that will contain the definitions of our table, along with the fields you want to create and the types of each. For this, we create a variable called "$table". This variable holds a SQL CREATE command who will be responsible for creating the table in our database.


    PHP:
    protected static $table = array(
        
    'createQuery' => 'CREATE TABLE IF NOT EXISTS `xf_simple_text` (             
                    `simple_id` INT( 10 ) NOT NULL AUTO_INCREMENT,
                    `simple_text` VARCHAR ( 200 ),
                    `simple_date` INT( 10 ) UNSIGNED,
                    PRIMARY KEY (`simple_id`)
                    )
                ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;'
    ,
        
    'dropQuery' => 'DROP TABLE IF EXISTS `xf_simple_text`'
    );


    You may notice that the variable $table is actually an array with two keys:

    createQuery -> SQL command to create the table.
    dropQuery -> SQL command to delete the table.

    Let's dissect the key createQuery SQL command:

    CREATE TABLE IF NOT EXISTS - Create the table if it does not exist. If it exists, skip this step and proceed to the next (if any). For details of the condition IF NOT EXISTS, click here.

    xf_simple_text - This is the name of the table we want to create the database.

    `simple_id` INT ( 10 ) - The autoincrement field of this table.

    `simple_text` VARCHAR ( 200 ) - First we'll create the field is called 'simple_text'. The field that will store the text we have written and then saved in the table. The type it is VARCHAR and the lenght is 200.

    `simple_date` INT UNSIGNED ( 10 ) - This field will store the date you sent the text to the database. Will be changed every time you write a new text and save the table. It is the type INT UNSIGNED the lenght is 10.

    ENGINE = InnoDB - We will use a Inno DB table. More information here.

    SET utf8 COLLATE utf8_general_ci - More information here.


    And the key dropQuery has the following command:

    DROP TABLE IF EXISTS `xf_simple_text` - This will delete the table of our database, if it exists. If not, nothing will be done. More information about the DROP TABLE here.

    Everything seen here? Ok, now we have our two queries for creating and deleting the table, we create two function that will run these two SQL statements.

    install()

    This will be our method for installing the add-on. The following code:

    PHP:
    /**
    * This is the function to create a table in the database so our addon will work.
    *
    * @since Version 1.0.0
    * @version 1.0.0
    * @author Fuhrmann
    */
    public static function install()
    {
        
    $db XenForo_Application::get('db');
        
    $db->query(self::$table['createQuery']);
    }


    Explanation:

    PHP:
    $db XenForo_Application::get('db');
    We took the object from the database by putting it in the variable $db so we can use it and call the query to create the table. We are using the class XenForo_Application and get() method.

    PHP:
    $db->query(self::$table['createQuery']);
    We use the object db, which is our database, and ran the query 'createQuery' which is in the array$table, defined earlier.


    uninstall()

    This is the method to uninstall our add-on. The following code:
    PHP:
    /**
    * This is the function to DELETE the table of our addon in the database.
    *
    * @since Version 1.0.0
    * @version 1.0.0
    * @author Fuhrmann
    */
    public static function uninstall()
    {
        
    $db XenForo_Application::get('db');
        
    $db->query(self::$table['dropQuery']);
    }
    As you can see, the two lines of code are almost the same method install().
    And this is the final code:
    PHP:
    <?php
    class SimpleText_Installer
    {
     
        protected static 
    $table = array(
            
    'createQuery' => 'CREATE TABLE IF NOT EXISTS `xf_simple_text` (             
                        `simple_id` INT( 10 ) NOT NULL AUTO_INCREMENT,
                        `simple_text` VARCHAR ( 200 ),
                        `simple_date` INT( 10 ) UNSIGNED ,
                        PRIMARY KEY (`simple_id`)
                        )
                    ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;'
    ,
            
    'dropQuery' => 'DROP TABLE IF EXISTS `xf_simple_text`'
        
    );
     
        
    /**
        * This is the function to create a table in the database so our addon will work.
        *
        * @since Version 1.0.0
        * @version 1.0.0
        * @author Fuhrmann
        */
        
    public static function install()
        {
            
    $db XenForo_Application::get('db');
            
    $db->query(self::$table['createQuery']);
        }
     
     
        
    /**
        * This is the function to DELETE the table of our addon in the database.
        *
        * @since Version 1.0.0
        * @version 1.0.0
        * @author Fuhrmann
        */
        
    public static function uninstall()
        {
            
    $db XenForo_Application::get('db');
            
    $db->query(self::$table['dropQuery']);
        }
    }
    ?>

    If you do not remember, XenForo classes follow the pattern of the names of folders and files. Therefore, the class XenForo_Application[/ i] is in which folder?



    Try to guess the folder and file of the following classes:

    Class-> XenForo_ControllerHelper_CacheRebuild
    Folders-> ??
    File-> ??


    Class-> XenForo_DataWriter_Discussion_Thread
    Folders-> ??
    File-> ??


    Class-> XenForo_Search_DataHandler_ProfilePost
    Folders-> ??
    File-> ??


    Step 4 - The Model

    The Model is responsible for reading data from our table. A brief explanation:


    First, create a new folder and a file inside the folder of our addon:


    forumroot
    --library
    ---SimpleText
    -----Model - new!
    --------SimpleText.php - new!
    -----Installer.php
    ---XenForo


    Open the file Model/SimpleText.php and let's give a name to the class:
    PHP:
    <?php
    class SimpleText_Model_SimpleText extends XenForo_Model
    {
     
    }
    ?>
    As you can see we follow the folder structure (SimpleText/Model/SimpleText.php) to name our class. As this is a Model, we extend to the XenForo_Model class!
    With the file opened, we will create a method to get a row in the database:
    PHP:
    /**
    * Get only one row using the data passed. 
    */
    public function getSimpleTextById($simpleId)
    {
        return 
    $this->_getDb()->fetchRow('
            SELECT * FROM xf_simple_text WHERE simple_id = ?'
    $simpleId);
    }
    With this query, we'll select all fields from our table WHERE the field simple_id is equal to the value passed to this method.
    Now, the next method will get ALL the rows.
    PHP:
    /**
    * Get all the rows of our table.
    *
    */
    public function getAllSimpleText()
    {
        return 
    $this->fetchAllKeyed('SELECT * FROM xf_simple_text ORDER BY simple_date DESC''simple_id');
    }
    Final Code:
    PHP:
    <?php
    class SimpleText_Model_SimpleText extends XenForo_Model
    {
        
    /**
        * Get only one row using the data passed. 
        */
        
    public function getSimpleTextById($simpleId)
        {
            return 
    $this->_getDb()->fetchRow('
                SELECT * FROM xf_simple_text WHERE simple_id = ?'
    $simpleId);
        }
     
        
    /**
        * Get all the rows of our table.
        *
        */
        
    public function getAllSimpleText()
        {
            return 
    $this->fetchAllKeyed('SELECT * FROM xf_simple_text ORDER BY simple_date DESC''simple_id');
        }
     
    }
    ?>

    Save the file and now let's go to the another step.


    Step 5 - The DataWriter

    The DataWriter will be responsible for writing our data into our table. Most often, when you develop an add-on, you will need a DataWriter. Then, go to work.

    What is a DataWriter?

    So let's create a new folder and a new file (always respecting the standard structure):


    forumroot
    --library
    ---SimpleText
    -----DataWriter - new!
    --------SimpleText.php - new!
    -----Model
    --------SimpleText.php
    -----Installer.php
    ---XenForo


    So, open the SimpleText.php file and let's give the file a class name:


    PHP:
    <?php
    class SimpleText_DataWriter_SimpleText extends XenForo_DataWriter
    {
     
    }
    ?>

    Now, we have to implement some functions that will make our DataWriter work:


    1-
    PHP:
    protected function _getFields()
    1. As the name says this function get all the fields defined for our table and define some types. If you add some new fields to the table, you have to make sure you'll update this file.

    Code:

    PHP:
    /**
    * Gets the fields that are defined for the table. See parent for explanation.
    *
    * @return array
    */
    protected function _getFields() {
        return array(
            
    'xf_simple_text' => array(
                
    'simple_id'    => array(
                    
    'type' => self::TYPE_UINT,
                    
    'autoIncrement' => true
                
    ),
                
    'simple_text'    => array(
                    
    'type' => self::TYPE_STRING'required' => true
                
    ),
                
    'simple_date'    => array(
                    
    'type'            => self::TYPE_UINT,
                    
    'required'        => false,
                    
    'default'        => XenForo_Application:$time
                
    ),
            )
        );
    }
    ----------------- // -----------------

    2.
    PHP:
    protected function _getExistingData($data)
    2. "Gets the actual existing data out of data that was passed in." So, when we use a DataWriter we can update the fields too, instead of just insert a new row. This function make sure that you can update a record that already exists. Grab the content for the existing record so we can update.

    Code:

    PHP:
    /**
    * Gets the actual existing data out of data that was passed in. See parent for explanation.
    *
    * @param mixed
    *
    * @see XenForo_DataWriter::_getExistingData()
    *
    * @return array|false
    */
    protected function _getExistingData($data)
    {
        if (!
    $id $this->_getExistingPrimaryKey($data'simple_id'))
        {
            return 
    false;
        }
     
        return array(
    'xf_simple_text' => $this->_getSimpleTextModel()->getSimpleTextById($id));
    }

    You may have noticed that we have a function that is not defined _getSimpleTextModel(). We will define them later. This function will take our Model (step 3) and call getSimpleTextByIdthat we've created in previous step.


    ----------------- // -----------------

    3.
    PHP:
    protected function _getUpdateCondition($tableName)
    3. If you want to update a row, you must have a update condition.

    Code:

    PHP:
    /**
    * Gets SQL condition to update the existing record.

    * @see XenForo_DataWriter::_getUpdateCondition() 
    *
    * @return string
    */
    protected function _getUpdateCondition($tableName)
    {
        return 
    'simple_id = ' $this->_db->quote($this->getExisting('simple_id'));
    }
    ----------------- // -----------------

    And the last one, the method to get our Model, so we can use it inside this DataWriter:

    PHP:
    /**
    * Get the simple text model.
    *
    * @return SimpleText_Model_SimpleText
    */
    protected function _getSimpleTextModel()
    {
        return 
    $this->getModelFromCache 'SimpleText_Model_SimpleText' );
    }
    Done! Our DataWriter is ready:

    PHP:
    <?php
    class SimpleText_DataWriter_SimpleText extends XenForo_DataWriter
    {
        
    /**
        * Gets the fields that are defined for the table. See parent for explanation.
        *
        * @return array
        */
        
    protected function _getFields() {
            return array(
                
    'xf_simple_text' => array(
                    
    'simple_id'    => array(
                        
    'type' => self::TYPE_UINT,
                        
    'autoIncrement' => true
                    
    ),
                    
    'simple_text'    => array(
                        
    'type' => self::TYPE_STRING'required' => true
                    
    ),
                    
    'simple_date'    => array(
                        
    'type'            => self::TYPE_UINT,
                        
    'required'        => false,
                        
    'default'        => XenForo_Application::$time
                    
    ),
                )
            );
        }
     
        
    /**
        * Gets the actual existing data out of data that was passed in. See parent for explanation.
        *
        * @param mixed
        *
          * @see XenForo_DataWriter::_getExistingData()
          *
          * @return array|false
        */
        
    protected function _getExistingData($data)
        {
            if (!
    $id $this->_getExistingPrimaryKey($data'simple_id'))
            {
                return 
    false;
            }
         
            return array(
    'xf_simple_text' => $this->_getSimpleTextModel()->getSimpleTextById($id));
        }
     
     
        
    /**
        * Gets SQL condition to update the existing record.
        * 
        * @see XenForo_DataWriter::_getUpdateCondition() 
        *
        * @return string
        */
        
    protected function _getUpdateCondition($tableName)
        {
            return 
    'simple_id = ' $this->_db->quote($this->getExisting('simple_id'));
        }
     
         
         
        
    /**
        * Get the simple text model.
        *
        * @return SimpleText_Model_SimpleText
        */
        
    protected function _getSimpleTextModel()
        {
            return 
    $this->getModelFromCache 'SimpleText_Model_SimpleText' );
        }
     
    }
    ?>

Recent Reviews

  1. maitandat1507
    maitandat1507
    5/5,
    Version: 1.0
    It's very clear and useful! I think with your tutorial, I can understand clearly 'bout practice on database using Xenforo. Have a nice day!
  2. Sean Kendle
    Sean Kendle
    5/5,
    Version: 1.0
    Thank you for clearly describing something that is very complex in a way that makes it accessible! I have noticed a disturbing trend in this forum of people who know a lot who are unwilling to share their knowledge, or do so in a way such that it obfuscates what they're saying to all but the most advanced PHP/MVC programmers. Thanks again!! ^_^
  3. Romchik®
    Romchik®
    5/5,
    Version: 1.0
    Thank you, very useful!
  4. Luis
    Luis
    4/5,
    Version: 1.0
    Good tutorial, thanks for your work Fuhrmann.
  5. The1LT
    The1LT
    5/5,
    Version: 1.0
    Thorough and well thought out. Thank you.
  6. Liam W
    Liam W
    5/5,
    Version: 1.0
    Excellent article. Helped me with my latest add-on :)
  7. MatthewEnderle
    MatthewEnderle
    5/5,
    Version: 1.0
    One of the most complete, and well written tutorials ever.
    1. Fuhrmann
      Author's Response
      Thank you! :)
  8. 0xym0r0n
    0xym0r0n
    5/5,
    Version: 1.0
    Just what I needed :)
  9. Sadik B
    Sadik B
    5/5,
    Version: 1.0
    The perfect Tutorial to learn how to create an Addon... Thanks Fuhrmann, this was really very very helpful.
  10. gordy
    gordy
    5/5,
    Version: 1.0
    Excellent write up thank you. I wish xenforo had a print to PDF button!