Map URL to MySQL databases

Question

I am interested to offer both hosted and SaaS version of my application. Now, SOers suggested that I map the URLs to MySQL databases. What I can think of is something like this

1. companya.mysoft.com will have the MySQL database that starts with the name companya*. i.e., compana_mydb
2. companyb.mysoft.com will have the MySQL database that starts with the name companyb*.i.e., companyb_mydb
3. and so on.

In this way it is easier to create new company accounts and doing the maintenance.

Now, is it possible to tweak all these settings inside the Apache configuration files and MySQL ini, and PHP ini files? ( I am using Apache, MySQL and PHP). It's preferable that I don't change my application.

Edit: MySQL doesn't support user-specified database-specific directories. Change the question to reflect this.

Edit2: Some suggested that I edited the PHP application to check for subdomain and select the correct database. But that would mean tweaking the hosting code to meet the SaaS version. So essentially we would have two code versions, one for hosting, another for SaaS, this is not what I desired. It would be better if everything is done through config file so that I would have only one version of code.

Answer 1

If you setup your PHP application right (maybe using some type of MVC design pattern or framework), you have one place where you specify the database name to be used by all subsequent mysql functions in that request. It is then just a matter of inspecting the request headers, grabbing (and validating) the company/db name prefix, and using that in the mysql_select_db function.

Answer 2

Why do you need to do it in the INI files? I would just have your PHP database class first parse $_SERVER['SERVER_NAME'] (or something else equivalent) to find out which subdomain you're on, and then select the corresponding database from there.

If you're using either PDO or mysqli (and you should be) for your database functions, you have to specify the db name in the constructor call, so you can just do this check right before you create that object.

Answer 3

You cannot create the database connection for PHP to use from Apache's configuration. The database connection has to be created from within PHP code, which can do things like check headers (the domain that the request used), or check config files that YOU create and specify if you're running the SaaS or the hosted version.