Problem: I need to update a field in one table, based on info that is found in three other separate tables. Specifically, I'm running Joomla 2.5 with Virtuemart 2. I am trying out a new image viewer on my product pages, that I found over at linelab.org.
I want to update captions for my products, so they will look good on the image viewer. The value showing as a caption in the VM2 slideshow is editable via the VM admin panel, "Image Alt-Text" field.
When editing a product, click the Product Images tab. To edit a particular image, click the lower right corner of the thumbnail, and the details will load below. Scroll down to see the image file name, etc. "Image Alt-Text" is what you can edit to change the caption for that image.
But the client I'm working for has over 600 images in their store, and I want to update all the captions via the database. This caption value is stored as file_meta in the virtuemart_product_medias table.
I would like to use the following for my updated captions: Product Name, Model and and Image Number, based on the order set in Product Images.
First, I make a query to retrieve this info from the several Virtuemart tables.
SELECT CONCAT(peg.product_name, ' Model ', p.product_sku, ' image ', pm.ordering)
FROM `pfx_virtuemart_products` p
, `pfx_virtuemart_products_en_gb` peg
, `pfx_virtuemart_product_medias` pm
, `pfx_virtuemart_medias` m
WHERE peg.virtuemart_product_id = pm.virtuemart_product_id
AND pm.virtuemart_media_id = m.virtuemart_media_id
AND p.virtuemart_product_id = peg.virtuemart_product_id;
If you use this query, replace the prefix 'pfx' with your own database prefix.
Now, I want to update the file_meta field in table virtuemart_product_medias with this data.
AHA! This works:
UPDATE `pfx_virtuemart_medias` vm,
(SELECT p.virtuemart_product_id as pid
, m.virtuemart_media_id as mid
, CONCAT(peg.product_name, ' Model ', p.product_sku, ' image ', pm.ordering) newcap
FROM `pfx_virtuemart_products` p
, `pfx_virtuemart_products_en_gb` peg
, `pfx_virtuemart_product_medias` pm
, `pfx_virtuemart_medias` m
WHERE peg.virtuemart_product_id = pm.virtuemart_product_id
AND pm.virtuemart_media_id = m.virtuemart_media_id
AND p.virtuemart_product_id = peg.virtuemart_product_id) t2
SET vm.file_meta = t2.newcap
WHERE vm.virtuemart_media_id = t2.mid;
But this works too, and may be a little more straightforward:
UPDATE `pfx_virtuemart_products` p
, `pfx_virtuemart_products_en_gb` peg
, `pfx_virtuemart_product_medias` pm
, `pfx_virtuemart_medias` m
SET m.file_meta = CONCAT(peg.product_name, ' Model ', p.product_sku, ' image ', pm.ordering)
WHERE peg.virtuemart_product_id = pm.virtuemart_product_id
AND pm.virtuemart_media_id = m.virtuemart_media_id
AND p.virtuemart_product_id = peg.virtuemart_product_id;
Happy Coding! Again, if you want to use this SQL code, remember to use your own db prefix, instead of the 'pfx' I used above.
If you need some help with custom Joomla site updates, Shoot Me an Email.
