PHP XML parsing issue

Question

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);
?>

I get the table name but no content displayed.

JAckie
– JAckie

2011-08-22 20:41:00 +00:00
Commented Aug 22, 2011 at 20:41 — JAckie
– JAckie, Commented Aug 22, 2011 at 20:41

Andrej · Accepted Answer · 2011-08-22 20:20:39Z

2

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

answered Aug 22, 2011 at 20:20

Andrej

7,5041 gold badge22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JAckie Over a year ago

Do you know how I would output all data in the table without declaring each and every column? There are over 20.

Andrej Over a year ago

You can use the same loop like in your code but instead of $output .= "<field name=\"$row1[0]\" type=\"$row1[1]\""; use methods startElement(), writeAttribute() and so on.

Marc B · Accepted Answer · 2011-08-22 20:53:47Z

1

$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.

answered Aug 22, 2011 at 20:53

Marc B

362k44 gold badges433 silver badges508 bronze badges

2 Comments

JAckie Over a year ago

I get this error as a output now: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table name here' at line 1

Marc B Over a year ago

what's the generated query statement look like? $sql = "..."; mysql_query($sql) or die("Error " . mysql_error() . $sql);

Collectives™ on Stack Overflow

PHP XML parsing issue

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related