One database multiple applications...

[Anon]

Hi,

Lets say that I have a news-script, it uses a database to store the news content etc.

Now I want to run a secound news-script, but the table names are already used by the first script, so you can not run a secound script.

What is the best way to avoid problems like that when developing a new application?

Thx,

Duval

[[deleted]]

Simple: create a seperate database for the second script.

[Anon]

many hosts do not allow more than 1 database per account, or ask extra money, while most sites can do just fine with one database.

[[deleted]]

Then change the column names for the second script.

[tasistro]

Duval:

When I'm limited to one dbase per site I declare a "table dbase prefix" so when I build my sql command I add it to the from section in the select.

Example I have app. vincent and app duval. All in one dbase and they share the posts table

Then

// to get all posts from the vincent forum
$table_prefix="vin";

$sql_cmd="SELECT * FROM ".$table_prefix."_posts WHERE %REMAINING SQL HERE%";

// for the duval forum

$table_prefix="duv";

$sql_cmd="SELECT * FROM ".$table_prefix."_posts WHERE %REMAINING SQL HERE%";

The dbase in the site could be called duval_dbase
and the tables would be
vin_posts
and
duv_posts

By doing this you can run multiple scripts each one with its own configuratio. The config can and should be held in a file that is included in all scripts. This allows an easy copy to a new directory in case you need another aplication/pseudosite running.

Saludos
Gerardo

[[deleted]]

That is allmost exactly what I would do.

[tasistro]

vincent:

given the last time you did almost the same thing I would do (remember that left join 4 second SQL) and dropped the response time by 500% over my SQL [(0.15sec to 0.03sec)].
I WOULD REALLY LIKE TO KNOW --EXACTLY-- what you would do? 😉

Saludos
Gerardo

[[deleted]]

grin

I would do nothing really special, just define a set of constants at the top of the script, to hold the modified tablenames.

define('PRODUCTS', $prefix.'products);
define('USERS', $prefix.'users);

then in the SQL you can just type:

$sQuery = "SELECT * FROM ".PRODUCTS;

which is easier to type/read, and the constants are true globals so you don't have to import them into functions as you would with the variable.

You could even go way overboard, and write a query parser. Then you could use table names like [PRODUCTS] and have the parser replace it by the real name

$sQuery = "SELECT * FROM [PRODUCTS] WHERE this=that";

That would save you the confusion of concatenating the constants... but that is really for the bravehearts/lazy typers amung us.

see, nothing shocking about my ideas this time :-)

[Anon]

Thank you both!!

Nice PHP shootout! ;-)

I wish I had the code skillZz of only one of you,

Duval