MySQL and Irish Surnames

jd

Cork Skate wrote:

Lads, i am getting this about 45 time !!

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

It has a problem with the Firstname column !!

INSERT INTO member ( MemberNo, FIrstName , Surname, PlaceOfBirth, Nationality , ClubNo )VALUES (00000001,’Michael’,’Devine’,’Cork’,’Irish’,000005);

Cork Skate

And do it like this .....

INSERT INTO member ( MemberNo, FIrstName , Surname, PlaceOfBirth, Nationality , ClubNo )VALUES (00000001,’Michael’,’Devine’,’Cork’,’Irish’,000005);
(00000002,’Danny’,’Murphy’,’London’,’English’,000005);

muckwarrior

Cork Skate wrote:

And do it like this .....

Nope. One set of values for each INSERT statement.

Cork Skate

Alright ...... i was posting and didn't read fully

INSERT INTO member ( MemberNo, FIrstName , Surname, PlaceOfBirth, Nationality , ClubNo ) VALUES
(00000001,’Michael’,’Devine’,’Cork’,’Irish’,000005);
INSERT INTO member ( MemberNo, FIrstName , Surname, PlaceOfBirth, Nationality , ClubNo ) VALUES
(00000002,’Danny’,’Murphy’,’London’,’English’,000005);


and so on !!

muckwarrior

Redundant

Cork Skate

Cork Skate wrote:

INSERT INTO member ( MemberNo, FIrstName , Surname, PlaceOfBirth, Nationality , ClubNo ) VALUES
(00000001,’Michael’,’Devine’,’Cork’,’Irish’,000005);
INSERT INTO member ( MemberNo, FIrstName , Surname, PlaceOfBirth, Nationality , ClubNo ) VALUES
(00000002,’Danny’,’Murphy’,’London’,’English’,000005);

Lads .... i am still getting this

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

on for each row !!

Cork Skate

Check this one out !!!

’ is not the same as '

I was using Microsoft Word to 'replace' and then paste back into notepad

And it didn't say anything about the \' so it must be good (if not i'll try \ ' + ')



I still have a problem ........

ERROR 1216: Cannot add or update a child row: a foreign key constraint fails


How do i update a column if i dont have the other on updated yet ??

steveland?

I know you're not at this point yet but it might be helpful for you to become accustomed to using addslashes and stripslashes when you begin using php with your database (as I assume you will be doing)

