MySQL and Irish Surnames

Cork Skate

Lads, I am trying to INSERT data into my database but i am having trouble with Irish surnames i.e. O'Brien, O'Callaghan, O'Kelly ...... it doesnt like the apostrophe !!

Is there an thing i can do about this, i was hoping to leave them as varchar .... has anyone come across this before ??

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near ''Brien,Cork,
Irish,000005),
(00000007,George,O'Callaghan,Cork,I
Query OK, 0 rows affected (0.00 sec)

ct5amr2ig1nfhp

Its not difficult to fix but I think it depends what language you are developing in?

A quick google for "<language> mysql apostrophes" should help you.

ambrose :cool:

0utshined

Use an escape character \ to allow you to put in the apostrophe.

Cork Skate

MySQL is the language !!

I replaced all the ' with \' and tried it .... got this

ERROR:
Unknown command '\''.
ERROR:
Unknown command '\''.
ERROR:
Unknown command '\''.
ERROR:
Unknown command '\''.
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near '\'Brien,Cork
,Irish,000005),
(00000007,George,O\'Callaghan,Cork
Query OK, 0 rows affected (0.00 sec)

0utshined

Are you inserting this : George,O\'Callaghan,Cork
or this : "George","O\'Callaghan","Cork" ?

Cork Skate

0utshined wrote:

Are you inserting this : George,O\'Callaghan,Cork
or this : "George","O\'Callaghan","Cork" ?

The first one !?!?!??

Is that right ??

0utshined

I don't think so, you need to quote a string IIRC (haven't done SQL in a long time).
You may need to use ' to quote rather than "

***Edit:

Two links one two

Cork Skate

0utshined wrote:

You may need to use ' to quote rather than "

I wasn't using either to be honest barr the ' for the apostrophes !!

This is a sample of the flat file ...

