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

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;

XENTIPS

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

We all have dumb questions :ROFLMAO:

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']);
    }
}
?>

XENTIPS

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?

XENXERCISE

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:

Model (XenForo_Model) - Used to communicate with the database, you get data in there, use it to query the database, and get data out of there. In XenForo this simply provides helper
methods for common actions. E.G.: When we are accessing a thread in our forum, the model is responsible for bringing the content of the topic, by querying the database.

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?

DataWriter (XenForo_DataWriter) - Data writers focus on writing a unit of data to the database, including verifying all data to the application rules (including those set by the owner) and doing denormalized updates as necessary. The writer may also interact with the cache, if required.

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' );
    }
 
}
?>

Reactions: BobVB

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

More resources from AnimeHaxor

Share this resource

Latest updates

How to read and write into the database (with a page) - Part 2