5 replies to this topic

[Orjan]

Hello!

I have a language table to store all texts for an application:

textid: varchar(45)
langid char(2)
text: varchar(500)
id: integer, auto_increment

the thaugt is this, to enter one line for each textid for each language.

like:

train EN train 1
train SE tåg 2
train DE zug 3
train NL trainen 4
train FR train 5
wagon EN wagon 6
wagon DE waggon 7
wagon SE vagn 8

etc..

now, I'd like to make a query to get a new layout:

textid, <choosenlang 1>, <choosenlang 2>

with the results for DE & SE:

train, zug, tåg
wagon, waggon, vagn
etc...

so I need to pair two languages into a glossary, using the textid as link between the words.

any suggestions on making one or more sql queries or whatever needed
to accomplish this?
I could use php as well, but I'd rather use only sql if possible.

thanks :-)

[Orjan]

well, as I stated, each translation is in a row for each word/sentence for each language. now I'd like to make a twist so i get the output in different rows instead.. just for an output...

[Orjan]

so, I solved this with a stored procedure

DELIMITER $$


DROP PROCEDURE IF EXISTS `glossary` $$

CREATE PROCEDURE `glossary`(v_lang1 char(2), v_lang2 char(2))

BEGIN


CREATE TEMPORARY TABLE lang1 (

  `textid` varchar(45) NOT NULL,

  `langid` char(2) NOT NULL,

  `text` varchar(500) NOT NULL);


CREATE TEMPORARY TABLE lang2 (

  `textid` varchar(45) NOT NULL,

  `langid` char(2) NOT NULL,

  `text` varchar(500) NOT NULL);


insert into lang1

select textid, langid, text from lang where langid=v_lang1;


insert into lang2

select textid, langid, text from lang where langid=v_lang2;


select a.textid, a.text as lang1, b.text as lang2 

from lang1 a left join lang2 b on (a.textid = b.textid);

END $$

Jordan, do you understand how I ment with this code?

[shokosugi]

you basically want to have multiple rows per language instead of multiple columns.

[Orjan]

jupp, can you see a "normal" query that can acomplish what this stored procedure does?