Tag: Database

Posted on

How to get table name with prefix in Magento 2?

You can fetch prefix values from the table name in Magento 2. If your database table has set prefix value for all the tables and wants to fetch that value you can read the next portion.

Let’s say, You have added Prefix value as mage_ for all the tables.

Using getTableName() method, you can get a table with prefix code always,

<?php
namespace Your\PathTo\Model;

use Magento\Framework\App\ResourceConnection;

class PrefixTable
{

    public function __construct(
        ResourceConnection $resource
    ) {
        $this->resource = $resource;
    }

   public function getPrefixTable()
   {
    /* Create Connection */
        $connection  = $this->resource->getConnection();
        $tableName   = $connection->getTableName('sales_order'); // return "prefix_sales_order"

    /* Run Row query */
        $query = "SQL Query";
        $connection->query($query);
    }
}

In the above function, your table will get a prefix value with the table name.
If your table has a prefix as mage_ then your table value will get like, mage_sales_order

Posted on

What is the use of search_query table in magento 2?

In Magento 2 search_query database table store the search query, which users have searched using the search box in Magento 2.
— query_text field in a search_query table in stores the value of search string. When User search through site, all of the search entry will be added to search_query table in query_text field.
— num_results field used for Number of results for specific word in the site. That field show count of any string search in site.
— popularity field is displayed popularity of specific word in your site, the Biggest number will have the most popular search word for your site.
— store_id will show search from a specific store.
There are some other field also available in search_query table but above field is the main field of a table.

You can check from the admin panel, all of the search query will be shown from Search Terms sectino.
Click on Marketing -> SEO & Search -> Search Terms
You will get all the search result grid for your site.

Posted on

How to upgrade database table in magento 2?

When You need to add a new field in the existing table, you need to create UpgradeSchema.php file in Setup folder of your module.

We add new address field in our existing table by below way, We have just create a database table in the previous post, Create Database table in Magento 2

<?php

namespace Rbj\Training\Setup;

use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Ddl\Table;

class UpgradeSchema implements UpgradeSchemaInterface
{

    /**
     * {@inheritdoc}
     */
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        if (version_compare($context->getVersion(), '1.0.1') < 0) {
            $connection = $setup->getConnection();
            $connection->addColumn(
                $setup->getTable('create_form'),
                'address',
                [
                    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                    'length' => 255,
                    'nullable' => true,
                    'default' => '',
                    'comment' => 'Address field'
                ]
            );
        }
    }
}

Run php bin/magento setup:upgrade

Now you can check your new address field is display in your create_form table.