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.
]