(00000005,Neal,Horgan,Cork,Irish,000005),
(00000006,Colin,O\'Brien,Cork,Irish,000005),
(00000007,George,O\'Callaghan,Cork,Irish,000005),
(00000008,Joe,Gamble,Cork,Irish,000005),

Cork Skate

0utshined wrote:

Are you inserting this : George,O\'Callaghan,Cork
or this : "George","O\'Callaghan","Cork" ?

Am i right in saying that i need to use something like this ... 'the_value'

Is the just for O\'Callaghan etc. or is it for everything ?

I didn't think i needed to use them for varchar's ??!!?

Random

Try:

$variablename = mysql_real_escape_string($variablename);

That should make it so that the special characters don't mess about with your MySQL statement.

(www.php.net/mysql_real_escape_string)

Cork Skate

I got this when i put all the values into ""

ERROR:
Unknown command '\Æ'.
ERROR:
Unknown command '\Æ'.
ERROR:
Unknown command '\Æ'.
ERROR:
Unknown command '\Æ'.
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near '\ÆBrienö,öCo
rkö,öIrishö,000005),
(00000007,öGeorgeö,öO\ÆCallag
Query OK, 0 rows affected (0.00 sec)

and i got this when i replaced all the " with '

ERROR:
Unknown command '\Æ'.
ERROR:
Unknown command '\Æ'.
ERROR:
Unknown command '\Æ'.
ERROR:
Unknown command '\Æ'.
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near '\ÆBrienÆ,ÆCo
rkÆ,ÆIrishÆ,000005),
(00000007,ÆGeorgeÆ,ÆO\ÆCallag
Query OK, 0 rows affected (0.00 sec)

ciaranfo wrote:

$variablename = mysql_real_escape_string($variablename);

Are you saying put that into MySQL command line (I am not using Php yet .... i am still working on the data entry in my database)

Random

Sorry, I just assumed you were using PHP. Rather stupid of me..


Just use the escapes then to put before any odd characters as suggested.

So if you have o'shea then replace it with o\'shea. Only use it where needed.

Cork Skate

ciaranfo wrote:

Sorry, I just assumed you were using PHP. Rather stupid of me..


Just use the escapes then to put before any odd characters as suggested.

So if you have o'shea then replace it with o\'shea. Only use it where needed.

I have tried that and it doesn't work ...... I tried three things tbh and none of them work ..... here there are:


1. (00000006,Colin,O\’Brien,Cork,Irish,000005),

2. (00000006,"Colin","O\’Brien","Cork","Irish",000005),

3. (00000006,'Colin','O\’Brien','Cork','Irish',000005),


And still no joy !!

Any ideas ??

Random

Shouldn't that be:

(00000006,'Colin,O\’Brien','Cork','Irish',000005),

?


NVM: you edited as I was posting

Cork Skate

ciaranfo wrote:

Shouldn't that be:

(00000006,'Colin,O\’Brien','Cork','Irish',000005),

No ... PLayerNo, FirstName, Surname, Town, Nationality, ClubNo

I need to keep the First and Surname seperate so surname is the problem value !!

jd

Cork Skate wrote:

No ... PLayerNo, FirstName, Surname, Town, Nationality, ClubNo

I need to keep the First and Surname seperate so surname is the problem value !!

PLayerNo and ClubNo may have to be in quotes too-

Random

Can you give us the various datatypes ?

Cork Skate

ciaranfo wrote:

Can you give us the various datatypes ?

What do you mean .... a few lines of data that is being used ?? sure

(00000005,Neal,Horgan,Cork,Irish,000005),
(00000006,Colin,O\'Brien,Cork,Irish,000005),
(00000007,George,O\'Callaghan,Cork,Irish,000005),
(00000008,Joe,Gamble,Cork,Irish,000005),

but it now has ' around the text.

I'll try it now around the Nos aswell

Random

No, what I mean is text, int, etc

Cork Skate

Cork Skate wrote:

I'll try it now around the Nos aswell

I tried it around all the int and still the same

ciaranfo wrote:

No, what I mean is text, int, etc

The datatypes ... you should have said !!

Here is part of the CREATE statement

MemberNo		integer		not null,
FIrstName		Varchar(32)	not null default '',
Surname			Varchar(32)	not null default '',
PlaceOfBirth		Varchar(32)	null default '',
Nationality		Varchar(32)	null default '',
ClubNo			integer		null default '',

jd

can you post up the exact sql@?

is_that_so

MySQL uses ANSI SQL and the escape used is a second apostrope.

so O'Callaghan becomes 'o''Callaghan'

your insert therefore would be

insert into my table ( Name) values ('o''Callaghan');

ct5amr2ig1nfhp

My apologies long day yesterday.

Have you tried double quotes, as is_that_so suggested?

O’Brien -> O’’Brien

ambrose :cool:

Cork Skate

kingambrose wrote:

My apologies long day yesterday.

Have you tried double quotes, as is_that_so suggested?

O’Brien -> O’’Brien

ambrose :cool:

Tried the O"Brien option ..... got this !!

ERROR 1054: Unknown column 'Michael' in 'field list'
Query OK, 0 rows affected (0.00 sec)

daveirl

This post has been deleted.

Cork Skate

Lads ... here it is ....

This is the CREATE table .........

CREATE TABLE Member(
MemberNo integer not null,
FIrstName Varchar(32) not null default '',
Surname Varchar(32) not null default '',
PlaceOfBirth Varchar(32) null default '',
Nationality Varchar(32) null default '',
ClubNo integer null default '',
INDEX ClubNo_Index (ClubNo),
CONSTRAINT MemberNo_Required PRIMARY KEY(MemberNo),
)TYPE=InnoDB;
COMMIT;

With this added later on ......

ALTER TABLE member ADD CONSTRAINT PresentClub_check FOREIGN KEY (ClubNo) REFERENCES Club(ClubNo);
COMMIT;

And this is the data i am trying to add to this table ...

INSERT INTO member VALUES
(00000001,’Michael’,’Devine’,’Cork’,’Irish’,000005),
(00000002,’Danny’,’Murphy’,’London’,’English’,000005),
(00000003,’Alan’,’Bennett’,’Cork’,’Irish’,000005),
(00000004,’Dan’,’Murray’,’Cambridge’,’Irish’,000005),
(00000005,’Neal’,’Horgan’,’Cork’,’Irish’,000005),
(00000006,’Colin’,’O\’Brien’,’Cork’,’Irish’,000005),
(00000007,’George’,’O\’Callaghan’,’Cork’,’Irish’,000005),
(00000008,’Joe’,’Gamble’,’Cork’,’Irish’,000005),
(00000009,’Liam’,’Kearney’,’Cork’,’Irish’,000005),
(00000010,’Kevin’,’Doyle’,’Wexford’,’Irish’,000005),
(00000011,’John’,’O\’Flynn’,’Cork’,’Irish’,000005),
(00000012,’Steve’,’Williams’,’Aberystwyth’,’Welsh’,000018),
(00000013,’Owen’,’Heary’,’Dublin’,’Irish’,000018),
(00000014,’David’,’Crawley’,’Dundalk’,’Irish’,000018),
(00000015,’Jamie’,’Harris’,’Swansea’,’Welsh’,000018),
(00000016,’Colin’,’Hawkins’,’Galway’,’Irish’,000018),
(00000017,’Wesley’,’Hoolahan’,’Dublin’,’Irish’,000018),
(00000018,’Ollie’,’Cahill’,’Clonmel’,’Irish’,000018),
(00000019,’Stuart’,’Byrne’,’Dublin’,’Irish’,000018),
(00000020,’Alan’,’Crawley’,’Sligo’,’Irish’,000018),
(00000021,’Glen’,’Fitzpatrick’,’Dublin’,’Irish’,000018),
(00000022,’Jason’,’Byrne’,’Dublin’,’Irish’,000018),
(00000023,’Patrick’,’Holden’,’Kilkenny’,’Irish’,000022),
(00000024,’Neil’,’Andrews’,’Kilkenny’,’Irish’,000022),
(00000025,’John’,’Frost’,’Limerick’,’Irish’,000022),
(00000026,’Kevin’,’Doherty’,’Dublin’,’Irish’,000022),
(00000027,’Pat’,’Purcell’,’Dublin’,’Irish’,000022),
(00000028,’Stephen’,’Yelverton’,’Cork’,’Irish’,000022),
(00000029,’Alan’,’Reynolds’,’Waterford’,’Irish’,000022),
(00000030,’Sean’,’Finn’,’Dublin’,’Irish’,000022),
(00000031,’Kevin’,’Waters’,’Wexford’,’Irish’,000022),
(00000032,’Daryl’,’Murphy’,’Waterford’,’Irish’,000022),
(00000033,’Willie’,’Bruton’,’Cork’,’Irish’,000022),
(00000034,’David’,’Forde’,’Galway’,’Irish’,000022),
(00000035,’Dan’,’Connor’,’Dublin’,’Irish’,000007),
(00000036,’Damien’,’Lynch’,’Dublin’,’Irish’,000007),
(00000037,’Simon’,’Webb’,’London’,’English’,000007),
(00000038,’Steven’,’Grey’,’Dublin’,’Irish’,000007),
(00000039,’Stuart’,’Malcolm’,’Edinburgh’,’Scottish’,000007),
(00000040,’Graham’,’Gartland’,’Dublin’,’Irish’,000007),
(00000041,’Shane’,’Robinson’,’Waterford’,’Irish’,000007),
(00000042,’Sami’,’Ristilla’,’Valkeakoski’,’Finnish’,000007),
(00000043,’Declan’,’O\’Brien’,’Dublin’,’Irish’,000007),
(00000044,’Mark’,’Rooney’,’Dublin’,’Irish’,000007),
(00000045,’Alan’,’Reilly’,’Dublin’,’Irish’,000007);
COMMIT;


Any ideas ??

Cork Skate

daveirl wrote:

This post has been deleted.

I am fairly close to that option now !!

But what do the French do for this though ??

J'espérerais qu'il travaillerait à cause de toutes les apostrophes qu'ils utilisent !
I would hope it would work because of all the apostrophes they use !

jd

Cork Skate wrote:

Lads ... here it is ....



INSERT INTO member VALUES
(00000001,’Michael’,’Devine’,’Cork’,’Irish’,000005),
(00000002,’Danny’,’Murphy’,’London’,’English’,000005),
(00000045,’Alan’,’Reilly’,’Dublin’,’Irish’,000007);
COMMIT;


Any ideas ??

INSERT INTO member VALUES (00000001,’Michael’,’Devine’,’Cork’,’Irish’,000005);
INSERT INTO member VALUES (00000002,’Danny’,’Murphy’,’London’,’English’,000005);

Cork Skate

jd wrote:

INSERT INTO member VALUES (00000001,’Michael’,’Devine’,’Cork’,’Irish’,000005);
INSERT INTO member VALUES (00000002,’Danny’,’Murphy’,’London’,’English’,000005);

OK .... i geddit now !!

muckwarrior

A double apostraphe is definately the answer to your original question. Make sure you use 2 apostraphes as opposed to 1 double quote.

Use '' Not "

Cork Skate

Lads, i am getting this about 45 time !!

ERROR 1054: Unknown column 'Michael' in 'field list'

It has a problem with the Firstname column !!