Export table to file using PHP

Question

I wonder if it is possible to export a mysql database table in PHP so that I can save in a variable the script to create this table.

Same as Phpmyadmin, export table to sql file.

Answer 1

Save bank via terminal

If you just want to take a backup, then maybe you don’t need PHP to use SSH and download it to the folder and then use FTP to download it, the command to back up is this (as per this answer):

mysqldump -R --user=usuario --password=senha nomedabase > arquivo.sql

It will save to your server (if you used SSH), then you will need to download sql later.

Save database with PHP

You can do this using php as described in this reply in Soen, with this function (you can create a file in include in your script):

exportdatabase.php:

<?php
    function Export_Database($host, $user, $pass, $name, $charset, $tables=false, $backup_name=false)
    {
        set_time_limit(0);

        $mysqli = new mysqli($host,$user,$pass,$name);
        $mysqli->set_charset($charset);

        $queryTables    = $mysqli->query('SHOW TABLES'); 
        while($row = $queryTables->fetch_row()) 
        { 
            $target_tables[] = $row[0]; 
        }   
        if($tables !== false) 
        { 
            $target_tables = array_intersect( $target_tables, $tables); 
        }
        foreach($target_tables as $table)
        {
            $result         =   $mysqli->query('SELECT * FROM '.$table, MYSQLI_USE_RESULT);  
            $fields_amount  =   $result->field_count;  
            $rows_num=$mysqli->affected_rows;     
            $res            =   $mysqli->query('SHOW CREATE TABLE '.$table); 
            $TableMLine     =   $res->fetch_row();
            $content        = (!isset($content) ?  '' : $content) . "\n\n".$TableMLine[1].";\n\n";

            for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0) 
            {
                while($row = $result->fetch_row())  
                { //when started (and every after 100 command cycle):
                    if ($st_counter%100 == 0 || $st_counter == 0 )  
                    {
                            $content .= "\nINSERT INTO ".$table." VALUES";
                    }
                    $content .= "\n(";
                    for($j=0; $j<$fields_amount; $j++)  
                    { 
                        $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) ); 
                        if (isset($row[$j]))
                        {
                            $content .= '"'.$row[$j].'"' ; 
                        }
                        else 
                        {   
                            $content .= '""';
                        }     
                        if ($j<($fields_amount-1))
                        {
                                $content.= ',';
                        }      
                    }
                    $content .=")";
                    //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
                    if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num) 
                    {   
                        $content .= ";";
                    } 
                    else 
                    {
                        $content .= ",";
                    } 
                    $st_counter=$st_counter+1;
                }
            } $content .="\n\n\n";
        }

        $backup_name = $backup_name ? $backup_name : $name.".sql";
        header('Content-Type: application/octet-stream');   
        header('Content-Transfer-Encoding: Binary'); 
        header('Content-disposition: attachment; filename="' . $backup_name . '"');  
        echo $content;
        exit;
    }

Example of use, thus using it will export all tables:

include 'exportdatabase.php';

Export_Database('localhost', 'root', 'senha', 'bancodedados', 'utf8');

If you want to export specific tables use so:

include 'exportdatabase.php';

$tabelas = array('tabela1', 'tabela2');
Export_Database('localhost', 'root', 'senha', 'bancodedados', 'utf8', $tabelas);

If you do not use utf8, just change the 5 parameter, for example latin1:

include 'exportdatabase.php';

Export_Database('localhost', 'root', 'senha', 'bancodedados', 'latin1');

Note I added the parameter MYSQLI_USE_RESULT to avoid high memory consumption and added set_time_limit to prevent the server from cancelling the download and added the option to change the charset.

Answer 2

What I did 1) Uninstalled the shaman 2) I installed again 3) I created the same database

Now when I will update Admin to open opencart a lot of error.

I am worried because this and Commerce is from the company as I can not use the folders already made of opencart...