Mass update of product attribute text

A previous developer at my work assigned all products an attribute called "delivery_text", which holds a string of html. For each product, the main body of this string is unique. However each one contains a link to a specific url (the link is the same for every product). Due to changes in our site layout, we now need to update this link for every product, without impacting the rest of the attribute text.

Obviously, I don't want to have to manually edit thousands of products just to change the url of the link in each product's delivery_text string. Is there a way I can programmatically parse the contents of this attribute for every product, and replace instances of links to oldsite.com/old-url with links to newsite.com/new-url?

I presume I can do it with some kind of database trickery, and have access to phpmyadmin via cpan, but I have no idea how to actually go about doing so.

Note that I don't consider this a duplicate question, as while there are many "how do i mass update attribute values for all products" type questions, I have yet to find one that asks "how do i search and replace a specific character sequence within the attribute text for each product".

Solution

If you want to do this from the database, you could do something like this:

Find the attribute values

SELECT at.* FROM catalog_product_entity_varchar AS at
INNER JOIN eav_attribute AS ea
  ON ea.attribute_id = at.attribute_id
WHERE ea.attribute_code = 'delivery_text';

This should show you a list of all of the attribute values in your database currently.

Update the attribute values

Once you've found all the values, you can use the same query to replace the URL within the values:

UPDATE catalog_product_entity_varchar AS at
INNER JOIN eav_attribute AS ea
  ON ea.attribute_id = at.attribute_id
SET at.value = REPLACE(at.value, 'http://oldurl.com', 'http://newurl.com')
WHERE ea.attribute_code = 'delivery_text';

Doing it without SQL

You could also do this using the Magento model APIs. Start by gathering the data you need, the same way as above:

Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('delivery_text');

foreach ($collection as $product) { /** @var Mage_Catalog_Model_Product $product */
    var_dump($product->getDeliveryText());
}

Then you can update the attribute within your loop. Here you need to specify a store ID, so I've assumed you'll do this at the admin (global) scope:

$old = 'http://oldurl.com';
$new = 'http://newurl.com';

foreach ($collection as $product) { /** @var Mage_Catalog_Model_Product $product */
    Mage::getModel('catalog/product_action')
        ->updateAttributes(
            array($product->getId()), 
            array('delivery_text' => $new),
            Mage_Core_Model_App::ADMIN_STORE_ID
        );
}

Please note: Using Mage_Catalog_Model_Product_Action::updateAttributes will work quickly and efficiently to update product attributes at a given store level, but if you are not using the admin store level then you may not be able to retrieve the value in a collection because this method doesn't create a default value - it just does what it says it will; create/update a value at a given store level.