Export table to file using PHP

3

I would like to know if it is possible for me to export a table from the mysql database in PHP so that I can save the script to create this table in a variable.

Same as PHPmyadmin, export the table to an sql file.

    
asked by anonymous 25.09.2015 / 02:48

1 answer

2

Save bank via terminal

If you just want to make a backup, then you may not need PHP to use SSH and download the folder and then use FTP to download it, the command to back up is this (as this answer ):

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

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

Save database with PHP

You can do this using php as described in this answer 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;
    }

Use example, using it will export all tables:

include 'exportdatabase.php';

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

If you want to export specific tables, use the following:

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 a high memory consumption and added set_time_limit to prevent the server from canceling the download and added the option to change the charset. >

    
25.09.2015 / 03:30