如何按名称删除多个数据库，与sql约会？

Question

The code below,list all the databases that exist and fetch them into an array,same thing for the tables of each database listed.Then it creates new databases and tables,containing every value of the older db and tables,but with th extension _backup-(a certain date).

$sql="SHOW DATABASES";
$query=mysql_query($sql,$connect);              
while ($row = mysql_fetch_assoc($query)) {
    $sql2="SHOW TABLES FROM ".$row['Database'];
    $query2=mysql_query($sql2, $connect);            
    $sql3 = "CREATE DATABASE `".$row['Database']."_backup-".$d."`";
    $query3=mysql_query($sql3,$connect);                                                    
 while( $row2 = mysql_fetch_assoc($query2) ) {   
  foreach($row2 as $rand2) { 
  $sql4 = "CREATE TABLE `".$row['Database']."_backup-".$d."`.`".$rand2."` SELECT * FROM `".$row['Database']."`.`".$rand2."`";
  $query4=mysql_query($sql4,$connect);
  }              
 }   
}

This code works as a backup,but the only thing that i cant figure out is how to drop the older databases.Lets say we trigger this script today (10.9.2014),and this code will creates a backup db1_backup-10.0.2014 for the original database named db1,if we trigger it today again,it will make backup of the previous backup,and thats a nasty thing,so i need to drop the previous backup,after the new backup is created,and keep the original db of course.I could really use your help guys,please.Thank you.

Answer 1

You can add DROP DATABASE before below code

$sql="SHOW DATABASES";
$query=mysql_query($sql,$connect);              
while ($row = mysql_fetch_assoc($query)) {
    //Blacklist these to not make backup 
    if(! in_array($row['Database'], array('mysql', 'information_schema', 'performance_schema')) && strpos($row['Database'], "_backup-".$d) === false) {
        $sql2="SHOW TABLES FROM `".$row['Database']."`;";
        $query2=mysql_query($sql2, $connect) or die(mysql_error());
        $dropDb = "DROP DATABASE IF EXISTS `".$row['Database']."_backup-".$d."`";
        mysql_query($dropDb, $connect);
        $sql3 = "CREATE DATABASE `".$row['Database']."_backup-".$d."`";
        $query3=mysql_query($sql3,$connect) or die(mysql_error());                                                    

        while( $row2 = mysql_fetch_assoc($query2) ) {   
          foreach($row2 as $rand2) { 
            $sql4 = "CREATE TABLE `".$row['Database']."_backup-".$d."`.`".$rand2."` SELECT * FROM `".$row['Database']."`.`".$rand2."`";
            $query4=mysql_query($sql4,$connect);
          }              
        }
    }
}

That way there will be only one copy of backup for same date

Update : You are getting mysql_fetch_assoc() expects parameter 1 to be resource because it was missing back ticks `` in table name of $sql2 & keep database name length below 16 character

Tested and working