Create a dynamic database for each user using PHP

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

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

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.

Bobby Iliev
Site Moderator
Site Moderator badge
May 5, 2021
Accepted 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. Regards, Bobby

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Featured on Community

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel