Different table prefix for one table?

Question

Hypothetically, let's say I had multiple installations of some odd MySQL/PHP driven software. They are the same software so the database table structure is the same cross all of the installs. What I want to do, is make the software use a different database prefix for just one table. Specifically, a user table. So say the installs are set up like this:

Main install: /home/www/main, database main, prefix is1
Second install: /home/www/second, database main, prefix is2
Third install: /home/www/third, database main, prefix is3

So what I want is to tell second install and third install to pull from the users table on prefix is1 for its own data via that table. Thus, any user that registers on main install is also registered on second and third and vice versa. Is it possible, and how would I do it if it is? Even if it's just a workaround that gives the same basic result I would be happy.

I'm not sure if you can just prefix one table, is there any reason why you can't have everything else prefixed? And for the second part of your question, are you having sql querying issues? When someone registers you can simply insert 3 times, one in each table. Or is there something else you are trying to do? — mugetsu, Nov 15 '11 at 21:36

score 2 · Answer 1 · answered Nov 15 '11 at 21:45

If you don't want to modify the app's PHP source-code, and it's not already configurable in this respect, then another option is to modify the database, changing is2users and is3users to be views on is1users:

DROP TABLE is2users;
CREATE VIEW is2users AS SELECT * FROM is1users;
DROP TABLE is3users;
CREATE VIEW is3users AS SELECT * FROM is1users;

(See http://dev.mysql.com/doc/refman/5.0/en/views.html for general information on views, http://dev.mysql.com/doc/refman/5.0/en/create-view.html for information on CREATE VIEW specifically.)

Depending on the app, this may not work perfectly -- for example, the app might cache some information in memory (such as the current value of an identifier-sequence) -- but it will probably work. Test it before putting it in production!

Oh gosh thank you. Much appreciated, it broke the passwords for existing users on the copies but on a fresh installation it makes no difference as I can just abandon the root account (I usually do anyway). I could kiss you. Cookies for you. =3 — Australiya, Nov 16 '11 at 06:27

score 0 · Answer 2 · answered Nov 15 '11 at 21:40

0

Your php code likely goes something like this in something like cfg.php:

$prefix = 'is3'

and in something like user.model.php:

$sql = 'SELECT * FROM `'.$prefix.'users`';

So you need to change in two of three installs code for working with 'users' table. But it seems to be too dangerous.

answered Nov 15 '11 at 21:40

Constantine

119
8

What makes you say it would be dangerous? – Australiya Nov 15 '11 at 23:58
It's rather difficult to make all necessary changes and not to make mistakes in big project. Depends on it's structure, of course. I think ruakh's answer is better way. – Constantine Nov 16 '11 at 00:03

score 0 · Answer 3 · answered Nov 15 '11 at 21:49

The setup of this is easy, schema-wise. You mention 'installs' which means you are using some packaged library which probably contains a config file where you can change various settings, and chances are one of the settings is the table prefix. If there is no table prefix option you can browse the install code and find where the schema is located and change the prefix manually for each install.

Now comes the hardpart, getting the code to behave as you described. You will have to make your app aware of all three databases, meaning you will probably have to add two new database connectors to the code (one connector for each database). Then you will have to add logic to handle user CRUD functionality to insert/update/delete user data across all three databases (transaction would be good here).

What you are describing is possible, but will require some custom code.

Different table prefix for one table?

3 Answers3