ampersands in database fields

[leegold2]

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

[Michael Fesser]

.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