MySQL email templates.. Is it possible?

Question

Not sure if it's possible but it's worth a shot.. I am trying to insert into a MySQL 'TEXT' field some text.. Some of the words within the text I want to change depending on other fields from some other tables in the MySQL database.. Something similar to a php email template where the 'Dear ${first_name}' can be changed depending on who the email is going to...

Can something like this be done within a field in a MySQL table?

I aware this can be done using a PHP file, but I was wondering if this can be done using MySQL..

Answer 1

Yeah, I guess you could do it, using a stored procedure.

It would have to have a REPEAT loop that uses LOCATE to find the string index of the next '${' token, takes the name from there up to the next LOCATEd '}', and replaces it by CONCAT and SUBSTRING with the value. If that value comes from a simple name-to-value lookup table that's not too bad, but if you want ${first_name} to actually use the column called first_name you would have to create some dynamic SQL in a string and run it using PREPARE...EXECUTE, which is ugly and dangerous.

It would be complex, fragile and DBMS-dependent. SQL is not really designed to be convenient for string fiddling. Any general-purpose programming language with reasonable string-manipulation facilities should be able to do it in a much more straightforward way. If you have PHP available, use it.