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

Allie Syadiqin
  • 83
  • 6
  • 1
    It would pay you to read the docs at https://codeigniter.com/user_guide/database/forge.html and give that a shot. – TimBrownlaw Mar 19 '20 at 03: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. – Allie Syadiqin Mar 19 '20 at 08: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. – TimBrownlaw Mar 19 '20 at 09: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. – Allie Syadiqin Mar 19 '20 at 13:58

1 Answers1

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.

Alex7
  • 560
  • 3
  • 19
  • 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. – Allie Syadiqin Mar 19 '20 at 14:00