【php-mysql】导出表数据

<?php
if (!function_exists('mysqli_connect')) {
    if (DIRECTORY_SEPARATOR == '/') {
        dl('php_mysqli.so');
    } else {
        dl('php_mysqli.dll');
    }
}
$database = null;
if (isset($argv[1])) {
    $database = $argv[1];
} else {
    display_help();
    exit();
}
$optional_args = array(
    '-h' => 'hostname',
    '-c' => 'charset',
    '-f' => 'filename',
    '-u' => 'username' 
);
$options = array(
    'hostname' => 'localhost',
    'charset' => 'utf8',
    'filename' => '%s.sql',
    'username' => 'root' 
);
$input_password = false;
for($i = 2; $i < $argc; $i++) {
    $arg = $argv[$i];
    if ($arg == '-p') {
        $input_password = true;
        continue;
    }
    if (isset($optional_args[$arg])) {
        $value_name = $optional_args[$arg];
        if (isset($argv[$i + 1])) {
            $options[$value_name] = $argv[$i + 1];
            $i++;
        }
    }
}
if ($input_password) {
    echo "password: ";
    fscanf(STDIN, '%s', $password);
    $options['password'] = $password;
    echo "\n";
} else {
    $options['password'] = '';
}
if ($database == null) {
    display_help();
    exit();
}

$con = mysqli_connect($options['hostname'], $options['username'], $options['password'], $database);
$con->set_charset($options['charset']);
// 设置要导出的表
$tables = list_tables($database, $con);
$filename = sprintf($options['filename'], $database);
$fp = fopen($filename, 'w');
foreach ( $tables as $tableArr ) {
    foreach ( $tableArr as $table ) {
        dump_table($table, $fp, $con);
    }
}
fclose($fp);
mysqli_close($con);
echo "done.\n";
exit();

function list_tables($database, $con) {
    $rs = $con->query("SHOW TABLES");
    $tables = array();
    while ( $row = mysqli_fetch_assoc($rs) ) {
        $tables[] = $row;
    }
    mysqli_free_result($rs);
    return $tables;
}

function dump_table($table, $fp = null, $con) {
    $need_close = false;
    if (is_null($fp)) {
        $fp = fopen($table . '.sql', 'w');
        $need_close = true;
    }
    fwrite($fp, "-- \n-- {$table}\n-- \n");
    $rs = $con->query("SELECT * FROM `{$table}`");
    while ( $row = mysqli_fetch_assoc($rs) ) {
        fwrite($fp, get_insert_sql($table, $row, $con));
    }
    mysqli_free_result($rs);
    if ($need_close) {
        fclose($fp);
    }
    fwrite($fp, "\n\n");
}

function get_insert_sql($table, $row, $con) {
    $sql = "INSERT INTO `{$table}` VALUES (";
    $values = array();
    foreach ( $row as $value ) {
        $values[] = "'" . $con->real_escape_string($value) . "'";
    }
    $sql .= implode(', ', $values) . ");\n";
    return $sql;
}

function display_help() {
    echo <<<EOT
syntax:
php export_db.php database [-h hostname] [-c charset] [-f filename] [-u username] [-p] 
defualt hostname : localhost 
default charset : utf8 
default username : root 
default password : (none) 
default filename : [database].sql 
EOT;
}