I searched over SO and found a few similar topics with my issue. Thanks to those topics I was able to write a function which renames the prefix of my tables and their columns.

Currently my script works only for my tables. All tables with the specific prefix will be renamed so a table called "PREFIX_users" will be only "users" once I start my function. My problem is now, that the function for all my columns inside the tables is not working. I do not get any php error/warnings, just my columns stay with the same name.

Here is my code:

/*
 * Rename tables and columns inside sysrec
 * @param db name
*/
public function renameTablesColumns($database) {
    $this->db->prepare("use ".$database."");
    $sqlShowSORTables = "SHOW TABLES ";
    $statement = $this->db->prepare($sqlShowSORTables);
    $statement->execute();
    $SORtables = $statement->fetchAll(PDO::FETCH_NUM);

    foreach($SORtables as $renameTable){

        $new_table_prefix = '';
        $old_table_prefix = 'PRAEFIX_';

        $old_table = $renameTable[0];
        if(!preg_match('/^'.$old_table_prefix.'/', $old_table)) {
            $new_table=$old_table;
        } else {
            $new_table = preg_replace('/^'.$old_table_prefix.'/', $new_table_prefix, $old_table);
            $sql[] = "ALTER TABLE ".$database.".".$old_table." RENAME TO ".$database.".".$new_table."; ";
        }

        $sqlShowSORColumns = "SHOW COLUMNS FROM ".$renameTable[0]." ";
        $statement = $this->db->prepare($sqlShowSORColumns);
        $statement->execute();
        $columns = $statement->fetchAll(PDO::FETCH_NUM);

        foreach($columns as $renameColumn) {
            $new_column_prefix = '';
            $old_column_prefix = 'PRAEFIX_';

            $old_column = $renameColumn[0];
            if(!preg_match('/^'.$old_column_prefix.'/', $old_column)) {
                $new_column=$old_column;
            } else {
                $new_column = preg_replace('/^'.$old_column_prefix.'/', $new_column_prefix, $old_column);
                $sql[] = "ALTER TABLE ".$database.".".$old_table." RENAME COLUMN ".$old_column." TO ".$new_column."; ";
            }
        }
    }

    $sqlState = implode(' ', $sql);

    $executeStatement = $this->db->exec($sqlState);
    return $executeStatement?$executeStatement:false;
}

Any help would be really appreciated.

0 Answers