I've a MySQL Database with a table "products" and a lot of columns.
I've a column called name that have always a name-structure like this: example
GD 0001 / PL40
GD 0034 / PL60
GD 0106 / PL30
GD 0122 / 2S
etc
In the same table products I have another column, image. It's possible to fill this field automatically relying on field products?
I'm trying to obtain this result:
gd0001_pl40.jpg
gd0034_pl40.jpg
gd0106_pl30.jpg
gd0122_2s.jpg
If necessary I can use PHP or SQL
Thank you, hope my question is understandable
To have the actual values converted, first run this SQL:
UPDATE products SET
image=LOWER(CONCAT(REPLACE((REPLACE(name," ","")),'/','_'),'','.jpg'));
After if you want total automatic conversion, you can crete two triggers, which will do it for you on INSERT and UPDATE:
DELIMITER ;;
CREATE TRIGGER products_ins BEFORE INSERT ON products
FOR EACH ROW
BEGIN
NEW.image = LOWER(CONCAT(REPLACE((REPLACE(NEW.name," ","")),'/','_'),'','.jpg'));
END;
CREATE TRIGGER products_ins BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
NEW.image = LOWER(CONCAT(REPLACE((REPLACE(NEW.name," ","")),'/','_'),'','.jpg'));
END;
DELIMITER ;
This way if you update your name field your image field will be auto-updated.
Use select concat(REPLACE((REPLACE(column_name," ","")),'/','_'),'','.jpg') from table_name
You can add a function to convert this whole string to small case, you can do that in mysql as well as in php on application layer
you need to write your own logic to get this. you may use javascript or java(String replace(),split()) methods.