>How to create and insert data into the table in Magento 2

How to create and insert data into the table in Magento 2

In the processing of Magento 2 development, creating a table and inserting data in the table when you would like to create a custom module is pretty important. In this article, we will guide you the steps to create a table and insert data to the table in Magento 2.

I. Create a simple module to create the table

First of all, we create a simple module named Bss_Schema, you need:

Step 1: Create file module.xml in app/code/Bss/Schema/etc/module.xml

Declare the module created already.

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
	<module name="Bss_Schema" setup_version="1.0.1"/>
</config>

Step 2: Create file registration.php in app/code/Bss/Schema/registration.php

Register the module with Magento system.

<?php
\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'Bss_Schema',
    __DIR__
);

Step 3: Create file InstallSchema.php in app/code/Bss/Schema/Setup/InstallSchema.php

Create table data_example and add a column to the table.

<?php 
namespace Bss\Schema\Setup;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\DB\Ddl\Table;
class InstallSchema implements \Magento\Framework\Setup\InstallSchemaInterface{
    public function install(SchemaSetupInterface $setup,ModuleContextInterface $context){
        $setup->startSetup();
        $conn = $setup->getConnection();
        $tableName = $setup->getTable('data_example');
        if($conn->isTableExists($tableName) != true){
            $table = $conn->newTable($tableName)
                            ->addColumn(
                                'id',
                                Table::TYPE_INTEGER,
                                null,
                     ['identity'=>true,'unsigned'=>true,'nullable'=>false,'primary'=>true]
                                )
                            ->addColumn(
                                'title',
                                Table::TYPE_TEXT,
                                255,
                                ['nullable'=>false,'default'=>'']
                                )
                            ->addColumn(
                                'content',
                                Table::TYPE_TEXT,
                                '2M',
                                ['nullbale'=>false,'default'=>'']
                                )
                            ->setOption('charset','utf8');
            $conn->createTable($table);
        }
        $setup->endSetup();
    }
}
 ?>

In which:

  • $setup-> startSetup (): start setup
  • $setup-> endSetup (): end setup
  • $conn: create a connection to the database
  • $tableName: create table name
  • addColumn (): there are 4 parameters; name is column name, type is data type (int, smallint, text, varchar, float …), size is data length, options are array values (primary key, empty or not …)

Example for options: [‘identity’=>true,’unsigned’=>true,’nullable’=>false,’primary’=>true].

  • identity => true: key
  • unsigned => true: the key is not negative
  • nulladble => false: not null
  • primary => true: primary key
  • default => ”: default data

Activate the module to create a table in the database, you should use the follow command:

php bin/magento module:enable Bss_Schema
php bin/magento setup:upgrade

Checking the database, you would see the data_example table created successfully.

But InstallSchema could only be used once time as running with the module installation. If you want to change and modify the mandatory you should use UpgradeSchema. We’ll show you how to add columns by using UpgradeSchema table:

Create file UpgradeSchema.php in app/code/Bss/Schema/Setup/UpgradeSchema.php

<?php 
namespace Bss\Schema\Setup;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\DB\Ddl\Table;
class UpgradeSchema implements \Magento\Framework\Setup\UpgradeSchemaInterface{
 
	public function upgrade(SchemaSetupInterface $setup,ModuleContextInterface $context){
        $setup->startSetup();
        if (version_compare($context->getVersion(), '1.0.1', '<')) {
            $this->addStatus($setup);
        }
        $setup->endSetup();
	}
	
    /**
     * @param SchemaSetupInterface $setup
     * @return void
     */
    private function addStatus(SchemaSetupInterface $setup)
    {
        $setup->getConnection()->addColumn(
            $setup->getTable('data_example'),
            'status',
            [
                'type' => \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
                'nullable' => true,
                'default' => 1,
                'comment' => 'Status'
            ]
        );
    }
}
?>

In which:

  • version_compare($context->getVersion(), ‘1.0.1’, ‘<‘) is modified version of module
  • addStatus () is function adding status column to table data_xample

Modify the module version in the module.xml file in Bss / Schema / etc / module.xml to run the UpgradeSchema.

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
	<module name="Bss_Schema" setup_version="1.0.1"/>
</config>

To add new column status to the table we run the following command:

 

php bin/magento setup:upgrade

Finally, checking in the database and you will see the status column added to the data_example table.

II. Add the first record in the table

Step 1: Set up Model, create DataExample.php file in Bss/Schema/Model/DataExample.php.

<?php 
namespace Bss\Schema\Model;
class DataExample extends \Magento\Framework\Model\AbstractModel{
	public function _construct(){
		$this->_init("Bss\Schema\Model\ResourceModel\DataExample");
	}
}
 ?>

Step 2: Set up the ResourceModel, create the DataExample.php file in the Bss/Schema/Model/ResourceModel/DataExample.php.

<?php 
namespace Bss\Schema\Model\ResourceModel;
class DataExample extends \Magento\Framework\Model\ResourceModel\Db\AbstractDb{
 public function _construct(){
 $this->_init("data_example","id");
 }
}
 ?>

Step 3: Set up Collection, create Collection.php file in Bss/Schema/Model/ResourceModel/Collection.php.

<?php 
namespace Bss\Schema\Model\ResourceModel\DataExample;
class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection{
	public function _construct(){
		$this->_init("Bss\Schema\Model\DataExample","Bss\Schema\Model\ResourceModel\DataExample");
	}
}
 ?>

Step 4: Set up Controller, create Index.php file in Bss/Schema/Controller/Index/Index.php.

<?php 
namespace Bss\Schema\Controller\Index;
use Bss\Schema\Model\DataExampleFactory;
use Magento\Framework\Controller\ResultFactory;
use Magento\Framework\App\Action\Context;
class Index extends \Magento\Framework\App\Action\Action{
    protected $_dataExample;
    protected $resultRedirect;
    public function __construct(\Magento\Framework\App\Action\Context $context,
        \Bss\Schema\Model\DataExampleFactory  $dataExample,
    \Magento\Framework\Controller\ResultFactory $result){
        parent::__construct($context);
        $this->_dataExample = $dataExample;
        $this->resultRedirect = $result;
    }
	public function execute(){
        $resultRedirect = $this->resultRedirect->create(ResultFactory::TYPE_REDIRECT);
        $resultRedirect->setUrl($this->_redirect->getRefererUrl());
		$model = $this->_dataExample->create();
		$model->addData([
			"title" => 'Title 01',
			"content" => 'Content 01',
			"status" => true,
			"sort_order" => 1
			]);
        $saveData = $model->save();
        if($saveData){
            $this->messageManager->addSuccess( __('Insert Record Successfully !') );
        }
		return $resultRedirect;
	}
}
 ?>

In which:

  • $model: the model initialization variables.
  • addData (): containing the key, column, and column values of the data passed.

Step 5: Configure route for controller, create routes.xml in Bss/Schema file /etc/frontend/routes.xml.

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:App/etc/routes.xsd">
	<router id="standard">
		<route id="schema" frontName="schema">
			<module name="Bss_Schema" />
		</route>
	</router>
</config>

Run the schema controller to add the data to the table. Open link http://localhost/magento219/schema/.

Finally checking in the database, you will see the data added to the data_example table as the below picture

Hope this article help you find your solution!

< Previous Post
Next Post >