This way when someone enters something into your database (again this is just assuming you're not going to be inputting all the details yourself) it handles if someone uses a ' or is quoting something using ""'s

http://ie2.php.net/addslashes

Again it's off-topic to an extent but it's handy to know when handling names

muckwarrior

Have you tried Member instead of member? You have it capitalized in your Create. Can't remember offhand how case sensitive MySQL is but you should always keep it the same anyway.

Caixa

Your code worked perfectly on my mysql install. This is what phpmyadmin suggests to enter it, you may as well give it a go:

INSERT INTO `member` VALUES (1, 'Michael', 'Devine', 'Cork', 'Irish', 5);
INSERT INTO `member` VALUES (2, 'Danny', 'Murphy', 'London', 'English', 5);
INSERT INTO `member` VALUES (3, 'Alan', 'Bennett', 'Cork', 'Irish', 5);
INSERT INTO `member` VALUES (4, 'Dan', 'Murray', 'Cambridge', 'Irish', 5);
INSERT INTO `member` VALUES (5, 'Neal', 'Horgan', 'Cork', 'Irish', 5);
INSERT INTO `member` VALUES (6, 'Colin', 'O\'Brien', 'Cork', 'Irish', 5);
INSERT INTO `member` VALUES (7, 'George', 'O\'Callaghan', 'Cork', 'Irish', 5);
INSERT INTO `member` VALUES (8, 'Joe', 'Gamble', 'Cork', 'Irish', 5);
INSERT INTO `member` VALUES (9, 'Liam', 'Kearney', 'Cork', 'Irish', 5);
INSERT INTO `member` VALUES (10, 'Kevin', 'Doyle', 'Wexford', 'Irish', 5);
INSERT INTO `member` VALUES (11, 'John', 'O\'Flynn', 'Cork', 'Irish', 5);
INSERT INTO `member` VALUES (12, 'Steve', 'Williams', 'Aberystwyth', 'Welsh', 18);
INSERT INTO `member` VALUES (13, 'Owen', 'Heary', 'Dublin', 'Irish', 18);
INSERT INTO `member` VALUES (14, 'David', 'Crawley', 'Dundalk', 'Irish', 18);
INSERT INTO `member` VALUES (15, 'Jamie', 'Harris', 'Swansea', 'Welsh', 18);
INSERT INTO `member` VALUES (16, 'Colin', 'Hawkins', 'Galway', 'Irish', 18);
INSERT INTO `member` VALUES (17, 'Wesley', 'Hoolahan', 'Dublin', 'Irish', 18);
INSERT INTO `member` VALUES (18, 'Ollie', 'Cahill', 'Clonmel', 'Irish', 18);
INSERT INTO `member` VALUES (19, 'Stuart', 'Byrne', 'Dublin', 'Irish', 18);
INSERT INTO `member` VALUES (20, 'Alan', 'Crawley', 'Sligo', 'Irish', 18);
INSERT INTO `member` VALUES (21, 'Glen', 'Fitzpatrick', 'Dublin', 'Irish', 18);
INSERT INTO `member` VALUES (22, 'Jason', 'Byrne', 'Dublin', 'Irish', 18);
INSERT INTO `member` VALUES (23, 'Patrick', 'Holden', 'Kilkenny', 'Irish', 22);
INSERT INTO `member` VALUES (24, 'Neil', 'Andrews', 'Kilkenny', 'Irish', 22);
INSERT INTO `member` VALUES (25, 'John', 'Frost', 'Limerick', 'Irish', 22);
INSERT INTO `member` VALUES (26, 'Kevin', 'Doherty', 'Dublin', 'Irish', 22);
INSERT INTO `member` VALUES (27, 'Pat', 'Purcell', 'Dublin', 'Irish', 22);
INSERT INTO `member` VALUES (28, 'Stephen', 'Yelverton', 'Cork', 'Irish', 22);
INSERT INTO `member` VALUES (29, 'Alan', 'Reynolds', 'Waterford', 'Irish', 22);
INSERT INTO `member` VALUES (30, 'Sean', 'Finn', 'Dublin', 'Irish', 22);
INSERT INTO `member` VALUES (31, 'Kevin', 'Waters', 'Wexford', 'Irish', 22);
INSERT INTO `member` VALUES (32, 'Daryl', 'Murphy', 'Waterford', 'Irish', 22);
INSERT INTO `member` VALUES (33, 'Willie', 'Bruton', 'Cork', 'Irish', 22);
INSERT INTO `member` VALUES (34, 'David', 'Forde', 'Galway', 'Irish', 22);
INSERT INTO `member` VALUES (35, 'Dan', 'Connor', 'Dublin', 'Irish', 7);
INSERT INTO `member` VALUES (36, 'Damien', 'Lynch', 'Dublin', 'Irish', 7);
INSERT INTO `member` VALUES (37, 'Simon', 'Webb', 'London', 'English', 7);
INSERT INTO `member` VALUES (38, 'Steven', 'Grey', 'Dublin', 'Irish', 7);
INSERT INTO `member` VALUES (39, 'Stuart', 'Malcolm', 'Edinburgh', 'Scottish', 7);
INSERT INTO `member` VALUES (40, 'Graham', 'Gartland', 'Dublin', 'Irish', 7);
INSERT INTO `member` VALUES (41, 'Shane', 'Robinson', 'Waterford', 'Irish', 7);
INSERT INTO `member` VALUES (42, 'Sami', 'Ristilla', 'Valkeakoski', 'Finnish', 7);
INSERT INTO `member` VALUES (43, 'Declan', 'O\'Brien', 'Dublin', 'Irish', 7);
INSERT INTO `member` VALUES (44, 'Mark', 'Rooney', 'Dublin', 'Irish', 7);
INSERT INTO `member` VALUES (45, 'Alan', 'Reilly', 'Dublin', 'Irish', 7);

jd

Cork Skate wrote:

Check this one out !!!

’ is not the same as '

I was using Microsoft Word to 'replace' and then paste back into notepad

And it didn't say anything about the \' so it must be good (if not i'll try \ ' + ')



I still have a problem ........

ERROR 1216: Cannot add or update a child row: a foreign key constraint fails


How do i update a column if i dont have the other on updated yet ??

