Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts

    Renaming all DB tables

    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?
    PHP Code:
    //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);


  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    try this;

    PHP Code:
    mysql_query("RENAME TABLE " $table " TO 'smf_" $table "'" ); 
    .
    Last edited by angst; 03-16-2010 at 08:40 PM.

  • #3
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    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.

  • #4
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    you might not have correct permissions to do this,

    or try adding this into the rename query:

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

  • #5
    New Coder
    Join Date
    Mar 2010
    Posts
    26
    Thanks
    3
    Thanked 1 Time in 1 Post
    Code:
    mysql_query("RENAME TABLE `".$table."` TO `smf_".$table."`" );

  • #6
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    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.
    Code:
    [3] => cards
        [4] => chat
        [5] => config
        [6] => continents
        [7] => countries

  • #7
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Try:

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

  • #8
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    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?

    PHP Code:
    $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()); ;


  • #9
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Ditch the second semicolon from the end of the line, and enable error reporting and display errors.

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

  • #10
    New Coder
    Join Date
    Mar 2010
    Posts
    26
    Thanks
    3
    Thanked 1 Time in 1 Post
    The easy way to figure out this kind of error, instead of executing the query, print it out:

    Code:
    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.

  • #11
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    yeah, that does seem to be the easiest way. Just doing it right in phpmyadmin
    Last edited by ptmuldoon; 03-17-2010 at 04:48 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •