Writing a trigger/stored-proc to achieve results but need assistance..

[Maven4Champ]

Greetings,

First let me explain my setup and then I will get to the question at hand.

Table: tbl_wiigames
(stores information about games for the Nintendo Wii including 1 game_cover image, 1 game_title, 1 game_id and 3 somewhat unecessary columns that are all used to do 1 simple task.

My task is this.
First populate field1 with the following string:
'

http://www.gamespot.com/search.html?qs='

Second, strip out the ' ' in my game_title and replace with '+' and store in field2.

Third, populate field3 with field1 then concatenate field1 and field 2 to equal:

http://www.gamespot.com/search.html?qs=keyword1+keyword2

-------------------------------------------------------

I want to be able to do this in some sort of trigger or whatever that only requires 1 field (field3) in my database. Thus I can eleminate field1 and field2. Furthermore, I need this procedure to occur anytime a record is added or modified (on update maybe?) and insert the value into the field3 column for each game_id.

What is the best way of achieving these results? Functions? Stored Procs? Trigers?

I am in MySQL 4 so until my host upgrades, I think triggers are out of the question.

Let me know.
Thanks!

 

[KarveR]

For a stored proc, you could use this:

UPDATE
tbl_wiigames
SET
field3=if(field3 is null,concat('

http://blah/search.html?qs=',replace(game_title,'

','+')),field3);

Test it first, but should do what you need without the need for field 1 and 2.

It also shouldn't affect any fields where the link is present already.

Not sure if you'll have set the field3 value to ' ' or if its originally null, youmay have to adjust that part to if(field3 = '', ......


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.