CODEWITHSUNDEEP
phpmysqlcodeignitermigration
tthlaszlo
tthlaszlo

Reputation: 485

Codeigniter 3 migrate sql

I use Codeigniter 3 framework for my application, and i use the migration library. I write an object which is create a 'user_type' table, and insert into three row.

Here is the code:

private $tableName = 'user_type';

public function up() {
    $sql = "CREATE TABLE `$this->tableName` (
                `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , 
                `name` CHAR(120) NOT NULL
            ) ENGINE = InnoDB;";

    $sql .= "INSERT INTO `$this->tableName` (`id`, `name`) VALUES
                (1, 'user'), (2, 'provider'), (3, 'admin');";

    $this->db->query($sql);
}

If i want to use the migration, it drop an error message with this text: A Database Error Occurred

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 'INSERT INTO user_type (id, name) VALUES (1, 'user'), (2, 'provider')' at line 4

CREATE TABLE user_type ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , name CHAR(120) NOT NULL ) ENGINE = InnoDB;INSERT INTO user_type (id, name) VALUES (1, 'user'), (2, 'provider'), (3, 'admin');

Filename: C:/xampp/htdocs/rentacar/application/migrations/20151211113200_usertype_table_data.php

Line Number: 18

The interesting part of this, is if i echo the $sql variable, and run it in the phpmyadmin, it works well.

Upvotes: 2

Views: 3335

Answers (2)

Jumpei
Jumpei

Reputation: 621

You can't execute multiple sql statements at once.
Split the statements and execute each statement like below.

private $tableName = 'user_type';

public function up() {
    // First
    $sql = "CREATE TABLE `$this->tableName` (
                `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , 
                `name` CHAR(120) NOT NULL
            ) ENGINE = InnoDB;";
    $this->db->query($sql);

    // Second
    $sql = "INSERT INTO `$this->tableName` (`id`, `name`) VALUES
                (1, 'user'), (2, 'provider'), (3, 'admin');";
    $this->db->query($sql);
}

Upvotes: 1

Niranjan N Raju
Niranjan N Raju

Reputation: 11987

Use dbfore to create tables,

$this->load->dbforge();
$tableName = 'user_type';

$this->dbforge->add_field(array(
    'id' => array(
        'type' => 'TINYINT UNSIGNED NOT NULL'
    ),
    'name' => array(
        'type' => 'VARCHAR',
        'constraint' => '120',
    )
));

$this->dbforge->create_table($tableName);

$sql = "INSERT INTO `$tableName` (`id`, `name`) VALUES
        (1, 'user'), (2, 'provider'), (3, 'admin');";

$this->db->query($sql);

See about dbforge

EDIT
In that case try breaking your query like this

$tableName = "user_type";
$sql = "";
$sql = "CREATE TABLE `$tableName` (
        `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , 
        `name` CHAR(120) NOT NULL
    ) ENGINE = InnoDB;";
echo $this->db->query($sql);
$sql = "  INSERT INTO `$tableName` (`id`, `name`) VALUES
        (1, 'user'), (2, 'provider'), (3, 'admin')";

echo $this->db->query($sql);

Upvotes: 0

Related Questions