John Davidson

Message:

I can't figure out what's the problem in the sql query. I'm using the dbforge library in codeigniter 3.

Mysql Version: 8.0

<?php



class Migration_merchant_withdraw_request extends CI_Migration

{

    public function up()

    {

        $this->dbforge->add_field('id');

        $this->dbforge->add_field([

            '`mitra_id` VARCHAR NOT NULL',

            '`amount` INT NOT NULL',

            '`password` VARCHAR NOT NULL',

            '`status` INT DEFAULT 0',

            '`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP',

            '`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',

        ]);

        $this->dbforge->create_table('merchant_withdraw_request');

    }



    public function down()

    {

        $this->dbforge->drop_table('merchant_withdraw_request');

    }

}

Which generates the sql query.

CREATE TABLE `merchant_withdraw_request` (

  `id` INT(9) NOT NULL AUTO_INCREMENT,

  `mitra_id` VARCHAR NOT NULL,

  `amount` INT NOT NULL,

  `password` VARCHAR NOT NULL,

  `status` INT DEFAULT 0,

  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  CONSTRAINT `pk_merchant_withdraw_request` PRIMARY KEY(`id`)

) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci

Yet, I get an error message.

Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL, amount INT NOT NULL, password VARCHAR NOT NULL, status INT DE' at line 3

I tried this style of adding fields in dbforge.

<?php



class Migration_merchant_withdraw_request extends CI_Migration

{

    public function up()

    {

        $this->dbforge->add_field('id');

        $this->dbforge->add_field([

            'mitra_id' => [

                'type' => 'VARCHAR',

            ],

            'amount' => [

                'type' => 'INT',

            ],

            'password' => [

                'type' => 'VARCHAR',

            ],

            'status' => [

                'type' => 'INT',

                'default' => 0,

            ],

            'created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP',

            'updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',

        ]);

        $this->dbforge->create_table('merchant_withdraw_request');

    }



    public function down()

    {

        $this->dbforge->drop_table('merchant_withdraw_request');

    }

}

Yet it produces the same error, what's the problem?

Problem Solved

It turns out that, I have not set the length of the VARCHAR data type. To do this in dbforge, this would be the syntax.

'column_name' => [

    'type' => 'VARCHAR',

    'constraint' => 64 // Set your wanted length.

]

---

My Answer:
DBForge in Codeigniter 3 is a database utility class that allows you to create, alter, and drop database tables. It provides a set of functions that make it easy to work with database schemas in your Codeigniter application.

Here is an example of how you can use DBForge in Codeigniter 3 to create a new database table:

php
$this->load->dbforge();

$fields = array(
'id' => array(
'type' => 'INT',
'constraint' => 5,
'unsigned' => TRUE,
'auto_increment' => TRUE
),
'name' => array(
'type' => 'VARCHAR',
'constraint' => 100,
),
'email' => array(
'type' => 'VARCHAR',
'constraint' => 100,
)
);

$this->dbforge->add_field($fields);
$this->dbforge->add_key('id', TRUE);
$this->dbforge->create_table('users', TRUE);


In this example, we first load the DBForge library using `$this->load->dbforge()`. We then define the fields for our new table in an associative array, where the keys are the field names and the values are arrays containing the field properties.

We then use the `add_field()` method to add the fields to the table, the `add_key()` method to set the primary key, and the `create_table()` method to create the table in the database.

DBForge in Codeigniter 3 provides many other methods for working with database tables, such as `rename_table()`, `drop_table()`, `add_column()`, `modify_column()`, `drop_column()`, and more. You can refer to the Codeigniter documentation for more information on how to use these methods.