Insert isn't working

Forum Moderators: open

Message Too Old, No Replies

Insert isn't working

TravelSite

10:19 pm on Aug 15, 2011 (gmt 0)

I'm attempting to do the following in MySQL (via PHP):

INSERT INTO letters (letter) VALUES ('e')
INSERT INTO letters (letter) VALUES ('é')

...but keep getting the following message:

#1062 - Duplicate entry 'é' for key 'letter'

The "letter" field is the primary field, making me think that perhaps MySQL is thinking that the 'é' character is the same as the 'e' charter for some reason.

The value 'é' itself comes from (and tries to write to) a MyISAM table with a
latin1_swedish_ci character set ( - the default settings when I create a new table)

Can anyone advise me how to get around this or why its happening?

TravelSite

6:45 am on Aug 16, 2011 (gmt 0)

I tried usiing addslashes but it didn't help

coopster

1:13 pm on Aug 18, 2011 (gmt 0)

Create your table using a utf8 character set and collation. I do so at the database level:

CREATE DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

TravelSite

1:41 pm on Aug 18, 2011 (gmt 0)

Thanks coopster.

I did try to change just the Letter field in the table to utf8 but got the same issue.

I probably will convert the whole database to utf8 the next time I re-run my scripts on all the tables, thanks. At the moment I've just dropped the unique property of the Letter feild - which allows me to insert the two seperate versions of 'e'/'é' (and similar) without any issues.

I suspect it might result in a few issues though a script or two further ahead when I try to do SELECT statements looking for "LIKE %<character>%" for each character.