Rachael w. Posted November 23, 2016 Share Posted November 23, 2016 I am looking to update my product_descriptions based on the category via phpmyadmin sql query. Example: Category: Carrots Product: Yellow Carrots Product Description: These are purple carrots. What I want to do is change the Product Description for the Yellow Carrot Product in the Carrot Category. Which would show this after: Category: Carrots Product: Yellow Carrots Product Description: These are yellow carrots. I've started it but got lost. I am writing over the current description with the new one. Here is what I have so far and I know I need more. `products_description`UPDATE `products_description` SET `products_description`=[These are yellow carrots.] WHERE ........ I don't know how to write it so it compares the product_id in products_description to the product_id in products_to_categories then only changes the products_description on products in the category_id 32. Thank you in advance for your help. Link to comment Share on other sites More sharing options...
♥bruyndoncx Posted November 23, 2016 Share Posted November 23, 2016 update products_description set products_description = 'these are yellow' where products_id in (select products_id from products_to_categories where categories_id = 32) !!! this is a very dangerous query it overwrites whatever there was, and does it for all languages for the products linked to categories id 32 KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt Link to comment Share on other sites More sharing options...
ArtcoInc Posted November 23, 2016 Share Posted November 23, 2016 @@Rachael w. How many of these do you have to edit? If only a few, wouldn't it be easier to edit these through the Admin side of the shop? Malcolm Link to comment Share on other sites More sharing options...
Jack_mcs Posted November 24, 2016 Share Posted November 24, 2016 @@Rachael w. The better approach, in my opinion, is to install Easy Populate. You can then download a file just for that category, do a search and replace or however you want to edit it and upload the file. Very quick, easy and safe. And if you backup the original file, you can restore them all, if needed. Support Links: For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc. Get the latest versions of my addons Recommended SEO Addons Link to comment Share on other sites More sharing options...
Rachael w. Posted November 24, 2016 Author Share Posted November 24, 2016 I installed an update on my server (damn pci compliance stuff) and it truncated every description in my store. I didn't notice until weeks after (since I had a heck of a time updating all the code to make the site function properly) and my backup from the previous month backed up the truncated description. Our products are pretty much the same in every category so writing over every product is the best route. I only have one category where their descriptions vary and I plan to update those individually through the admin. We have over 150,000 products on our site. Updating via sql is the quickest and easiest way to go. Our individual product pages have their own individual titles and we have those included in the description area (along with their manufacturers, etc.) so it will create descriptions which have some individual information. SEO hasn't been a problem for these since we have a very niche product catalog. Thank you bruyndoncx, I'll try that on my test site. Edited to add: I thought about Easy Populate over the past years. This site is a beast, even trying to open the sql file locally takes forever and editing it is horrible. Just this category has over 50,000 products. Another has 60,000 and others vary from 100 to 10,000. I'm still amazed the site works at all. Link to comment Share on other sites More sharing options...
MrPhil Posted November 24, 2016 Share Posted November 24, 2016 That's quite bizarre. I don't recall ever hearing about something like that happening. I presume that you've checked, and gone into phpMyAdmin to browse the product tables and indeed, the text is truncated there. Were the field definitions changed (shortened)? What was changed on the DB server side to meet PCI compliance? Were other changes made at the same time, such as switching to UTF-8? If someone else broke it while doing PCI work, I would think you have a legal case against them to at least recover lost sales and the cost of remediation. If it was you... Whenever you do major work on a site, especially the database, you should check that you have a good solid backup before beginning work. Don't rely on your host's backups, or older routine backups that you make. And make doubly sure that your database was in fact corrupted by the update, and it's not some glitch in a new PHP version or changes to your osC code that is truncating text strings. I've seen people tearing their hair out fixing "corrupted data" when the problem was actually elsewhere, such as strings being chopped off at now-invalid characters (when converting to UTF-8). If a conversion was done, maybe the text wasn't changed properly to UTF-8 -- it may still be there, just temporarily inaccessible. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.