Yii2 how to download database

Question

I want to download my database by clicking on a button. I have search around and found this code. yii2 database download

I have added a action on my controller and added this modified code. I've removed database connection in the original code as I guess I don't need to make the connection again. I'm getting error at 'fetch_row()'. I tried Yii::$app->db->createCommand('fetch_row')but still getting error.

define('BACKUP_DIR', 'download/' ) ; 
        // Define  Database Credentials
        define('HOST', 'localhost' ) ; 
        define('USER', 'root' ) ; 
        define('PASSWORD', 'x24613rt' ) ; 
        define('DB_NAME', 'panthoibi' ) ; 
        $files = scandir(BACKUP_DIR);
        if(count($files) > 2) {
            for ($i=2; $i < count($files); $i++) { 
                unlink(BACKUP_DIR."/".$files[$i]);
            }
        }
        /*
        Define the filename for the sql file
        If you plan to upload the  file to Amazon's S3 service , use only lower-case letters 
        */
        $fileName = 'mysqlibackup--' . date('d-m-Y') . '@'.date('h.i.s').'.sql' ; 
        // Set execution time limit
        if(function_exists('max_execution_time')) {
            if( ini_get('max_execution_time') > 0 )     
                set_time_limit(0) ;
        }

        // Check if directory is already created and has the proper permissions
        if (!file_exists(BACKUP_DIR)) mkdir(BACKUP_DIR , 0700) ;
        if (!is_writable(BACKUP_DIR)) chmod(BACKUP_DIR , 0700) ; 

        // Create an ".htaccess" file , it will restrict direct accss to the backup-directory . 
        //$content = 'deny from all' ; 
        //$file = new SplFileObject(BACKUP_DIR . '/.htaccess', "w") ;
        //$file->fwrite($content) ;


        // Introduction information //
        $return = "";
        $return .= "--\n";
        $return .= "-- A mysqli Backup System \n";
        $return .= "--\n";
        $return .= '-- Export created: ' . date("Y/m/d") . ' on ' . date("h:i") . "\n\n\n";
        $return = "--\n";
        $return .= "-- Database : " . DB_NAME . "\n";
        $return .= "--\n";
        $return .= "-- --------------------------------------------------\n";
        $return .= "-- ---------------------------------------------------\n";
        $return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
        $return .= 'SET FOREIGN_KEY_CHECKS=0 ;' ."\n" ;
        $tables = array() ; 

        // Exploring what tables this database has
        $result = Yii::$app->db->createCommand('SHOW TABLES');

        // Cycle through "$result" and put content into an array
        while ($row = $result->fetch_row()) 
            $tables[] = $row[0] ;

        // Cycle through each  table
        foreach($tables as $table)
        { 
            // Get content of each table
            $result = $mysqli->query('SELECT * FROM '. $table) ; 
            // Get number of fields (columns) of each table
            $num_fields = $mysqli->field_count  ;
            // Add table information
            $return .= "--\n" ;
            $return .= '-- Tabel structure for table `' . $table . '`' . "\n" ;
            $return .= "--\n" ;
            $return.= 'DROP TABLE  IF EXISTS `'.$table.'`;' . "\n" ; 
            // Get the table-shema
            $shema = $mysqli->query('SHOW CREATE TABLE '.$table) ;
            // Extract table shema 
            $tableshema = $shema->fetch_row() ; 
            // Append table-shema into code
            $return.= $tableshema[1].";" . "\n\n" ; 
            // Cycle through each table-row
            while($rowdata = $result->fetch_row()) 
            { 
                // Prepare code that will insert data into table 
                $return .= 'INSERT INTO `'.$table .'`  VALUES ( '  ;
                // Extract data of each row 
                for($i=0; $i<$num_fields; $i++)
                    $return .= '"'.$rowdata[$i] . "\"," ;
                 // Let's remove the last comma 
                 $return = substr("$return", 0, -1) ; 
                 $return .= ");" ."\n" ;
            } 
            $return .= "\n\n" ; 
        }

        $return .= 'SET FOREIGN_KEY_CHECKS = 1 ; '  . "\n" ; 
        $return .= 'COMMIT ; '  . "\n" ;
        $return .= 'SET AUTOCOMMIT = 1 ; ' . "\n"  ; 
        //$file = file_put_contents($fileName , $return) ; 
        $zip = new ZipArchive() ;
        $resOpen = $zip->open(BACKUP_DIR . '/' .$fileName.".zip" , ZIPARCHIVE::CREATE) ;
        if( $resOpen )
            $zip->addFromString( $fileName , "$return" ) ;
        $zip->close() ;
        $fileSize = $this->get_file_size_unit(filesize(BACKUP_DIR . "/". $fileName . '.zip')) ; 

Answer 1

You are mixing Yii query command with mysqli command this don't work ..

eg:

You are using CreateCommand

    // Exploring what tables this database has
    $result = Yii::$app->db->createCommand('SHOW TABLES');

but don't execute .. you should try

  $result = Yii::$app->db->createCommand('SHOW TABLES')->execute();

or

  $result = Yii::$app->db->createCommand('SHOW TABLES')->queryOne();

or

  $result = Yii::$app->db->createCommand('SHOW TABLES')->queryAll();

Then try take a look at the $result content eg:

 var_dump($result);

Your $result content don't shoul be adapt for

  $result->fetch_row()

but eventually you can iterate or manage the actual $result content with php function ..