ok - you mean you were quoting with ` instead of ' ?

do the inserts fail on the commit? can you mot satisy the constarint before the commit?

steveland?

Cork Skate wrote:

I was using Microsoft Word to 'replace' and then paste back into notepad

You know notepad has a replace function (Ctrl+h), this might be more helpful because it uses plaintext whereas Word tends to add those dodgy line break symbols which can be a pain.

Cork Skate

steveland? wrote:

I know you're not at this point yet but it might be helpful for you to become accustomed to using addslashes and stripslashes when you begin using php with your database (as I assume you will be doing)

This way when someone enters something into your database (again this is just assuming you're not going to be inputting all the details yourself) it handles if someone uses a ' or is quoting something using ""'s

http://ie2.php.net/addslashes

Again it's off-topic to an extent but it's handy to know when handling names

Steve ... thanks for that ..... yup ... i'll be using Php

That will ( echo addslashes($str); ) be on the webpage i take it when taking in the user input ??

steveland?

When someone is adding their details you'll be using a form.

So when they submit it you want to declare variables for each element of the form,

ie

$name = $_POST['name']

This means you're going to be taking the form field named name and assigning it to $name

when you go to insert that into your table your code should look something like this

$name = $_POST['name'];
$name = addslashes($name);
$sql = "INSERT INTO table VALUES ("$name");

and then when you pull info from the database you'll want to use something like

$sql = "SELECT * from table";

--- some other code which allows you to assign variables to the fields you pull which i won't go into here ---

$name = stripslashes($name);
echo "$name";

This means if you input a name like John O'Reilly it'll put it into the db as John O\'Reilly and when it takes it out it'll convert it back to John O'Reilly

0utshined

muckwarrior wrote:

Some others suggested the \' to solve your problem. I don't want to call them liars but to my knowledge this won't work. Use the '' like I said above.

Muckwarrior, I believe this was me but I did warn that I hadn't done SQL in a while. That's the escape character for C\C++ and this link from the MySQL homepages suggests it should work.


OP,

Ditch Notepad and Word and use something like Textpad.

steveland?

0utshined wrote:

Ditch Notepad and Word and use something like Textpad.

I second this motion, or Google for Pspad (tabbed editor )

Cork Skate

Caixa wrote:

Your code worked perfectly on my mysql install. This is what phpmyadmin suggests to enter it, you may as well give it a go:

Caixa, tried that and still the same !?!

jd wrote:

do the inserts fail on the commit? can you mot satisy the constarint before the commit?

Yeah .... 45 beeps all in one go

I cant really .... i mean .... i'll get caught some where because nearly every tables will have a/a few foreign keys ..... i'll always get caught .... unless i take out the foreign constraint now .... update and Alter it later ...... is that wise though ??

muckwarrior

0utshined wrote:

Muckwarrior, I believe this was me but I did warn that I hadn't done SQL in a while. That's the escape character for C\C++ and this link from the MySQL homepages suggests it should work.

Sorry. Was actually just after editing that post. I never seen it done that way, just wanted to point out that I knew my way worked for sure

0utshined

No bother, like I say I haven't used SQL in a while so all I'm doing is quoting a link, if you have first hand experience I'm sure that's more beneficial to the OP.

OP,
Are you sure one of the earlier inserts didn't work? Are you clearing down your database before adding the data in again?

Cork Skate

0utshined wrote:

Are you sure one of the earlier inserts didn't work? Are you clearing down your database before adding the data in again?

Spot the newbie here !! .... how do i do that ??

Would it be

DELETE FROM member VALUES ???

0utshined

Delete * from member; I think.

May need to remove the Constraint too.

Cork Skate

0utshined wrote:

Delete * from member; I think.

May need to remove the Constraint too.

Delete from member worked !!

I'll try and remove the constraint !!

muckwarrior

Don't think * works in a DELETE. AFAIK it should be just DELETE FROM member;

ct5amr2ig1nfhp

Cork_Skate,
Have you been able to get the data in your table?

I tried the O'Brien->O''Brien (double apostrophe) at home tonight and it worked grand.

Found a good link that might help. Its using PHP to enter your data.
http://www.blazonry.com/scripting/linksdb/insert_data.php

Hope it helps.

ambrose :cool:

steveland?

kingambrose wrote:

Cork_Skate,
Have you been able to get the data in your table?

I tried the O'Brien->O''Brien (double apostrophe) at home tonight and it worked grand.

Found a good link that might help. Its using PHP to enter your data.
http://www.blazonry.com/scripting/linksdb/insert_data.php

Hope it helps.

ambrose :cool:

That site uses a similar method to the addslashes

$description = str_replace("'","''",$description);

This replaces all 's with ''s and then you can convert back. It's just the same only using '' instead of \' (both of which work)

rsynnott

Caution; If you ever connect this to a program, you MUST add the slashes automatically. Otherwise, naughty, naughty people will compromise your system.

Cork Skate

Lads, I have tried to drop the CONSTRAINT with this

ALTER TABLE member DROP CONSTRAINT PresentClub_check;

And this doesn't work .....

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 'CONSTRAINT P
resentClub_check' at line 1

Any ideas on what i am doing wrong ??
It must be something simple yeah ?!?!




I have also tried to drop the table

DROP TABLE member;

and i get .....

ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails

Cork Skate

Problem solved !!

at the start put .....

SET FOREIGN_KEY_CHECKS=0;

and at the end put ....

SET FOREIGN_KEY_CHECKS=1;

Works fine !!

SELECT * FROM table_name;

and Hey Presto !!

is_that_so

Ok A bit of explanation on all of this

Foreign keys

You can only "get rid of" a foreign key by disabling it. Foreign keys are best enabled once the tables that they reference are actually populated, avoids all the messy errors.

Id numbers

Use an autoincrement type for this, you don't really care what the ID is. A DB query will retrieve it.

Delete

Delete * from table wipes all the table data :rolleyes:

Insert

Each insertion requires a termination which is ;

Quotes

Don't use any fancy quotes- notepad , textpad are fine. Use the ASCII value to be certain. Problem is that the fancy ones are high ASCII values/UNICODE values which may not map correctly thus giving mad errors.

pollyantic

Did you try replaceing your apostrophe with 2 apostrophes . eg O''Neil