Any suggestions on how we can create dynamic database for each user using PHP (as an when they register)? I am using the below code - it works on the local server, but not working in the live server on digitalocean. Is this a permission issue to create DB?

public function setupNewDatabaseWithSubdomainName($domain_name = '') {
        $db2 = array(
            'dsn' => '', 'hostname' => $this->hostname, 'username' => $this->username, 'password' => $this->password, 'database' => $domain_name,
            'dbdriver' => 'mysqli', 'dbprefix' => '', 'pconnect' => FALSE, 'db_debug' => (ENVIRONMENT !== 'production'),
            'cache_on' => FALSE, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '',
            'encrypt' => FALSE, 'compress' => FALSE, 'stricton' => FALSE, 'failover' => array(), 'save_queries' => TRUE

        $sql_db = "CREATE DATABASE IF NOT EXISTS " . $domain_name;
        $this->dbNew = $this->load->database($db2, TRUE);
        $arr_table_lists = $this->retun_all_tables_from_database($this->db->database);
        foreach ($arr_table_lists as $tbl_name) {
            $sql_new = "";
            $sql_new = "SHOW CREATE TABLE " . $this->db->database . "." . $tbl_name->table_name;
            $query = $this->db->query($sql_new);
            $result = $query->result_array();
            $sql_new = strtolower($result[0]['Create Table']);
            $sql_new = str_replace("create table", "create table if not exists ", $sql_new);
edited by bobbyiliev

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Submit an Answer
1 answer

Hi there,

Yes indeed if you are not using the root MySQL user it could be related to the privileges of the non-root user that you are using.

What you could do is grant the user privileges to either all databases or better all databases with a specific prefix.

  • To grant privileges to all databases you could run the following:
GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'%';
  • To keep things a bit more secure, you could introduce a prefix for all of your databases, for example, you could add a keyword like saas or your_app_name before each database, then you could use the following query to grant privileges to your user to all databases that start with that specific keyword:
GRANT ALL ON `yourPrefix\_%`.* TO 'your_user'@'%' WITH GRANT OPTION;

That way your_user will have access and be able to create any databases that start with yourPrefix\_.

If you take the second approach you would need to update this line here in your code to include the new prefix:

        $sql_db = "CREATE DATABASE IF NOT EXISTS yourPrefix\_" . $domain_name;

Hope that this helps.