[RESOLVED] XML and SQL and PHP, Oh My!

tcarnes

Sorry for the Wizard of Oz reference, but I'm trying to retain my sense of humor while trying to figure out what's going wrong.

I have XML documents which I have parsed and stored into a MS SQL2005 database. I particularly have a text field (varchar(350)) that contains data I extracted from a Node.XML. The data contains text, and some HTML entities (some by number and some by name: ie, ® ™ ® @trade). BTW, I typed in the exact HTML entity numbers for the first two, but they got immediately rendered as the symbols. The entity numbers were: & #174 and & #8482 (just remove the spaces after the ampersands).

When I use MS SQL Server Management Studio, I can see the symbols in the data (they are rendered as the symbols themselves, not as their entity numbers).

When I extract the data from the database and render it on the webpage, I end up with boxes, instead of the symbols. What happened?

I am thinking that I have stored the data properly since I can see the symbols in the database. So, is the problem with extracting the data?

Can anyone please help?

rsmith

I would look at http://www.php.net/htmlspecialchars and see if that can help you out at all.

NogDog

What charset/encoding are you using to render your web page: UTF-8, ISO-8859-1, or something else? If UTF-8, I would suggest unencoding the text then filtering it through htmlspecialchars(), as many HTML character entities do not work well with UTF-8.

echo htmlspecialchars(html_entity_decode($text));

tcarnes

Thank you for your replies, nogdog and rsmith.

I've tried a lot of different combinations of htmlspecialchars, htmlentities, html_entity_decode, etc.

The best response I've gotten is with htmlentities. Using htmlentities() I get:

[INDENT]ABILIFY<sup>®</sup> DISCMELT<sup>�</sup> [/INDENT]
where the second code is supposed to be & #8482 (TM).

Without htmlentities I get:
[INDENT]ABILIFY� DISCMELT� [/INDENT]

It seems like the special &reg is out of sync with the other html codes, and the &trade isn't getting translated at all.

As far as what charset/encoding I'm using, the lines at the top of my page are:
[INDENT]<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
<html xmlns:str="http://exslt.org/strings">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
[/INDENT]

NogDog

Just for fun (?) you might want to try changing the charset=UTF-8 to charset=ISO-8859-1 and then output the text as is (no htmlentities, etc.). You may also want to force it via the HTTP headers with the following before any text is output:

<?php
header("Content-Type: text/html;charset=ISO-8859-1");
?>

tcarnes

Thanks, NogDog, that worked!

I was just about to run an update query on the data replacing the entities I knew about with the entity names, when I received your note.

This way I'll be ready for any entity numbers thrown my way.

Thanks again!

Terry