Insert our table in database 1C-Bitrix

One of these days I was tasked to write my own functionality for our site on Bitrix CMS, which would output data from the table in the database of the site. The table needed to create a new one, with the following fields:

category

question

answer

Then it was necessary to display on the page all the records of this table, broken down into categories, into different blocks, through a cycle.

The task is absolutely not difficult, nevertheless, I wanted to write about this post. Beginner developers useful!

Create a folder, we place the file in it index.php

In the file we place the standard contents of the Bitrix file: header, meta-tagging, footer.

Next, we need to create our own table in the 1C-Bitrix database. You can not do this using the administrative panel: if we go to the Administration section, we will not see the item "Create a new table" anywhere. However, we can edit the available, via navigation points:
Administration -> Performance -> Tables.

To create a table, we need a console (I use PuTTY) and root-access to the server via SSH.

We connect to the server, we enter our database using the command:

mysql -uUsername -pUserPassword NameDatabase

We look, what tables we have:

show databases;

Choose the table we need (the one that is used for our site). See which database is used, and also the user name and password can be in the folder /bitrix/php_interface/dbconn.php

Enter the command:

use OurDatabaseName;

Further, we look, what we have tables (that at creation of the new to avoid mistakes with the identical name):

show tables;

Now we need to create our own table and on this our communication with the console will end.

Below I present the row for creating a table. You can adjust it at your own discretion. In particular, rename the name of the table (our table is called `questions`), and the title of the table headers, except for id (in the example below, these are headers: `category`, `question`, `answer`). If you need more columns, then add, by analogy.

CREATE TABLE `questions` ( `id` INT NOT NULL AUTO_INCREMENT , `category` VARCHAR(255) NOT NULL , `question` TEXT NOT NULL , `answer` TEXT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci;

In order for us to have something to edit in the administrative panel of Bitrix, I still suggest filling out the table with minimal test data. So it will be easier for us to navigate in the future. If you do not need it - you can skip this item.

INSERT INTO `questions` (`id`, `category`, `question`, `answer`) VALUES (NULL, 'Category 1', 'Question 1', 'Answer 1'), (NULL, 'Category 2', 'Question 2', 'Answer 2');

Now we can safely exit the console. First, close the connection to the database:

exit;

Go to the administrative panel of Bitrix. Then: Administration -> Performance -> Tables.

We see our table. Change the existing data (except id), add new rows.

Now our task is to filter all by categories and output to beautiful tables on our page. The number of tables should be equal to the number of categories.

We enter a new php-code on our page:

<?
// connect to the database
global $DB;

// make a query to the database, in which we obtain unique names for our categories
$categoryQuery = $DB->Query("SELECT category FROM questions GROUP BY category");

// run the loop and the array $arrayCategory write down the names of our categories
while($rowCategory = $categoryQuery->Fetch()) {
    $arrayCategory []= $rowCategory['category'];
}
?>

Now we need to generate page layout:

<?// run the cycle as many times as we have unique categories ?>   
<? foreach($arrayCategory as $category): ?>
    
    <?// for each iteration of the cycle, we form a new query to the database, in which we select all the data corresponding to the current category ?>
    <? $query = $DB->Query("SELECT * FROM questions WHERE category = '$category'"); ?>

        <table class="newDocs questions">
            <thead>
                <tr><td><?=$category?></td></tr>
            </thead>
            <tbody>
            
            <?// create another loop, inside of which we extract data from the resource ?>
            <? while($result = $query->Fetch()): ?>
                <tr data-question="head" class="gray">
                    <td><a href="#"><?=$result['question']?></a></td>
                </tr>
                <tr data-question='text'>
                    <td><?=$result['answer']?></td>
                </tr>
            <? endwhile ?>

            </tbody>
        </table>

<? endforeach ?>

After that, we will have so many such tablets with data, how many unique categories we have:

Category
Question 1
Answer 1
Question 2
Answer 2
Question 3
Answer 3

Create your table in the 1C-Bitrix database