ampersands in database fields

The subject title is a sedgeway into my question that may slightly of
topic but I've asked many sources and don't have an answer yet so I ask
it here.

I have a text fields of html marked up content which I render via php.
Looking at the rendered html page in a text editor I see that ampersands
all appear as & When I select the field via sql I also see &

I have a pretty standard seach, Fulltext and substring (ie. %keyword%)
searches. There's an acronym in my content: NA&SD and when I try
searching for this it really mucks up. The Fulltext no matter what I try
does not get a hit. The substring search will work properly if I put the
following in the search form: NA&SD that works. But trying
the substring search with NA&SD produces weird results...kinda works
but strangely and affects the rendering of the search results page.

So what's the cause of all this? Should I upfront load my db text fields
differently, or, search them differently - what is the fix via mysql
or php for the ampersand problem?

Thanks,
Lee G.
Washington DC

.oO(leegold2)

>I have a text fields of html marked up content which I render via php.
>Looking at the rendered html page in a text editor I see that ampersands
>all appear as &

That's good.

> When I select the field via sql I also see &

That's bad.

>So what's the cause of all this? Should I upfront load my db text fields
>differently, or, search them differently

The data in the database should not contain any language-specific
encodings (entities etc.), it should be in raw format. Then when you
print out data on an HTML page you just have to run it through
htmlspecialchars().

>what is the fix via mysql
>or php for the ampersand problem?

You should fix the data in MySQL.

First make a backup of the db (dump it to a file with mysqldump).

Then you could use a copy of the backup file, replace all & with &
and import the data again. Or you could login to MySQL and fix it with
some UPDATE queries, something like

UPDATE yourTable SET column = REPLACE(column, '&', '&')

HTH
Micha