Replace one word with another in lots of rows in a database

[oskare100]

Hello,
Is there in any way (with MySQL or PHP or software) to replace one work with another in all rows in a table?

I have about 7000 product descriptions in Swedish and I need to translate them into English. The good thing is that all the description only contain a few words (I think that I just need to replace about 30 words in total to translate everything). The bad thing is that I don't know how to do that.

So I want to select all rows with the language id for English (currently all English decriptions are in Swedish) and then replace all occurances of a specific word with another specific word.

So UDPATE description ???? WHERE language_id = 5 is what I've figured wold be a start for an MySQL command... the problem is that I don't know what to put instead of ????.

Would it be possible to do that in PHP or MySQL? Could you help me with any kind of command (is that how you say it?) or any other help?

Regards /Oskar

[bazz]

Can you show us yout table arranagement. I don;t seem to be grasping the language differences thing properly.

Basically I think you could use UPDATE but I can't be sure of the WHERE condition until I understand your tables.

bazz

[oskare100]

Hello,
Thanks for your help, here is the database structure:

CREATE TABLE IF NOT EXISTS `products_description` (
`products_id` int(11) NOT NULL auto_increment,
`language_id` int(11) NOT NULL default '1',
`products_name` varchar(400) NOT NULL,
`products_description` text,
`products_url` varchar(255) default NULL,
`products_viewed` int(5) default '0',
PRIMARY KEY (`products_id`,`language_id`),
KEY `products_name` (`products_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7540 ;

--
-- Data i tabell `products_description`
--

INSERT INTO `products_description` (`products_id`, `language_id`, `products_name`, `products_description`, `products_url`, `products_viewed`) VALUES
(2742, 4, 'Antenn till Nokia 7260', 'Typ: Antennadapter<br>\r\nPassar till: Alcatel <br>\r\n<br>\r\nAdapter Ej orginal.', '', 0),
(110, 4, 'Antennadapter till Regex', 'Typ: Antennadapter<br>\r\nPassar till: Alcatel <br>\r\n<br>\r\nAdapter Ej orginal.', '', 30),
(111, 4, 'Antennadapter till Alcatel', 'Typ: Antennadapter<br>\r\nPassar till: Alcatel <br>\r\n<br>\r\nAdapter Ej orginal.', '', 2),
(112, 4, 'Antennadapter till Test', 'Typ: Antennadapter<br>\r\nPassar till: Ericsson A2618s<br>\r\n<br>\r\nAdapter för att koppla mobiltelefon till extern antenn. Ej orginal.', '', 1),
(113, 4, 'Antennadapter till Ericsson ', 'Typ: Antennadapter<br>\r\nPassar till: Alcatel <br>\r\n<br>\r\nAdapter Ej orginal.', '', 5),
(114, 4, 'Antennadapter till mobitleefon', 'Typ: Antennadapter<br>\r\nPassar till: Alcatel <br>\r\n<br>\r\nAdapter Ej orginal.', '', 2),

/Oskar

[Fumigator]

MySQL has a nifty REPLACE() function that you can use inside UPDATE queries.

UPDATE table1
SET col1 = (REPLACE (col1, 'blah', 'superblah'))
WHERE col2 = 'whatever'