将多个数据库表导出为SQL数据 - Export more than one DB Table as SQL Data

Export more than one DB Table as SQL Data

本文关键字：SQL 数据数据库 | 更新日期: 2023-09-27

我希望将11个数据库表的数据导出为XML。我轻松地导出了一张表，没有任何问题。但我真的希望出口不止一个。

我确信有一种方法，很明显可以将数据作为单独的表实体输出。任何帮助都非常感谢，因为我觉得这有点棘手。

我的代码如下

<?php
error_reporting(E_ALL);
$host       = "localhost";
$user       = "root";
$pass       = "";
$database   = "db_etch";
$table = "keywords";
$SQL_query = "SELECT * FROM $table";
$DB_link = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $DB_link) or die ("Could not find or access the database.");
$result = mysql_query ($SQL_query, $DB_link) or die ("Data not found. Your SQL query didn't work... ");
// produce XML
header("Content-type: text/xml");
$XML = "<?xml version='"1.0'"?>'n";
// root node
$XML .= "<result>'n";
// rows
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {    
  $XML .= "'t<$table>'n"; 
  $i = 0;
  // cells
  foreach ($row as $cell) {
    $cell = str_replace("&", "&amp;", $cell);
    $cell = str_replace("<", "&lt;", $cell);
    $cell = str_replace(">", "&gt;", $cell);
    $cell = str_replace("'"", "&quot;", $cell);
    $col_name = mysql_field_name($result,$i);
    $XML .= "'t't<" . $col_name . ">" . $cell . "</" . $col_name . ">'n";
    $i++;
  }
  $XML .= "'t</$table>'n"; 
 }
$XML .= "</result>'n";
// output the whole XML string
echo $XML;
// Write $sql to file   
$File = "keywords.xml";
$fh = fopen($File, 'w') or die("can't open file");
$stringData = $XML;
fwrite($fh, $stringData);
fclose($fh);
?>

我更改了您的一些代码，假设您需要一些额外的东西：

已将str_replace（）更改为htmlspecialchar（）
将xml从头移到了开头
添加了一个新的根节点

仅此而已。如果你想从某个数据库输出所有表，你应该使用"show tables；"-query来找出数据库所包含的表。

<?php
error_reporting(E_ALL);
$host       = "localhost";
$user       = "root";
$pass       = "";
$database   = "db_etch";
$table = "keywords";
$tables_to_output_array = array('keywords', 'othertable1', 'othertable2');

$DB_link = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $DB_link) or die ("Could not find or access the database.");
// produce XML
header("Content-type: text/xml");
$XML = "<?xml version='"1.0'"?>'n";
// root node
$XML .= "<tables>'n";
while (list(, $table) = each($tables_to_output_array)) {

  $SQL_query = "SELECT * FROM $table";
  $result = mysql_query ($SQL_query, $DB_link) or die ("Data not found. Your SQL query didn't work... ");
  // tables
  $XML .= "'t<$table>'n";
  // rows
  while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $XML .= "'t't<row>'n"; 
    $i = 0;
    // cells
    foreach ($row as $cell) {
      $col_name = mysql_field_name($result,$i);
      $XML .= "'t't't<" . $col_name . ">" . htmlspecialchars($cell) . "</" . $col_name . ">'n";
      $i++;
    }
    $XML .= "'t't</row>'n"; 
   }
  $XML .= "'t</$table>'n";
}
$XML .= "</tables>'n";
// output the whole XML string
echo $XML;
// Write $sql to file   
$File = "keywords.xml";
$fh = fopen($File, 'w') or die("can't open file");
$stringData = $XML;
fwrite($fh, $stringData);
fclose($fh);
?>