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

Vamsi Krishna B · Accepted Answer · 2011-06-22 14:12:40Z

5

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);

edited Jun 22, 2011 at 14:12

answered Jun 22, 2011 at 13:17

Vamsi Krishna B

11.5k15 gold badges71 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Paris Liakos Over a year ago

thanks,Krish, but from the first checks it only provides a way to create tables,not users

Vamsi Krishna B Over a year ago

edited the answer..but I really donno if its a good practice or not.

Paris Liakos Over a year ago

it works but i had to change db->insert('user', $data); to db->insert('mysql.user', $data);

Paris Liakos Over a year ago

thanks and accepted:) just don't forget to correct your answer ;)

Ross · Accepted Answer · 2011-06-22 13:43:22Z

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.

answered Jun 22, 2011 at 13:43

Ross

18k7 gold badges46 silver badges65 bronze badges

2 Comments

Paris Liakos Over a year ago

hmmm, smart :) just like i updated on my question,no permission problems...i will try your logic out and let you know if it works

Paris Liakos Over a year ago

thanks Ross,it works, but i will accept Krish's answer since it's more complete.thanks again

Collectives™ on Stack Overflow

Codeigniter,create tables and users for MySQL

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related