We've built a single-tenant webapp in PHP and as it stands each hosted 'site' has its own database.

However we need to be able to run a script to create their database, assign a mysql user, and insert the default sql.

Now, we've got the database and user stuff working fine, its just the importing of the SQL. From what I can tell, PHP quite simply sucks when it comes to handling sql files, so storing a 'template' sql file seems to be out of the question.

My next thought was maybe storing a live database which is then cloned, giving the site its pre-populated database, however I cant see how this would work.

At the same time, I'd rather not 'hard code' the table creations into PHP as if we perform any updates we want to be able to update the SQL direct from our backend control panel.

Surely there has to be a logical way of handling creations and updates on a multi-tenant SaaS?

  • 2
    If each tenant has its own database, it's not really a "multi-tenant database" as your title suggests, right? Commented Feb 7, 2013 at 20:09
  • ah crap sorry. Working on too many things at once and getting muddled up. Correct, its single tenancy.
    – Sk446
    Commented Feb 7, 2013 at 20:20
  • I'm curious how 'php sucks at handling sql files'... are they magically different from other text files? Commented Mar 12, 2013 at 15:37
  • From the point of being able to simply give php an sql file to run - it cant. Its nothing to do with the file per-se, it's just PHP's lack of ability to split queries. So you cant get PHP to run an import without splitting the sql file into individual queries.
    – Sk446
    Commented Mar 12, 2013 at 18:41
  • Any reason you can't just use a single SQL file, have PHP split the file on the newline character using explode("\n", file_get_contents($sqlFilePath)), and then run each of the query statements one at a time by looping through the array of queries to execute? That would get around the limitation of only being able to execute a single statement per request in PHP.
    – Kyle Lowry
    Commented Mar 13, 2013 at 0:24

3 Answers 3


You can execute an SQL file from the command line using MySQL's client. So you always have the option of doing:

mysql -u <etc> -p <etc> <database> < master_definition.sql

right from a command line or shell script. Or exec() from within PHP. Once the structure is created, then insert users from within a PHP script.

  • Much better solution than attempting to split a file of commands yourself.
    – Jules
    Commented Mar 14, 2013 at 1:24

I think I would connect to the database you want to take this from, and query the information_schema database in you MySQL server that holds info on all databases. Then loop it, then use what ever is in it to make a new database by running querys. If you change some fields or table names you don't need to worry about anything beeing hardcoded, because all names are taken from the information_schema and not an existing database that you might change:

select * from SCHEMATA  where SCHEMA_NAME = 'dateabase_to_clone'

You can pass in the name with a variable, so when you start this cloning script nothing at all is hard coded. This gets you the specifics on the database, caracterset, colarion, path. You can now create an identical database dynamically.

select * from TABLES where TABLE_SCHEMA = 'database_to_clone' 

gives you a list off all the tables in this database. You loop it and get the rest and so on.


We ended up going with a rather dirty method.

The sql file has a comment below each query like so:

-- split --

When the sql file is loaded up by the php script, it simply explodes the sql data, using the split. It then just loops through the exploded array data and runs each query.

I dont like it, but its the best option out of a bad bunch really.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.