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]

Re: ampersands in database fields

.oO(leegold2)
[color=blue]
>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 &[/color]

That's good.
[color=blue]
> When I select the field via sql I also see &[/color]

That's bad.
[color=blue]
>So what's the cause of all this? Should I upfront load my db text fields
>differently, or, search them differently[/color]

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
htmlspecialchar s().
[color=blue]
>what is the fix via mysql
>or php for the ampersand problem?[/color]

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