sweedish MySQL

[otuatail]

Can someone help out here. I had an SQL query that worked fine on one mysql server but not on another.

The other one states in PHPmysql Collation  latin1_swedish_ci

 

The query is

 

INSERT INTO Updates (UserID , Date , SQL) VALUES

(1,'1213821547','UPDATE Drinks SET Description = ''John Smiths'' , Pint = ''2.48'', H_Pint = 1.28, ABV = ''3.8'' WHERE ID = 1')

 

Desmond.

structure

 

DROP TABLE IF EXISTS `Updates`;

CREATE TABLE IF NOT EXISTS `Updates` (

  `ID` int(11) NOT NULL auto_increment,

  `UserID` int(4) unsigned default NULL,

  `Date` char(12) NOT NULL default '',

  `SQL` char(120) NOT NULL default '',

  PRIMARY KEY  (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

 

[hitman6003]

''John Smiths''

''2.48''

''3.8''

 

Those are wrapped in two single quotes.  They should have a single single quote and the single quotes inside the field should be escaped with a backslash ("\")

[otuatail]

No This does not work either

 

INSERT INTO Updates (UserID , Date , SQL)VALUES

(1,'1213857350',

 

'UPDATE Drinks SET Description = \'John Smiths\' ,

Pint = \'2.41\', H_Pint = 1.21, ABV = \'3.8\' WHERE ID = 1')

[fenway]

Why do you have an UPDATE statement inside your INSERT?