MySQL question

synna · 02-25-2004, 07:11 AM

Hi,

I'm writting a multilingual news system and I have this two table (here I will simplify it)

Code:

TABLE news( 
   newsid mediumint(8) NOT NULL PRIMARY KEY
   #other info
);

TABLE newscontent(
    newsid mediumint
    languageid varchar(5)
    UNIQUE(newsid,languageid)
     #inof like title, content....
);

Now what I want to do is is select all news with the current language, if a news isn't translated then select the défault (english).

I mean this won't work :

Code:

SELECT news.*, newscontent.* FROM news, newscontent WHERE news.newsid=newscontent.newsid
SELECT news.*, newscontent.* FROM news, newscontent WHERE news.newsid=newscontent.newsid AND newscontent.languageid=$currentlanguage

The first one select all availlable language for each news.
The seconde doesn't select a news if no translation is availlable (here we must also show english).

I want to have a single row for each news.

Any idea about an sql command that could do this ?

Maybe it's not possible in MySQL and I have to code the language selection separately ?

I'm quite sure that MySQL can do it much faster than a coded thing (currently PHP).

Thanks.

Mara · 02-25-2004, 09:40 AM

Code:

SELECT news.*, newscontent.*
FROM news, newscontent AS nc1
WHERE news.newsid=newscontent.newsid AND    
    (newscontent.languageid=$currentlanguage OR
        (newscontent.languageid=$english AND 
            NOT EXISTS 
                (SELECT * 
                FROM newscontent
                WHERE newscontest.newsid=nc1.newsid AND 
                newscontent.language=$currentlanguage)
       )
)

It should work (not 100% sure) but you mey need new MySQL to do it (to support my EXISTS). Plus it's probably not the fastest way to do it.

synna · 02-25-2004, 11:36 AM

I'm quite sure it can work but it doesn't run on the server (generate an error) as EXISTS is only from version 4.0 and the server is 3.23.52 (and I can't change it).

So any idea more... ?

kev82 · 02-25-2004, 01:37 PM

Code:

select n2.* 
from newscontent as n1 join newscontent as n2 
 on n1.newsid=n2.newsid and n1.languageid!='eng' and n2.languageid=$currentlanguage
union 
select * from newscontent where languageid=$currentlanguage

mysql 3.x doesnt have union so you can either run two seperate queries or follow the instructions here

i think it works but theres bound to be a much faster/better way.