Codeigniter,create tables and users for MySQL

Question

I want to create databases and users with CI programmatically. So far i have these 2 simple MySQL statements.

CREATE DATABASE `testdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

and

CREATE USER 'test_user'@'%' IDENTIFIED BY '***';

GRANT ALL PRIVILEGES ON * . * TO 'test_user'@'%' IDENTIFIED BY '***' WITH GRANT 
OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 
MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `testdb` . * TO 'test_user'@'%';

Can those be converted to Active Record, or does CI provides its own way of creating users/tables? i search the docs and couldn't find anything...

EDIT: I do this as an intranet application so no worries about permissions

Answer 1

CodeIgniter provides a class called as Database Forge , it contains some functions which can be used to perform basic maintenance on your DB.

$this->load->dbforge()

if ($this->dbforge->create_database('my_db'))
{
    echo 'Database created!';
}

and regarding adding users, its possible, but I don't know if its a good practice or not.

and also make sure that the mysql user who runs the queries has the permissions to create db.

$data = array(
   'Host' => 'localhost' ,
   'User' => 'krish' ,
   'Password' => 'somepass',
   'Select_priv' => 'Y',
   'Insert_priv' => 'Y'
);

$this->db->insert('mysql.user', $data); 

Answer 2

IF I understand, you can try

$config['database'] = 'mysql';
// other config details e.g username. port, pass

$this->load->database($config);
$query = "INSERT INTO user (host, user, password, select_priv, 
          insert_priv, update_priv) 
          VALUES ('localhost', 'user', PASSWORD('mypass'), 'Y', 'Y', 'Y')";
$this->db->query($query);

My syntax might be a little off.

Essentially - use the mysql database, insert directly in to the user table.

Some hosts may deny access to this directy, however.