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@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@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 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@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
On Jul 12, 3:44 pm, 2...@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