Export more than one DB Table as SQL Data

[stublackett]

0 down vote favorite

 

 

I'm looking to export 11 Database tables' data as XML. I've easily managed to export one table, without issue. But I am looking to export more than one really.

 

I'm sure theres a way and obviously output the data as seperate table entitys. Any help is much appreciated on this one, As I'm finding it a little tricky.

 

My code is as follows

 

<?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("&", "&", $cell);
    $cell = str_replace("<", "<", $cell);
    $cell = str_replace(">", ">", $cell);
    $cell = str_replace("\"", """, $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);

?>

[TeNDoLLA]

Just make an array holding all the tables you want to create the XML from, and then loop through this array and create inside the loop the XML files. If you need to query the database for the table names and create the array that way, you can use the SHOW TABLE syntax with or without wildcards to get the desired table names.

 

$tables = array('keywords', 'users', 'test_table');
foreach ($tables as $table)
{
// Create XML for each table in the array. 
}

[stublackett]

Thats perfect, Thanks!

 

I've been looking at an array for this. I've managed to piece it together for each table.

 

The SHOW TABLE syntax may come in handy at a later date, as I may need to export the relationships etc.

 

Cheers