inserting the character ' into mysql

PickledOnion

I have a gardening pictures site where I enter info into a form such as:

common_name, description and an image. The image is renamed to ['common_name']large.jpg then copied, the copy renamed to ['common_name']small.jpg and made into a thumbnail.

Everything works brilliantly, images stored in a folder, the correct things entered into the databse.

Viewing the pictures involves a preview using the image_small.jpg. Then if you want full details, click a 'view' link and see the picture in its full glory. Everything works perfectly...

Except in Internet Explorer (surprise, surprise!). So, to the problem:

If the common_name has a ' in it, such as Iris 'Harmony', the image is named as:

Iris \ 'Harmony\ '._large.jpg (ignore the spaces, I can't post a \ next to a ' as it strips them)

Firefox, lynx, opera, epiphany, mozilla, etc handle that just fine and show the images prefectly.

Naturally, IE can't show the image as it has a \ in it!

Not having ' in the name is not an option. Not having the image renamed to the common_name is not an option due to my customer's requirements.

So how do I enter data into the database with a ' in the data and no slashes?

It won't enter the data because it has a ' in it. If I escape the ' with a \, then IE won't display the image.

Um, any ideas? I've tried everything I can think of and can find after 2 hours of searching mysql, php.net and here.

In summary, how to enter data into mysql with a ' in the data, without a \ as IE can't display the image with a \ in it?

PickledOnion

Forget that post.

I was looking at it backwards and I think I have figured it out.

I can rename the image by stripping the slashes, so it is called:

Iris 'Harmony'large.jpg but store it as Iris \ 'Harmony\ 'large.jpg

Then when I recall the image name from the table strip it again so it looks for Iris 'Harmony'_large.jpg in the image folder.

Yeah, got it now. Sorry for wasting anyones time :bemused:

uffis

I'd suggest using the SQL standard way which means writing two apostrophes in a row inside strings that are delimited by apostrophes. That way you can write more portable SQL.

MySQL supports both ways.

PickledOnion

Thanks for the advice, can you give an example of what you mean?

For example, the common name is:

Iris 'Harmony'.

Thus the image name will be:

Iris 'Harmony'_large.jpg

Can you show how to enter that in sql so that it has no \ in the entry (it can't have backslashes or IE won't show the image as it doesn't understand \ in image names)

Thanks for the help 🙂

(I've done it one way now, as described in my second post - it only took one more line of code, but would like to know a better way if there is one)

uffis

Something like this

$sql = "SELECT description FROM Images WHERE common_name LIKE 'Iris ''Harmony'' ";

or 

$sql = 'SELECT description FROM Images WHERE common_name LIKE \ 'Iris \ '\ 'Harmony\ '\ ' ';

The first example uses quotes as PHP string delimiters and single quotes (apostrophes) as SQL string delimiters.

The second example uses single quotes as PHP string and SQL string delimiters. The backslashes are needed for single quotes inside single quotes. The spaces are again there for the forum to display the code correctly.

Don't forget to check the setting magic_quotes_gpc as it escapes all single quotes, double quotes and backslashes by default.

EDIT: typos

PickledOnion

Thanks for the info 🙂