Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Help update product_description by category via sql query


Rachael w.

Recommended Posts

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

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

@@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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...