ampersands in database fields

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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?

Lee G.
Washington DC

Re: ampersands in database fields


Quoted text here. Click to load it

That's good.

That's bad.

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

Quoted text here. Click to load it

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, '&', '&')


Site Timeline