CODEWITHSUNDEEP
phpmysql
user158107
user158107

Reputation:

Dynamically-naming databases with PHP/SQL

Hey there guys and gals. I'm very new to php and am following various tutorials, reading books, watching videos etc.
The reason I'm learning is to create one specific web application, as well as to make that jump from simple geek to proper nerd, of course.
So far I've managed to learn most of what I need to create this web app.

The key part that has thus far eluded me is creating a dynamically-named, pre-defined(structure) database.
Essentially the application is a giant booking system. When a user registers I want the system to create a new database and link it to their account.

Whilst I know that I could easily have a php file that could run some SQL to create a database with all the right tables and columns, I don't know how to give that new database a unique name.

That name also needs to be written into the main users table so that whenever a user name connected to that client's account logs in, it uses that database name in the connection string and pulls up their data. Though, I'm sure that part wouldn't be as complicated.

If it matters, I'm using MySQL. Any help would be greatly-appreciated.

Edit: I should've made clear why I need more than one database in the first place, my apologies.
Essentially, it's going to be a private calendar(of sorts) system for businesses. Because of that, there will be an 'owner' of each database and all employees of that person will be utilising their employer's database.

Upvotes: 0

Views: 134

Answers (4)

Rob Drimmie
Rob Drimmie

Reputation: 1586

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.

Upvotes: 0

inerte
inerte

Reputation: 1154

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!

Upvotes: 0

VoteyDisciple
VoteyDisciple

Reputation: 37803

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.

Upvotes: 0

Sebastian Paaske Tørholm
Sebastian Paaske Tørholm

Reputation: 50868

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.

Upvotes: 3

Related Questions