-2

So basically I have a database in MySQL which has a bunch of tables that have many diffrent names for example 'table_data' I would like to copy and rename all of these tables by adding for example '_city' so it will be: 'table_data_city' Any ideas about how to do this with a simple script? notice i want to copy the structure only.

Bibhudatta Sahoo
  • 4,808
  • 2
  • 27
  • 51
Sandra Lopez
  • 107
  • 2
  • 10
  • no, i have no idea how to do it, i know how to do it manually in phpmyadmin but it wil take so much time, cuz i have many tables... i was wondering if there is a way to do it automatically. – Sandra Lopez Aug 01 '17 at 07:24
  • Wow, a database with a bunch of tables, who would have imagined?! – Difster Aug 01 '17 at 07:25
  • what do you mean? – Sandra Lopez Aug 01 '17 at 07:25
  • How many tables is many? It may be quicker to export the table structure, open your editor and add `_city` to each, rather than write a script. – Dave Aug 01 '17 at 07:26
  • around 120 table , and it wouldnt work because it has many diffrent names, that means i will have to type the new name and search for it to replace it, it would be same as doin it in phpmyadmin – Sandra Lopez Aug 01 '17 at 07:31
  • yes i want to keep the name and add a string to it, for example table_data i rename it to table_data_city – Sandra Lopez Aug 01 '17 at 07:42
  • yes sir, they will have the same previous name and we add _city to them..for example: table_data = table_data_city, table_log = table_log_city.... and then i will have to do... table_data = table_data_city2...it would take forever to do it manually – Sandra Lopez Aug 01 '17 at 07:47
  • Possible duplicate of [Remove prefix from all mysql table names of a database](https://stackoverflow.com/questions/35103436/remove-prefix-from-all-mysql-table-names-of-a-database) – OfirD Aug 01 '17 at 07:47

1 Answers1

0

try this php code

<?php
$conn=mysqli_connect("localhost","root","","db_name");
$query="SELECT table_name FROM information_schema.tables where table_schema='db_name';";

$result=mysqli_query($conn,$query);
$tables=array();
while ($row=mysqli_fetch_array($result)) {
    $query="RENAME TABLE ".$row[0]." TO ".$row[0]."_city";
    mysqli_query($conn,$query);
}
Bhargav Chudasama
  • 6,928
  • 5
  • 21
  • 39
  • ty so much , your script doesnt copy but it changes the name , so i will just copy the tables in diffrent database and then rename them , and then join them to the original one. i have a little problem , it doesnt change the name of tables with these names :"table-log" but if its 'table_log' it works, any solution? – Sandra Lopez Aug 01 '17 at 08:10
  • fixed it was just quotes problem ; $query=' RENAME TABLE `'.$row[0].'` TO `'.$row[0].'_city`'; ty alot! – Sandra Lopez Aug 01 '17 at 08:34