Formatting XML from a MySQL database

[Bitze]

Hi, I'm trying to get the data I currently have in a table I have set up.

 

Result        Profit      Expenses    Amount

Jan            2000      1500          450

Feb            1000        200          600

Mar            1500        500          300

 

I'm trying to use php to have the data formatted so it'll have an output of:

 

<data>

<result month="Jan">            
    <profit>2000</profit>
    <expenses>1500</expenses>
    <amount>450</amount>
</result>

<result month="Feb">            
    <profit>1000</profit>
    <expenses>200</expenses>
    <amount>600</amount>
</result>

<result month="Mar">            
    <profit>1500</profit>
    <expenses>500</expenses>
    <amount>300</amount>
</result>

</data>

 

This is the current code that I have setup:

<?php 

header("Content-type: text/xml"); 

$host = "localhost"; 
$user = "root"; 
$pass = "root"; 
$database = "test"; 

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host."); 
mysql_select_db($database, $linkID) or die("Could not find database."); 

$query = "SELECT * FROM expenses"; 
$resultID = mysql_query($query, $linkID) or die("Data not found."); 

$xml_output .= "<?xml version=\"1.0\"?>\n";
$xml_output .= "<data>\n"; 

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){ 
    $row = mysql_fetch_assoc($resultID); 
    $xml_output .= "\t<entry>\n"; 
    $xml_output .= "\t\t<month>" . $row['result'] . "</month>\n"; 
        // Escaping illegal characters 
        $row['text'] = str_replace("&", "&", $row['text']); 
        $row['text'] = str_replace("<", "<", $row['text']); 
        $row['text'] = str_replace(">", ">", $row['text']); 
        $row['text'] = str_replace("\"", """, $row['text']); 
    $xml_output .= "\t\t<profit>" . $row['profit'] . "</profit>\n"; 
// Escaping illegal characters
$row['text'] = str_replace("&", "&", $row['text']); 
        $row['text'] = str_replace("<", "<", $row['text']); 
        $row['text'] = str_replace(">", ">", $row['text']); 
        $row['text'] = str_replace("\"", """, $row['text']);
    $xml_output .= "\t\t<expenses>" . $row['expenses'] . "</expenses>\n";
// Escaping illegal characters
$row['text'] = str_replace("&", "&", $row['text']); 
        $row['text'] = str_replace("<", "<", $row['text']); 
        $row['text'] = str_replace(">", ">", $row['text']); 
        $row['text'] = str_replace("\"", """, $row['text']);
    $xml_output .= "\t\t<amount>" . $row['amount'] . "</amount>\n";
// Escaping illegal characters
$row['text'] = str_replace("&", "&", $row['text']); 
        $row['text'] = str_replace("<", "<", $row['text']); 
$row['text'] = str_replace(">", ">", $row['text']); 
        $row['text'] = str_replace("\"", """, $row['text']);
    $xml_output 
} 

$xml_output .= "</data>"; 

echo $xml_output; 

?>

 

My problem is that I'm not sure how to make profit, expenses, and amount subsets of the result element. I'm also not sure how to make the months as attributes for result. Can someone help me out?

[Barand]

simplified example

$sql = "SELECT result, profit, expenses, amount
        FROM results";
$res = mysql_query($sql);

$xml_output = "<?xml version=\"1.0\"?>\n<data>\n";

while (list($r,$p,$e,$a) = mysql_fetch_row($res)) {
    $xml_output .= "<result month=\"$r\">
    <profit>$p</profit>
    <expense>$e</expense>
    <amount>$a</amount>
    </result>\n";
}

$xml_output .= "</data>\n";
header("content-type: text/xml");
echo "$xml_output";

[Bitze]

Thanks for responding!

 

I'm testing the example you provided. When I try to run the PHP, it's being recognized as an XML, but there's no document tree generated. I'm able to connect to my database fine however. Is there an option that I have to enable on my server?

 

Sorry, I'm relatively new to all of this.

[Barand]

my results:

 

  <?xml version="1.0" ?> 
- <data>
- <result month="Feb">
  <profit>1000</profit> 
  <expense>200</expense> 
  <amount>600</amount> 
  </result>
- <result month="Jan">
  <profit>2000</profit> 
  <expense>1500</expense> 
  <amount>450</amount> 
  </result>
- <result month="Mar">
  <profit>1500</profit> 
  <expense>500</expense> 
  <amount>300</amount> 
  </result>
  </data>

[Bitze]

Would it matter what datatype the fields are in the MySQL database? I'm currently using varchar.

[Barand]

I used

CREATE TABLE `results` (
`Result` varchar(3) NOT NULL,
`Profit` int(11) DEFAULT NULL,
`Expenses` int(11) DEFAULT NULL,
`Amount` int(11) DEFAULT NULL,
PRIMARY KEY (`Result`)
)

 

but when I changed it to

CREATE TABLE `results` (
  `Result` varchar(3) NOT NULL,
  `Profit` varchar( DEFAULT NULL,
  `Expenses` varchar( DEFAULT NULL,
  `Amount` varchar( DEFAULT NULL,
  PRIMARY KEY (`Result`)
)

 

I got exact same result

[Bitze]

Oh my god. I feel so stupid. I forgot to include the echo command.

 

Everything works now. Thanks for helping!

[Barand]

I could hear the DOH! from here