frodo — 2010-11-19T09:10:28-05:00 — #1
I am back with another question: I love SPFs!
I have a table which holds all the language strings for my website.
A string is made up of three parts: the variable name (ie lang_home) of the string, the text associated with this string (ie "Home") and the language this is in. None of these is unique.
I would like to query my DB to help me figure out which variables have not been translated. Here following are the DB scheme and some sample data:
CREATE TABLE locales (
id mediumint(9) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`text` text NOT NULL,
lang varchar(5) NOT NULL,
mod_time int(10) unsigned NOT NULL,
PRIMARY KEY (id),
KEY lang (lang),
KEY `name` (`name`),
FULLTEXT KEY `text` (`text`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO locales SET name = 'lang_home', text = 'Home', lang = 'en-US';
INSERT INTO locales SET name = 'lang_home', text = 'Casa', lang = 'it-IT';
INSERT INTO locales SET name = 'lang_test', text = 'Test', lang = 'en-US';
In this case, I would like to query the DB for missing it-IT translations and have it return 'lang_test'.
How could I do this?
r937 — 2010-11-19T09:21:21-05:00 — #2
FROM ( SELECT DISTINCT name FROM locales ) AS x
ON locales.name = x.name
AND locales.lang = 'it-IT'
WHERE locales.name IS NULL
frodo — 2010-11-19T10:44:51-05:00 — #3
Thanks 937, you are great!
r937 — 2010-11-19T11:32:03-05:00 — #4
thanks, but it's actually SQL that is great
such a complex problem, and such an elegant solution
can you imagine how a php programmer would do it?
frodo — 2010-11-19T12:02:42-05:00 — #5
yeah, that's exactly what i was tying to avoid