I am presently creating a multilingual website, where submissions are put in British first and than converted into other languages.
All of the submissions are saved in a single MySQL table that appears such as this:
id language title master 1 en The first page title 1 2 es Página not título 1 3 p Seite einen titel 1 4 en Page two title 2 5 es Página dos título 2
What I have to do is have the ability to get a listing of all of the content that also must be converted. Within the situation above it might be #4 must be converted into German.
Any help could be appreciated.
select master from MyTable group by master having count(distinct language) < 3
If you would like not just the count, but the info what's missing you migh would like to try
SELECT GROUP_CONCAT(language ORDER BY language) as languages_done FROM tablename GROUP BY master HAVING languages_done<>'de,en,es'
I'd make use of this to obtain the content and also the translations still needed.
SELECT master , Languages.language FROM MyTable LEFT JOIN ( SELECT DISTINCT language FROM master ) AS Languages ON master.language = Languages.language WHERE master.language IS NULL
Hope this can help,
- Find all of the languages being used.
- Find all of the master documents being used.
- Create a listing of master documents in most languages.
Take away the listing of documents already converted (MINUS or EXCEPT based on platform)
WITH languages AS (SELECT DISTINCT language FROM mytable), masters AS (SELECT DISTINCT master FROM mytable) SELECT m.master, l.language FROM masters m, languages l MINUS SELECT m.master, m.language FROM mytable m