how to add fileds to sales order table programmatically ?

[bharath553]

Hi guys, i want to post two values into sales order table on the time of clicking the "place order" button. where should i add that fileds ?  or else any alternative ?

can any one guide me ?

[Rakesh Jesadiya]

You need to create event observer for save custom field value in database table using magento 2.

Check link,

https://magento.stackexchange.com/questions/178420/how-to-save-custom-field-value-in-sales-order-tab...

https://stackoverflow.com/questions/39041729/magento-2-how-to-save-data-after-the-order-was-successf...

[gelanivishal]

Hello @bharath553,

reate a new module and add a Module/Setup/InstallSchema.php file to it. This file is executed the first time you run

php bin/magento setup:upgrade

from you command line, with that module enabled. The following code adds a new column to sales_order with title my_new_column:

namespace MyCompany\MyModule\Setup;

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

class InstallSchema implements InstallSchemaInterface
{
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $setup;
        $installer->startSetup();
        $connection = $installer->getConnection();

        if ($connection->tableColumnExists('sales_order', 'my_new_column') === false) {
            $connection
                ->addColumn(
                    $setup->getTable('sales_order'),
                    'my_new_column',
                    [
                        'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                        'length' => 0,
                        'comment' => 'My New Order Column'
                    ]
                );
        }
        $installer->endSetup();
    }
}

This code is quite basic, you may see it in other modules. First I check if that column already exists (you can use tableColumnExists() method for this). This is not necessary, because this script is only executed once, if you install this module. For development reasons, it is good to make such if statements. With it, you can rerun this script as often as you like (remove module line in setup_module table before!).

--
If my answer is useful, please Accept as Solution & give Kudos

[bharath553]

hi bro, am getting error bcuz of below lines. can you check once ?

the below steps are correct ?

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();

$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();

$tableName = $resource->getTableName('sales_order');

$sql="INSERT INTO ".$tableName."(source, campaign) VALUES ('google', '12553') where entity_id=".$order_id;
$result=$connection->query($sql);

[Rakesh Jesadiya]

Kindly check for insert row query using magento 2 by below blogs,

https://www.rakeshjesadiya.com/direct-sql-queries-in-magento-2/

[Manthan Dave]

Hi @bharath553

So what error are you facing on the code you have posted ?

Can you please post the error too ? so its help us to troubleshoot the issue !

[bharath553]

Actually i could not found any error . but am sure that the query is not excuting.

[bharath553]

Actually i created two fields directly in DB. is that the problem for not excuting ?

[bharath553]

i wrote that code below path

/home/psabbavarapu/public_html/demo/vendor/magento/module-checkout/view/frontend/templates/success.phtml

after clicking place order i could not see continue shopping.but am sure after adding the below code the problem araised.

<?php /** @var $block \Magento\Checkout\Block\Onepage\Success */
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$storeManager = $objectManager->get('\Magento\Store\Model\StoreManagerInterface');
$storeID = $storeManager->getStore()->getStoreId();
$baseurl = $storeManager->getStore()->getBaseUrl();
$append="naturals-category.html";
$order_id = $block->getEntityId();

$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();

$source="google"; //var char in db
$campaign="12gb"; //var char in db

$tableName = $resource->getTableName('sales_order');

$sql="INSERT INTO".$tableName."(source,campaign) VALUES ('".$source."', '".$campaign."') where entity_id=".$order_id";
$connection->query($sql);

?>

<div class="checkout-success">
<?php if ($block->getOrderId()):?>
<?php if ($block->getCanViewOrder()) :?>
<p><?= __('Your order number is: %1.', sprintf('<a href="%s" class="order-number"><strong>%s</strong></a>', $block->escapeHtml($block->getViewOrderUrl()), $block->escapeHtml($block->getOrderId()))) ?></p>
<?php else :?>
<p><?= __('Your order # is: <span>%1</span>.', $block->escapeHtml($block->getOrderId())) ?></p>
<?php endif;?>
<p><?= /* @escapeNotVerified */ __('We\'ll email you an order confirmation with details and tracking info.') ?></p>
<?php endif;?>

<?= $block->getAdditionalInfoHtml() ?>

<div class="actions-toolbar">
<div class="primary">

<?php if($storeID == 1) :?>
<a class="action primary continue" href="<?= /* @escapeNotVerified */ $block->getContinueUrl() ?>"> <span><?= /* @escapeNotVerified */ __('Continue Shopping') ?></span></a>
<?php else :?>
<a class="action primary continue" href="<?php echo $baseurl.$tableName ?>"><span><?= /* @escapeNotVerified */ __('Continue Shopping') ?></span></a>
<?php endif;?>

</div>
</div>
</div>

can any one help me ?

[Kavitha Mano · Accepted solution]

If you think your sql query has an error please try to put "echo $sql;" . you can find out the error if it has any syntax error in the sql.

Already the row has been inserted in the table, so try to update the fields values in the row.

Try the sql query with update command as,

---

---

$sql= "UPDATE ".$tableName." SET source=' ".$sourceValue." ', compaign=' ".$compaignValue." ' where entity_id=".$order_id;