...

View Full Version : Renaming all DB tables



ptmuldoon
03-16-2010, 08:24 PM
I'm trying to have a quick way to rename all tables in a database to by appending a prefix on to them. I thought the below would work, yet the the table names aren't being updated.

Is this correct?


//Query all tables in the database
$tables = array();
$rows = mysql_query("SHOW TABLES FROM mydatabase");

while ($row = mysql_fetch_array($rows)) {
$tables[] = $row[0];

}
//Tables Before Name Change
print_r($tables);

//Append and Rename all tables in a database
foreach($tables as $table){
mysql_query('RENAME TABLE' .$table . ' TO smf_'.$table);
}

angst
03-16-2010, 08:36 PM
try this;



mysql_query("RENAME TABLE " . $table . " TO 'smf_" . $table . "'" );
.

ptmuldoon
03-16-2010, 08:48 PM
Strangely, that didn't change them either. No errors returned, but still nothing being changed.

I have also echo'd out the array and it shows all the table names correctly, thus I see the names before the attempt to change them ok.

angst
03-16-2010, 09:02 PM
you might not have correct permissions to do this,

or try adding this into the rename query:

or die(mysql_errno() . ": " . mysql_error());

Goldfish
03-16-2010, 09:32 PM
mysql_query("RENAME TABLE `".$table."` TO `smf_".$table."`" );

ptmuldoon
03-16-2010, 10:02 PM
Again, neither seems to work. I added in the or die to the query, and still no errors of any kind. The array being generated looks like this as well, yet the tables continue to not get renamed.

I doing this on a local WAMP install, and have full user permissions.

snippet of the array.


[3] => cards
[4] => chat
[5] => config
[6] => continents
[7] => countries

MattF
03-16-2010, 10:22 PM
Try:



mysql_query('RENAME TABLE \''.$table.'\' TO \'smf_'.$table.'\'');

ptmuldoon
03-16-2010, 10:34 PM
Its so strange, but they still don't change to the new name. Here's the entire, tiny script, and my WAMP install is using php 5.3.0 and apache 2.2.11. Could it somehow be permission related, or php or apache setting/extension not enabled?



$hostname="localhost";
$mysql_login="username";
$mysql_password="password";
$database="mydb";

// connect to the database server
if (!($db = mysql_pconnect($hostname, $mysql_login , $mysql_password))){
die("Can't connect to database server.");
}else{
// select a database
if (!(mysql_select_db("$database",$db))){
die("Can't connect to database.");
}
}

//Query all tables in the database
$tables = array();
$rows = mysql_query("SHOW TABLES FROM mydb") or die(mysql_errno() . ": " . mysql_error());;

while ($row = mysql_fetch_array($rows)) {
$tables[] = $row[0];

}
//Tables Before Name Change
echo '<pre>';
print_r($tables);
echo '</pre>';

//Append and Rename all tables in a database
foreach($tables as $table){
mysql_query('RENAME TABLE \''.$table.'\' TO \'smf_'.$table.'\'');
//mysql_query("RENAME TABLE '".$table."' TO 'smf_".$table."'" ) or die(mysql_errno() . ": " . mysql_error()); ;
}

MattF
03-16-2010, 10:44 PM
Ditch the second semicolon from the end of the line, and enable error reporting and display errors.



$rows = mysql_query("SHOW TABLES FROM mydb") or die(mysql_errno() . ": " . mysql_error());;

Goldfish
03-16-2010, 11:11 PM
The easy way to figure out this kind of error, instead of executing the query, print it out:



print "RENAME TABLE `".$table."` TO `smf_".$table."`<br>";


Then go to PHPMyAdmin, click "Sql" and copy and paste the command in.
PHPMyAdmin will show you what´s wrong.

ptmuldoon
03-17-2010, 04:44 PM
yeah, that does seem to be the easiest way. Just doing it right in phpmyadmin



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum