Posted by Rik on 07/16/07 18:58

On Fri, 13 Jul 2007 00:44:55 +0200, <2525@virgin.co.uk> wrote:

> In article <5fngvtF3cuhupU1@mid.individual.net>,
> paul.lautman@btinternet.com says...
>> 2525@virgin.co.uk wrote:
>> > I'm not very familiar with SQL - Using mySQL
>> >
>> > I need to add or remove prefixes from table names - can anyone tell=
me
>> > an SQL (or PHP for that matter) way to do this please?
>> >
>> > I can't seem to find a way to do it.
>> >
>> > thanks
>> >
>> > cleo
>>
>> Your question does not make sense. Maybe some examples of what you ar=
e
>> trying to say may help.
>>
>>
>>
>
> I need to rename all the tables in my databases by changing the first =
few
> characters of each table name:
>
> eg
> my_email to email
> my_zips to zip
>
> There are many tables and hand changing one at a time is not feasible.=

> I can't find an SQL statement that will do something like
>
> rename TABLE * where TABLENAME =3D my_* TO *

There isn't a statement in MySQL that will do that to my knowledge.

> ie dropping the my_ part.
>
> How can I do this either with SQL (prefered) or PHP if needs be.

$prefix =3D 'my_';

$regex =3D '/^'.preg_qoute($prefix,'/').'/';
$c =3D mysql_connect();
mysql_select_db($c,'database_name');
$tables =3D mysql_query('SHOW TABLES');
while($table =3D mysql_fetch_row($tables)){
if(preg_match($regex,$table[0]){
mysql_query('RENAME TABLE '.$table[0].' TO =

\''.preg_replace($regex,'',$table[0]). '\'');
}
}

-- =

Rik Wasmus

[Back to original message]