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.
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.
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.
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 addedset_time_limit
to prevent the server from canceling the download and added the option to change the charset. >