Chosing one field over another if it exists with same field name

[Simon Moon]

First of all, I am using PHP 5.2.4 and Mysql 5.0.45.

 

The problem I have here is a bit odd. I have a work around, but this is not satisfying. I know there is a way to do this with SQL, I am certain I have done this before even but hell, I can't find out when or where that was. For a couple of days I have been running in circles and I am sure the solution is too simple for me to stumble on it on my own by now. So I hope you guys can help me out here by pointing out what I am missing.

 

One table, 3 fields: languageid (int), phrasename (string), phrasecontent (string).

First row: 0, test, Testing0

Second row: 2, test, Testing2

 

Now imagine the second row was no there. I am looking for the phrase with the name "test". In that case i get back "Testing0". Now the second row gets added. In this case i would get back two results, but I only want the one with the languageid 2.

 

Problem: I need ONE query (always the same) to return only ONE row in each case. The query needs to look for language id 2, but if it cant find the phrase it, will fall back on the phrase with the id 0 (who will always be there). If I can get this to work with one field, I should be able to create a list with that too.

 

The system I am creating is a multi language base system for a software. So these would be the translations, but the idea is to have a fall back on English in case it's not present in the "translated" version yet, or is not needed to be translated at all.

 

My Work around currently does the god awful thing of loading all phrases in english into an array with the phrasenames as keys then loads all the ones from the specific language, overwriting the values in the array where the keys match (because that phrase got translated). This can't be the solution, so I really would appriciate it if anyone has a good solution that would work in SQL. If it works "generally" in sql would be even better, but i take what i can get

[Barand]

SELECT phrasecontent FROM lang WHERE languageid=2 AND phrasename='test'
UNION
SELECT phrasecontent FROM lang WHERE languageid=0 AND phrasename='test'
LIMIT 1

[Simon Moon]

Thanks, i remembered union but I never get the right results with it.

 

SELECT * FROM `phrase` WHERE languageid=2 AND phrasename='test'
UNION
SELECT * FROM `phrase` WHERE languageid=0 AND phrasename='test'
LIMIT 1

I want the language id 2 selected over language id 0 (if language id 2 exists that is) and this query does that.

 

The trouble is, it only works with one single value. I need a query that would actually work without the LIMIT, so it could in effect work over a whole list of entries in that same table.

[Barand]

Have you considered a change of table structure?

 

Put fall-back English phrases in one table and LEFT JOIN with the phrases in the other table.

[Simon Moon]

Hmm, interesting approach, but how to make it work?

 

I mean the phrase table does not contain ONE language, it can contain 2, 3 or 100 for that matter, selected through the language id. As long as you have something in that quere with "WHERE languageid=2", the left join will not work, it will always send me back the ones with the id 2 only. And i need to be able to select for the id 2, as i need to be able to also use different ids, but the same table, which is why i used this structure from the beginning. I can't make one table for each language.

 

And this is just the beginning, it gets worse later one when I need to apply similar approaches to the theme's and forums.

 

There always are work arounds, but I am sure there is a possible solution, I just haven't found it yet.

[Simon Moon]

Btw i highly appreciate your inputs here. I did not express that really clearly, so wanted to make sure you don't see me as an ungrateful bastard

 

Maybe you know of another place where I could find some help on this as well? I tried a couple, but mostly they seem dead.

[Barand]

fallback                               phrases
+-------------+---------------         -----------+-------------+---------------
  phrasename    phrasecontent          languageid   phrasename    phrasecontent
+-------------+---------------         -----------+-------------+---------------
    test      |  testing0                    2    |     test    |    testing0a
    test1     |  testing1                    2    |     test1   |    testing1b
                                             2    |     test1   |    testing1a
                                             3    |     test    |    testing0c
                                             2    |     test2   |    testing2b
                                             3    |     test2   |    testing2a

SELECT f.phrasecontent as fallback, p.phrasecontent
FROM fallback f
    LEFT JOIN phrases p ON  f.phrasename = p.phrasename AND p.languageid = 2
WHERE f.phrasename = '$searchphrase'

 

If more than 1 match they are returned. If no matches, phrasecontent column is NULL and you use fallback

[Simon Moon]

Again, highly appreciate your time. I think this is very close to the solution but not quiet yet.

 

SELECT pf.phrasecontent AS fallback, p.phrasecontent

FROM `phrase` AS pf

LEFT JOIN `phrase` AS p

ON pf.languageid='0'

AND pf.phrasename = p.phrasename

AND p.languageid = '2'

 

This works with one table, but, I also get things back i did not really want that way. It ignores both AND clauses, which also happens if I use two tables.

 

I mean how can you get Mysql to overwrite a value in a result set? I did it before, I know it's possible, and this is the key to this problem. If you select from two tables or one table doesn't make a difference really, the end result is that you have two columns that are the same, and if both columns are set, it prefers one over the other by a default (mostly which one is written first in the query as table). The more I think about it, I get the feeling that the JOIN is the answer, but I can't lay my finger on the solution yet.

 

Ideas on that overwriting principle? I mean there is even an example that does it:

http://dev.mysql.com/doc/refman/5.0/en/join.html

Look for "Here column a contains the values of t1.a." and the example just below it. Still playing around with it, but somehow I don;t get the results I am supposed to...

[Simon Moon]

Here is an SQL block that will create a table and data to test it with.

CREATE TABLE `phrase` (
  `phraseid` int(10) unsigned NOT NULL auto_increment,
  `languageid` tinyint(3) unsigned NOT NULL default '0',
  `phrasename` varchar(100) NOT NULL,
  `phrasecontent` mediumtext NOT NULL,
  PRIMARY KEY  (`phraseid`)
);

INSERT INTO `phrase` (`phraseid`, `languageid`, `phrasename`, `phrasecontent`) VALUES
(1, 1, 'test1', 'test1 english'),
(2, 2, 'test1', 'test1 german'),
(3, 1, 'test2', 'test2 english');

 

In essence, it should never give out always 2 lines in a query. It falls back on English (language id 1) if it cant find it on the actual language. So the English query would look for language id 1, the German query for language id 2. In the English case, it returns phrase id 1 and 3, in the german, it returns 2 and 3, since "test2" is not translated into German yet, it spits back as default the English one. If you would make a third language, and query it with this, it would return only the english phrases because it has no translated phrases as of yet.