Dynamically-naming databases with PHP/SQL

Answer 1

If you need to create a new database for each user, I'd suggest radically rethinking your approach to the problem.

Very few problems require going that far.

For a booking system, for instance, I would imagine you would need one database with the following tables:

• A user table with the user information for each user
• A hotel table (if it's hotel booking, substitute what you need) with information on the hotels-
• A booking table that links each booking to a user, a hotel and a time.

Edit:

An example of a problem for which it would be suited would be a meta-booking system; as in a system where you could set up a booking system for your own site or whatever.

If that is what you need, ignore this.

Answer 2

This is certainly possible. You could, for example, call uniqid() and then check for an existing database by that name in case you happen upon a duplicate (though that's unlikely).

However, I'm extremely wary of your overall approach. In general, you should not have to create tables (excluding TEMPORARY tables) at runtime. Instead, you could put data for all organizations in a single database, but with a simple column to distinguish which records are associated with which users.

Answer 3

Find something unique about the user, like its username, and prefix the new database name with it. You can concatenate the number of databases already assigned to that user when creating a new one, so for example my first database would be inerte_0, my second one, inerte_1. Don't forget to sanitize whatever you'll use to prefix the database name to check if it's actually composed of allowed characters in Mysql's database names!

Answer 4

It sounds like you're using a centralized database to store some of the information - the database wherein the "main users table" you mention resides. You also mention that you can identify which client a user is associated with, which implies a clients table in that centralized database.

That clients table very likely has a primary key field. There is your unique identifier for each client's database. You can use that, or you can generate a hash of some sort using a combination of information from that row, something like:

$unique = md5( $client_id . $client_name . $date_created );

You can also make sure that the column holding the client database names is set to be unique.