0

I am using CodeIgniter 3 and MySQL 5.7 and I need to find a way to create a new database on-the-fly for every new client that signs up for an account.

So basically, I have a skeleton or source database called db_source which contains all the tables and some initial data. I need to be able to create a new database, eg. db_client_xxx, every time a new user signs up for an account. I would prefer to create the new database from the source database if possible.

If anyone needs more information, please let me know. Thanks.

Regards, Allie

Improve this question
4
  • 1
    It would pay you to read the docs at codeigniter.com/user_guide/database/forge.html and give that a shot. Commented Mar 19, 2020 at 3:44
  • Thanks @TimBrownlaw. I am familiar with dbforge and that is the method that I may implement if there is no other alternatives. However I am looking for an alternative solution where a database can be "cloned" from another database (source). For example, I am using HeidiSQL and I can select a database and make a full copy of that database. So I am just asking if there is a similar method that I could implement using PHP. Thanks. Commented Mar 19, 2020 at 8:11
  • Have you considered using the one database and then copy the tables with the corresponding prefix or suffix of the user id. If that would suit your requirements. Commented Mar 19, 2020 at 9:17
  • @TimBrownlaw: I have used that method in a past project but as the client base grew, it does affect the performance of the single database. That is why I am going by this route where each user account have their own dedicated database which may contain up to 20 tables. It would also be easy when the user wish to terminate and request all their data in the database as well as the security aspect. Commented Mar 19, 2020 at 13:58

1 Answer 1

Reset to default
1

A question similar to what you need has been answered here: how to import .sql file in mysql database using php

You can dump the db_source into a file. The correct answers explains how to run import a dump into an existing database. What you have to add to that script is to create database before that.

Please keep in mind that the database creation may take some time and may not be a very good experience for the user.

If I may suggest you another option is to create in advance the databases, e.g.: database_1, database_2 etc. and when a new user creates an account assign one database that is not used to that account. This may be easier and faster for the user. You can create a cron job to keep creating database so you always have a number of X databases available. This approach also may be easier because you can use shell instead of PHP to dump and create new databases.

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

1 Comment

I haven't thought of that. Thanks for the advise! I'll wait to see if anyone else have any other suggestion but this is the best answer so far.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.