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

2525 [at] 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 are
trying to say may help.

In article <5fngvtF3cuhupU1 [at] mid.individual.net>,
paul.lautman [at] btinternet.com says...
> 2525 [at] 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 are
> 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 = my_* TO *

ie dropping the my_ part.

How can I do this either with SQL (prefered) or PHP if needs be.

cleo

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

> In article <5fngvtF3cuhupU1 [at] mid.individual.net>,
> paul.lautman [at] btinternet.com says...
>> 2525 [at] 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

On Jul 12, 3:44 pm, 2... [at] virgin.co.uk wrote:
>
> 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

$oldPrefix = 'my_';
$newPrefix = '';
// Connect to the server and choose the database, and then...
$result = mysql_query('SHOW TABLES');
while ($record = mysql_fetch_row($result)) {
$oldName = $record[0];
$newName = str_replace($oldPrefix, $newPrefix, $oldName);
mysql_query("RENAME TABLE $oldName TO $newName");
}

Note that using str_replace() may produce unexpected results (for
example, my_infamy_table would be renamed to infatable). If this is a
concern, you should use a regular expression instead of
str_replace().

Best regards,
NC