How do you combine multiple mysql databases into one database? I was thinking thta soem servers restrict you to one to five mysql databases. If I have a dynamic part of a table on my site, I would use one database for that. For every table of dynamic information, I use one database. Can I streamline what I am doing and combine all of my databases into one? I could make multipple tables in the same database. Would that work? thanks

Reply

The most common method, and many well coded apps support this, is table prefixes. So let's say you have xoops. All the tables would be xoops_news, xoops_conent, xoops_banners, etc.

Then let's say you want to have Promisance game engine.

Then those tables could be prom1_game, prom1_news.

Let's say you want a second Promisance game engine:

Prom2_game, prom2_news

let's say you want to include phpbb...

phpbb_news, phpbb_members

so on and so forth. I used to have to do this back in the day when you only got 1 MySQL database with your hosting accounts.

Reply

Exactly.
The trouble with that is the need to re-code your queries and such in all of your scripts, but it is do-able, yes.
It would be preferred to have seperate databases for each "function" to make the code portable and to allow for "modularization", but if the larger restriction is the number of databases allowed on the account, then it would win the battle.
SQLite DBMS is available through php. Might want to check to see if that could be used to augment the D/b count. MySql and SQLite are 'similar', but not the same. Have a look at the php Manual for information about SQLite.
Or are there some uses that might be able to use 'flat-files' instead of Databases? Using a flat-file to store information which doesn't change too often or isn't used much might be one way to reduce your D/b count.
As to the 'how-to-do-this' aspect of combining the data tables into a single database, have a look at the phpadmin software. It does amazing stuff.

Reply

That is the only way to do it and if you wrote the code correctly to begin with, you would have made a new table for each new set of data you have rather than a new database. A database should be the same for everysite so that if you ever want to export it or even distribute it as a template, all the other person has to do is import the 1 table and everything will work. Plus it keeps your databases clean and easy to use if you host more than one site on the same account and both use a database. Then you dont have to worry about name conflicts and finding the right tables. All you do is go to the correct db and then change the right table.

Reply