I have a database made to support multiple languages. Every text that needs to be translated into other languages is an idTranslation linked to my translation table. There's also a entry table with idTranslation and the idLanguage as primary keys that contains all the texts. This Stored procedure is for modification of any text that has a idTranslation. it first checks if theres already an entry matching the new text, then if there isn't, it creates an idTranslation and matching entry and returns the idtranslation.
It works fine if I call it once but as soon as i call it twice with new text in both calls it just returns the same idTranslation both times... Am i doing sumthing wrong?
CREATE DEFINER=`root`@`localhost` PROCEDURE `AjoutEntrySiFieldExiste`(in PentryText varchar(500),in PfieldName varchar(45),in PlanguageCode varchar(5),out PidTranslation int(11))
BEGIN
if PentryText!="" then
Select `idLanguage` into @languageid from language where languageCode=lower(PlanguageCode);
Select `idTranslation` into @idTrans from entry where lower(`Text`) = lower(PentryText) and idLanguage=@languageid;
if @idTrans is null then
Insert Into translation Values(Null,(Select idField from field where lower(`Name`)=lower(PfieldName)));
Set @id = LAST_INSERT_ID();
Set PidTranslation=@id;
Insert Into entry Values(@id,@languageid,PentryText);
else
set PidTranslation=@idTrans;
end if;
SELECT PidTranslation;
end if;
END