An easy way to change a table name?

swatisonee

Hi,

I inherited this db from someone and find the most used tables also have the most complicated table names - a combination of word,underscore,hyphen and number.

As a result several of the php files which link to them have errors because something or the other is wrong in the table name.

Now if i rename the table thru phpmyadmin, i am left with the task of manually changing almost 100 odd scripts and i fear that solution may be worse than the problem .

Does anyone know of a way or some php script that will replace the table name in ALL scripts at one shot ?

Thanks. Swati

Rodney_H_

Ah, not sure, but a great argument for assiging table name values to variables used in the scripts, so if they DO change, you only have to change it in one place: where you assign the variable it's value, usually in the db.config.php file...

If all the table names have the same structure, ex: "table_name-1" that you want to change to "name", and you are good at regular expressions, you could write a script to open up all the files in a directory in READ moe, save the data to a variable, remove the unwanted characters, and re-write that file using the "W" mode....

But if you were to attempt that, make sure you make a backup of the files, first, just in case...

Really seems like a pain in the @$$ though. I feel for you, dude!

MarkR

It's not difficult to replace the table name everywhere. Simply use the search & replace facility in your editor.

Then take care to test every page which has changed (or is affected by the changes in other files).

Doing all of this in your development environment should be very easy. You should probably have some kind of test plan for just this kind of eventuality. If it's a non-automated test procedure however, you'll probably only use it occasionally.

As a side note, unless your site is INCREDIBLY complicated, 100 scripts is too many. No reasonable sized web site requires that many.

Defining constants for your table names is a waste of time and will convolute your application (if you rename a table to something different from the constant).

Clearly you should plan this change very well. After you've tested your application in your development environment (after you've carried out all the scripted database ALTER TABLE scripts), you should have a procedure whereby you can push the changes into the production server while the site's shut down, to avoid errors during the changeover period. Then you can perform a final test on the production server to make it's all worked, before reopening the site to the public.

Mark

Rodney_H_

MarkR wrote:
Defining constants for your table names is a waste of time and will convolute your application (if you rename a table to something different from the constant).

@: With all due respect, your advise and comments are great. I agree with all except the quoted excerpt. If this had been done, if his table names had been assigned to variables or defined as constants, then this thread would be moot, right? The minute it would take to do that, would, now, and in cases like this, most certainly not be a waste of time, in my humble opinion. I mean, people save database names, users and passwords in their config file, right? Why not table names?

swatisonee

Rodney H , MarkR , thank you both !

a) The 100 scripts could probably have been 50 .

Example : I have update_customer.php ( select a customer by alphabet ) and then update_customer-1.php which chooses a customer and then a third, update_customer_chosen.php and finally update_customer_chosen-process.php that runs the sql !

The whole site is full of such stuff for product names, models, specs etc !

Then take care to test every page which has changed (or is affected by the changes in other files).

This is what i am afraid off - even assuming i start alphabetically, theres always a chance some darned script gets left out .

c)Umm.. I save my db names etc in a file called protect.php but dont kow how that would work?

If say , in update_customer_chosen-process.php

I have a statement :

$sql= "UPDATE `ALL_CUSTOMERS_STATE-WISE` SET  `Company` = '$company'" "
and so on...

how would that table name get pulled out from protect.php ?

maxtech

:rolleyes:

maxtech

declare all the table names as constants in protect.php
for example:

/
Database Table Constants
*/

define("TBL_COMP", "tblComp");
define("TBL_PRODUCT", "tblProd");
.
.
.

and just include this page wherever u want and mention the constant name instead of the original table name

"UPDATE ".TBL_COMP." SET Company = '$company'"