Bitrix Framework

Practical example of handling a Database using API D7 for creating a custom component. Create such a component:

<?php

class d7SQL extends CBitrixComponent
{
    var $connection;
    var $sqlHelper;
    var $sql;

    function __construct($component = null)
    {
        parent::__construct($component);
        $this->connection = \Bitrix\Main\Application::getConnection();
        $this->sqlHelper = $this->connection->getSqlHelper();

        //Query string. Get all logins, active users
        $this->sql = 'SELECT LOGIN FROM b_user WHERE ACTIVE = \''.$this->sqlHelper->forSql('Y', 1).'\' ';
    }

    /*
     * Fetch all values
     */
    function var1()
    {
        $recordset = $this->connection->query($this->sql);
        while ($record = $recordset->fetch())
        {
            $arResult[]=$record;
        }

        return $arResult;
    }

    /*
     * Return first two values
     */
    function var2()
    {
        $recordset = $this->connection->query($this->sql,2);
        while ($record = $recordset->fetch())
        {
            $arResult[]=$record;
        }

        return $arResult;
    }

    /*
     * Return two values, with two elements offset from the start
     */
    function var3()
    {
        $recordset = $this->connection->query($this->sql,2,2);
        while ($record = $recordset->fetch())
        {
            $arResult[]=$record;
        }

        return $arResult;
    }

    /*
     * Return first element from query
     */
    function var4()
    {
        $arResult = $this->connection->queryScalar($this->sql);

        return $arResult;
    }

    /*
     * Execute query, without returning the result, i. e. INSERT, UPDATE, DELETE
     */
    function var5()
    {
        $this->connection->queryExecute('UPDATE b_user SET ACTIVE = \'N\' WHERE LOGIN=\'test\' ');//Replace for UPDATE
    }

    /*
     * Modify the result
     */
    function var6()
    {
        $recordset = $this->connection->query($this->sql);
        $recordset->addFetchDataModifier(
            function ($data)
            {
                $data["LOGIN"] .= ": User login";
                return $data;
            }
        );
        while ($record = $recordset->fetch())
        {
            $arResult[]=$record;
        }

        return $arResult;
    }

    public function executeComponent()
    {
        //$this->arResult = $this->var1();

        //$this->arResult = $this->var2();

        //$this->arResult = $this->var3();

        //$this->arResult = $this->var4();

        //$this->var5();

        $this->arResult = $this->var6();

        $this->includeComponentTemplate();
    }
};

Code contains three variables:

1. connection - stores database connection;
2. sqlHelper- stores specific class object for generating SQL queries;
3. sql - SQL query.

We get a connection in the class constructor via applications which serve as input point.

Because we have a generated query string: retrieves all user logins from user tables, i. e. with the field ACTIVE set as Y. The query string uses the method forSql for secure input parameters. It also can limit the string length. In our case, it is shown as an example: passes Y but the length shall not be more than a single character.

Using an application, execute the query and get all values matching the value.

Function var1: it executes query and gets results via fetch. Standard data is returned immediately as a type, not as strings or numerals.

    /*
     * Return all values
     */
    function var1()
    {
        $recordset = $this->connection->query($this->sql);
        while ($record = $recordset->fetch())
        {
            $arResult[]=$record;
        }

        return $arResult;
    }

Function var2. Here, executes the same query but indicates the limit for the number of retrieved elements. In out case, its 2.

    /*
     * Returns first two values
     */
    function var2()
    {
        $recordset = $this->connection->query($this->sql,2);
        while ($record = $recordset->fetch())
        {
            $arResult[]=$record;
        }

        return $arResult;
    }

Function var3. Executes the same query, but indicates two additional parameters. Such record indicates that two elements will be returned. This is the last parameter. And theses elements are returned starting from the second position. This is a second parameter. We have есть отступаем два элемента и отдаем два, начиная с третьего элемента.

    /*
     * Return two value, with two element offset from the start
     */
    function var3()
    {
        $recordset = $this->connection->query($this->sql,2,2);
        while ($record = $recordset->fetch())
        {
            $arResult[]=$record;
        }

        return $arResult;
    }

Function var4 - scalar query, i. e. returning first and sole fetch result.

    /*
     * Return first element from query
     */
    function var4()
    {
        $arResult = $this->connection->queryScalar($this->sql);

        return $arResult;
    }

Function var5 - executing the query without getting the result. It's needed in case of INSERT, UPDATE, DELETE.

    /*
     * Execute the query, without fetching the result, i. e. INSERT, UPDATE, DELETE
     */
    function var5()
    {
        $this->connection->queryExecute('UPDATE b_user SET ACTIVE = \'N\' WHERE LOGIN=\'test\' ');//Replace to UPDATE
    }

Function var6 modifies the result. Using the method addFetchDataModifier declare the function which will get array with result for a single element and returns it after modifying it. In our case, the example is not complex: just adds User login text to the login field after colon.

    /*
     * Modify the result
     */
    function var6()
    {
        $recordset = $this->connection->query($this->sql);
        $recordset->addFetchDataModifier(
            function ($data)
            {
                $data["LOGIN"] .= ": User login";
                return $data;
            }
        );
        while ($record = $recordset->fetch())
        {
            $arResult[]=$record;
        }

        return $arResult;
    }

The fetch method can pass the converter. It looks as follows:

<?
$record = $recordset->fetch(\Bitrix\Main\Text\Converter::getHtmlConverter())

It's permissible to use methods Bitrix\Main\Text\Converter::getHtmlConverter and Bitrix\Main\Text\Converter::getXmlConverter. Accordingly, they prepare to display as html and as xml.