Transforming XML with XSL

da_mic_1530 · 6 Nov 2008 at 10:31

Hi there again, should be a quick simple answer to this

I have a PHP page that takes my SQL database and outputs it as an XML file

I then want to transform the XML using XSL, I already have the XSL file

this is my PHP file

<?php

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

$host = "mysql4.hehe.com";
$user = "not seeing my pass";
$pass = "not ever";
$database = "nah";

$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 a5168769_cust";
$resultID = mysql_query($query, $linkID) or die("Data not found.");

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

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
$row = mysql_fetch_assoc($resultID);
$xml_output .= "\t<username>\n";
$xml_output .= "\t\t<username>" . $row['username'] . "</username>\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<password>" . $row['password'] . "</password>\n";
$xml_output .= "\t</username>\n";
}

$xml_output .= "</entries>";

echo $xml_output;

?>

I have the line

<?xml-stylesheet type="text/xsl" href="inn.xsl"?>

I need to include that somewhere in the above PHP script, whereabouts would I place it? Many thanks

da_mic_1530 · 6 Nov 2008 at 18:03

anyone? it would be hugely appreciated

ChrisB · 6 Nov 2008 at 18:14

echo it just after the header

da_mic_1530 · 7 Nov 2008 at 10:29

ChrisB said:
echo it just after the header

cheers, what am I looking at here

I've tried

echo <?xml-stylesheet type="text/xsl" href="inn.xsl"?> and that doesn't work, I'm assuming because I've used the echo command I need to look at my line of code following it but I don't know how to correctly present it

ChrisB · 7 Nov 2008 at 11:21

I'd forgot you'd echoed the xml version part later

I've just moved it upwards so you can see what I mean... feel free to place it all into $xml_output

<?php

header("Content-type: text/xml");
echo '<?xml version="1.0" encoding="UTF-8"?>\n';
echo '<?xml-stylesheet type="text/xsl" href="inn.xsl"?>\n';

$host = "mysql4.hehe.com";
$user = "not seeing my pass";
$pass = "not ever";
$database = "nah";

$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 a5168769_cust";
$resultID = mysql_query($query, $linkID) or die("Data not found.");

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

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
$row = mysql_fetch_assoc($resultID);
$xml_output .= "\t<username>\n";
$xml_output .= "\t\t<username>" . $row['username'] . "</username>\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<password>" . $row['password'] . "</password>\n";
$xml_output .= "\t</username>\n";
}

$xml_output .= "</entries>";

echo $xml_output;

?>

That's assuming you want the browser to render the xml and apply the styling.
If on the other hand you want the server to do it use xslt, e.g. http://devzone.zend.com/article/1302-Using-PHP-and-XSL-to-Transform-XML-into-Web-Content

da_mic_1530 · 7 Nov 2008 at 11:35

Can't use XLST on these server's sadly, thats why I'm going down this path...

cheers, when I post that into dreamweaver some of the colours dissapear as if the code is no longer regonised, anyway I saved it and ran it and now I'm getting

Scrap the above

You left a PHP tag in that was a note rather than an actual command, deleted it and got the colours back

Now I'm getting

The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

Invalid at the top level of the document. Error processing resource 'http://www.micholden.site50.net/outputocuk.php'. Line...

<?xml version="1.0" encoding="UTF-8"?>\n<?xml-stylesheet type="text/xsl" href="output.xsl"?>\n<br><tabl...

ChrisB · 7 Nov 2008 at 11:53

Hmmm could be your xsl stylesheet or a whitespace issue, are you trying this in IE? It might be worth having a quick check with firefox...

What does the xml output look like? and the xsl?

da_mic_1530 · 7 Nov 2008 at 11:55

The XML output looks like this when using the orginial code before we started the changes

http://www.micholden.site50.net/outputxml3.php

This is the XSL file

<?xml version="1.0" encoding="ISO-8859-1"?><xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/">
<html>
<body>
<h2>Latest News</h2>
<table border="1">
<tr bgcolor="#9acd32">
<th align="left">id</th>
<th align="left">username</th><br />
<th align="left">password</th>
</tr>
<xsl:for-each select="entries/username">
<tr>
<td><xsl:value-of select="id"/></td>
<td><xsl:value-of select="username"/></td>
<td><xsl:value-of select="password"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template></xsl:stylesheet>

Firefox gives this error when running the new code

XML Parsing Error: not well-formed
Location: http://www.micholden.site50.net/outputocuk.php
Line Number 1, Column 39:<?xml version="1.0" encoding="UTF-8"?>\n<?xml-stylesheet type="text/xsl" href="output.xsl"?>\n<entries>
--------------------------------------^

Even if we don't get this sorted I really owe you thanks for taking time out to help

ChrisB · 7 Nov 2008 at 12:00

bah its printing the new line character '\n', silly me

<?php

header("Content-type: text/xml");
echo '<?xml version="1.0" encoding="UTF-8"?>';
echo '<?xml-stylesheet type="text/xsl" href="inn.xsl"?>';

$host = "mysql4.hehe.com";
$user = "not seeing my pass";
$pass = "not ever";
$database = "nah";

$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 a5168769_cust";
$resultID = mysql_query($query, $linkID) or die("Data not found.");

$xml_output .= "<entries>\n";

for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
$row = mysql_fetch_assoc($resultID);
$xml_output .= "\t<username>\n";
$xml_output .= "\t\t<username>" . $row['username'] . "</username>\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<password>" . $row['password'] . "</password>\n";
$xml_output .= "\t</username>\n";
}

$xml_output .= "</entries>";

echo $xml_output;

?>

da_mic_1530 · 7 Nov 2008 at 12:06

After running the new code

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

--------------------------------------------------------------------------------

Only one top level element is allowed in an XML document. Error processing resource 'http://www.micholden.site50.net/output...

<script type="text/javascript">
-^

Firefox

XML Parsing Error: junk after document element
Location: http://micholden.site50.net/outputocuk3.php
Line Number 32, Column 1:<script type="text/javascript">
^

ChrisB · 7 Nov 2008 at 12:13

Looks like this is being caused by the web host inserting a tracking script

Not sure I can get around this one...

SimonCHere · 7 Nov 2008 at 12:15

It's right, you can't have 2 top level elements!
Check you XML and either remove the <script> tag, which the parser is taking as a top level element along with your <entries> element.
Or put a higher level element and change your xsl accordingly.

scratch that then. if the site is putting in tracking scripts I don't know what you can do about it.

Simon

da_mic_1530 · 7 Nov 2008 at 12:18

Right one second, I'll try it on a webspace I know not to add trackers etc

Give me 5 mins

da_mic_1530 · 7 Nov 2008 at 12:41

Brilliant, works a treat, thanks a lot guys!