CODEWITHSUNDEEP
phpmysql
Appz Venture
Appz Venture

Reputation: 939

Generate XML from PHP via SQL

i have a error when running this code .. i run fine when there is no special characteres in database. if its has special characters then i got the error please solve me i am very thankful.

Following Error are occured when any special charachers in database like " ' & ? " i don't why those error come .. and i am not using DOM or XMLWrite just simple create the xml via file, and clerify 1 thing that CDDATA also not working for my code i check it. Please tell me some thing how could i make the xml with error less..

following are the code:

    $file= fopen("../xml/{$productID}.xml" , "w"); 


    $_xml ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>";
    $_XML = "<!DOCTYPE Server SYSTEM \"opt/pdos/etc/pdoslrd.dtd\">";

    $_xml .="<productsnode>";


     while ($row = mysql_fetch_array($productQuery, MYSQL_ASSOC)) { 

        $_xml .=" <product>";
        $_xml .="   <productID>" . $row['productID'] . "</productID>";
        $_xml .="   <productName>" . htmlspecialchars($row['productName']) . "</productName>";          
        $_xml .="   <productDescription>" . htmlspecialchars($row['productDescription']) . "</productDescription>"; 
        $_xml .="   <productPicture>" . htmlspecialchars($row['productPic']) . "</productPicture>";
        $_xml .=" <category>";
        $_xml .="   <categoryID>" . $row['categoryID'] . "</categoryID>";
        $_xml .="   <categoryName>" . htmlspecialchars($row['categoryName']) . "</categoryName>";   
        $_xml .="   <categoryDescription>" . htmlspecialchars($row['categoryDiscription']) . "</categoryDescription>";  
        $_xml .="   <categoryPicture>" . htmlspecialchars($row['categoryPic']) . "</categoryPicture>";              
        $_xml .=" <subCategory>";
        $_xml .="   <subCategoryID>" . $row['subCategoryID'] . "</subCategoryID>";
        $_xml .="   <subCategoryName>" . htmlspecialchars($row['subCategoryName']) . "</subCategoryName>";  
        $_xml .="   <subCategoryDetail>" . htmlspecialchars($row['subCategoryDescription']) . "</subCategoryDetail>";               
        $_xml .=" </subCategory>";
        $_xml .=" </category>";
        $_xml .=" </product>";

    }
    $_xml .="</productsnode>";
    fwrite($file, $_xml);
    fclose($file);

Upvotes: 0

Views: 1167

Answers (4)

Christophe
Christophe

Reputation: 4828

Special chars are illegal in xml so you need to wrap everything between CDATA tags

eg:

$_xml .="   <subCategoryName><![CDATA[" . htmlspecialchars($row['subCategoryName']) . "]]></subCategoryName>";

read more at -> http://www.w3schools.com/xml/xml_cdata.asp

your code should be something like:

$_xml .=" <product>";
        $_xml .="   <productID><![CDATA[" . $row['productID'] . "]]></productID>";
        $_xml .="   <productName><![CDATA[" . htmlspecialchars($row['productName']) . "]]></productName>";          
        $_xml .="   <productDescription><![CDATA[" . htmlspecialchars($row['productDescription']) . "]]></productDescription>"; 
        $_xml .="   <productPicture><![CDATA[" . htmlspecialchars($row['productPic']) . "]]></productPicture>";
        $_xml .=" <category>";
        $_xml .="   <categoryID><![CDATA[" . $row['categoryID'] . "]]></categoryID>";
        $_xml .="   <categoryName><![CDATA[" . htmlspecialchars($row['categoryName']) . "]]></categoryName>";   
        $_xml .="   <categoryDescription><![CDATA[" . htmlspecialchars($row['categoryDiscription']) . "]]></categoryDescription>";  
        $_xml .="   <categoryPicture><![CDATA[" . htmlspecialchars($row['categoryPic']) . "]]></categoryPicture>";              
        $_xml .=" <subCategory>";
        $_xml .="   <subCategoryID><![CDATA[" . $row['subCategoryID'] . "]]></subCategoryID>";
        $_xml .="   <subCategoryName><![CDATA[" . htmlspecialchars($row['subCategoryName']) . "]]></subCategoryName>";  
        $_xml .="   <subCategoryDetail><![CDATA[" . htmlspecialchars($row['subCategoryDescription']) . "]]></subCategoryDetail>";               
        $_xml .=" </subCategory>";
        $_xml .=" </category>";
        $_xml .=" </product>";

If that doesn't work it means there is something else, I suggest you copy the error you get and put it in your answer and maybe take a screenshot of what is stored in the database. If you want help give us more details

Upvotes: 1

Cups
Cups

Reputation: 6896

I am not really answering your question exactly, but perhaps you should take a look at mysql xml abilities. This may depend upon your mysql version, and especially if you could possibly use the CLI methods to access your sql.

The article mentioned does describe the slightly more convoluted means of getting xml via sql selects though which worked for me on 5.0.67 (win32).

Upvotes: 0

Mamsaac
Mamsaac

Reputation: 6273

It is VERY very difficult to tell when you're not posting the exact errors. However, I will try to do an avid guess: check encodings. It's likely that you're using latin-swedish (AKA-ISO 8859-1) on the database and utf8 for the rest... or viceversa. I would recommend using utf8 for everything and saving your time.

Check table encodings, then the connection encoding (mysql_* functions by default could do the connection using ISO-8859-1 and you should use http://php.net/manual/en/function.mysql-set-charset.php in case that is happening), and that should help.

ALSO: Why are you concatenating strings for generating XML? It is a much better idea to do it with proper XML libraries like SimpleXML (please notice there's other libraries, you can check XMLWriter and many others too). Links: http://mx.php.net/manual/en/book.simplexml.php and ... I can't write the other link because I'm a newb in here :) Google is everyone's friend.

If you go to PEAR, there's more stuff.

Upvotes: 0

konsolenfreddy
konsolenfreddy

Reputation: 9671

wrap your XML in CDATA 

<![CDATA[your content here]]>

(This will also take care of specialchars which are not replaced by htmlspecialchars())

Upvotes: 2

Related Questions