Click Button To Export All Data In Database In To Myfile As .sql (Extension)

Question

There is my Function to download backup is sql format

public function actionBackup()
{
    // Set the database access credentials
    $db_host = 'localhost';
    $db_username = 'root';
    $db_password = '';
    $db_name = 'fads';
    
    // Set the backup filename and path
    $backup_file = 'backup.sql';
    $backup_path = "C:/Users/" . get_current_user() . "/Downloads/" . $backup_file;

    // Execute the mysqldump command
    $path = "C: \ xampp_7.3\mysql\bin";
    $command = "mysqldump -u {$db_username} -p{$db_password} {$db_name} > {$backup_path}";
    // echo $path.$command;die;
    exec("$path > $command");

    // Send the backup file as a download
    Yii::$app->response->sendFile($backup_path);

    // Delete the backup file
    unlink($backup_path);
}

It's download backup.sql but its empty i don't understand that where is my code wrong and why i received blank backup.sql

Answer 1

I was solved that problem there is my code

public function actionBackup()
{
    // Set the database access credentials
    $db_host = '';
    $db_username = '';
    $db_password = '';
    $db_name = '';

    // Set the backup filename and path
    $backup_file = date('Y-m-d').'.sql';
    $backup_path = 'your path' . $backup_file;

    // Connect to the database
    $dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
    $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
    $pdo = new PDO($dsn, $db_username, $db_password, $options);

    // Set the backup query
    $backup_query = "SET NAMES 'utf8';\n";
    $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tables as $table) {
        $backup_query .= "DROP TABLE IF EXISTS `$table`;\n";
        $create_table = $pdo->query("SHOW CREATE TABLE `$table`")->fetch(PDO::FETCH_COLUMN, 1);
        $backup_query .= "$create_table;\n";

        // Process data in smaller batches to avoid memory errors
        $offset = 0;
        $batch_size = 1000;
        while ($insert_rows = $pdo->query("SELECT * FROM `$table` LIMIT $offset, $batch_size")->fetchAll(PDO::FETCH_ASSOC)) {
            foreach ($insert_rows as $row) {
                $backup_query .= "INSERT INTO `$table` VALUES (";
                $values = [];
                foreach ($row as $value) {
                    $values[] = $pdo->quote($value);
                }
                $backup_query .= implode(', ', $values) . ");\n";
            }
            $offset += $batch_size;
        }
    }

    // Write the backup query to file
    file_put_contents($backup_path, $backup_query);
    Yii::$app->response->sendFile($backup_path);
    // Output the backup file as a downloadable link
    // $download_link = 'http://' . $_SERVER['HTTP_HOST'] . '/' . $backup_path;
    // echo '<p>Download your backup file <a href="' . $download_link . '">' . $backup_file . '</a></p>';

    // Delete the backup file
    unlink($backup_path);
}