Mass Inventory Update with MySQL
I recently did an import into a CubeCart store that wound up with over 109,000 products. The file from the supplier didn't include image URLs, but the images were named the same as the Product Code and in a folder named from the first two characters of that Code. So how to generate the file names? I could of course write some custom PHP, but I like making MySQL do my dirty work.
As I said in my first post here, as a developer, there's little you can do that's of more overall benefit than when you Read the Manual. So with that in mind, I headed over to the MySQL site to brush up on my function syntax. I know I wanted the first two characters of my product code and would need to add '.jpg' to the end. Well, I knew there was a traditional SUBSTR() function and a concatenate function - CONCAT() -- so those should do what I wanted.
Here's the code I wound up using:
UPDATE `CubeCart_inventory` SET `image` = CONCAT(SUBSTR(`productCode`,1,2),'/',`productCode`,'.jpg') WHERE `image` IS NULL;
This gave me image names of '01/010067.jpg' where there was a product code of '010067'. It gave me exactly what I wanted and only took a few seconds to process the nearly 110,000 products. Note: I could have left off the WHERE clause, but some of the products already had images assigned.
This may or may not help you with your store, but be aware that there are many things you can do to your database directly via PHPMyAdmin if you have good idea of what MySQL can do on its own.
blog comments powered by Disqus
