You are here:  » Creating prefixed tables within database for multiple install site

Active Forum Topics


Creating prefixed tables within database for multiple install site

Submitted by ChrisNBC on Fri, 2015-11-06 00:15 in

Hi David,

I’m contemplating finally migrating one of my sites to a multiple install site structure. I was reluctant to do this in the past because of the I wanted a central search function but with the mod on the download page this now looks much more straight forward and attractive.

My plan is to create multiple installs linked to one database containing prefixed tables. I wondered if you could suggest if there is any easy way to duplicate the existing set of dB tables to create a new set with a new prefix. My site is highly customised so I can’t just create a new installation and generate a standard set of tables as half the fields would be missing. Also, I’m planning to expand and create new installs in the future so any kind of solution that didn’t involve copy pasting/renaming tables would be great!

Thanks in advance.

Regards
Chris

Submitted by support on Fri, 2015-11-06 09:04

Hi Chris,

Sure - save the below as something like dbcopy.php, designed to run from top level of Price Tapestry installation:

<?php
  set_time_limit
(0);
  require(
"includes/common.php");
  
$newTablePrefix "pt2_";
  
$sql "SHOW TABLES LIKE '".$config_databaseTablePrefix."%'";
  
database_querySelect($sql,$rows);
  
$keys array_keys($rows[0]);
  
$key $keys[0];
  foreach(
$rows as $row)
  {
    
$newTable str_replace($config_databaseTablePrefix,$newTablePrefix,$row[$key]);
    
$sql "CREATE TABLE ".$newTable." LIKE ".$row[$key];
    
database_queryModify($sql,$result);
  }
  print 
"Done\n";
  exit();
?>

Before each run edit line 4 with the new table prefix required in this case:

  $newTablePrefix = "pt2_";

The uses SHOW TABLES LIKE to get the list of tables with the current $config_databaseTablePrefix of your master installation, and then issues a "CREATE TABLE name LIKE name" to replicate all the tables with the new prefix.

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by ChrisNBC on Fri, 2015-11-06 17:17

Thanks David, I just tried the script and it works perfectly. It will certainly save a lot of time!

Best regards
Chris