CODEWITHSUNDEEP
phpsqlxml
JAckie
JAckie

Reputation: 21

PHP XML parsing issue

I want to be able to parse a SQL database with PHP to output an XML, however I cannot get it to show the table name and all of it's contents. I could use some help, here is my code without login information: I have defined the db server,mdb user and pass, and db name; should anything else be defined?

<?php
// connection to the database
$dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS)
   or die("Unable to connect to MySQL");

// select a database to work with
$selected = mysql_select_db(DB_NAME, $dbhandle)
   or die("Could not select data");

// return all available tables 
$result_tbl = mysql_query( "SHOW TABLES FROM "DB_NAME, $dbhandle );

$tables = array();
while ($row = mysql_fetch_row($result_tbl)) {
   $tables[] = $row[0];
}

$output = "<?xml version=\"1.0\" ?>\n";
$output .= "<schema>";

// iterate over each table and return the fields for each table
foreach ( $tables as $table ) {
   $output .= "<table name=\"$table\">";
   $result_fld = mysql_query( "SHOW FIELDS FROM "$table, $dbhandle );

   while( $row1 = mysql_fetch_row($result_fld) ) {
      $output .= "<field name=\"$row1[0]\" type=\"$row1[1]\"";
      $output .= ($row1[3] == "PRI") ? " primary_key=\"yes\" />" : " />";
   }

   $output .= "</table>";
}

$output .= "</schema>"; 

// tell the browser what kind of file is come in
header("Content-type: text/xml");
// print out XML that describes the schema
echo $output;

// close the connection
mysql_close($dbhandle);
?>

Upvotes: 2

Views: 124

Answers (2)

Marc B
Marc B

Reputation: 360662

$result_tbl = mysql_query( "SHOW TABLES FROM "DB_NAME, $dbhandle );
                                             ^^^---typo

$result_fld = mysql_query( "SHOW FIELDS FROM "$table, $dbhandle );
                                             ^^^---typo

You've got at least two typos in your queries. Most likely you want this:

$result_tbl = mysql_query("SHOW TABLES FROM " . DB_NAME, $dbhandle) or die(mysql_error());
and
$result_fld = mysql_query("SHOW FIELDS FROM $table", $dbhandle) or die(mysql_error());

Note the concatenation operator (.) on the first one, and the addition of or die(...). Never assume a query succeeds. even if the query string itself is syntactically correct, there's far too many OTHER reasons is could fail to NOT check for an error condition.

Upvotes: 1

Andrej
Andrej

Reputation: 7504

I think it's better to use standard php class XmlWriter for this one. Look at http://www.php.net/manual/en/book.xmlwriter.php

Upvotes: 2

Related Questions