Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2014
    Location
    United States
    Posts
    8
    Plugin Contributions
    0

    Default Using MySQL to Update products_model

    Hello,
    I'm using v1.5.5e and have over 5000 products. I'd like to change the products_model using MySQL to standardize them. Can I run multiple update statements, for example:
    UPDATE `products` SET `products_model` = 'BOR4000' WHERE `products`.`products_id` = 5205;

    My concern is that there are fields/tables that relate to products_model that need updated as well. I understand the best way to accomplish this would be through the GUI however that is not an option for over 5000 products.

    Installed plugins:
    Sales Tax Summary
    Easy Populate 4
    Ceon URI Mapping (SEO)

    Thanks!

  2. #2
    Join Date
    Dec 2009
    Location
    Amersfoort, The Netherlands
    Posts
    2,285
    Plugin Contributions
    20

    Default Re: Using MySQL to Update products_model

    Is the model always the same?

  3. #3
    Join Date
    Feb 2009
    Location
    UK
    Posts
    478
    Plugin Contributions
    0

    Default Re: Using MySQL to Update products_model

    On v1.5.5e products_model only appears in 'products' and 'orders_products'. So I guess it depends on whether you have orders for the products you want to change the model of. You could do a test in the GUI to change the model number of a product you have an order for and see if the 'orders_products' is also changed - if it is then you could change that value using SQL statements as well.

  4. #4
    Join Date
    Dec 2009
    Location
    Amersfoort, The Netherlands
    Posts
    2,285
    Plugin Contributions
    20

    Default Re: Using MySQL to Update products_model

    Quote Originally Posted by simon1066 View Post
    On v1.5.5e products_model only appears in 'products' and 'orders_products'. So I guess it depends on whether you have orders for the products you want to change the model of. You could do a test in the GUI to change the model number of a product you have an order for and see if the 'orders_products' is also changed - if it is then you could change that value using SQL statements as well.
    The 'orders_products' will not change. That would be wrong, and will destroy the order audit trail.

  5. #5
    Join Date
    Jul 2012
    Posts
    11,636
    Plugin Contributions
    13

    Default Re: Using MySQL to Update products_model

    If you're using the latest version of EP4, then you could set the primary key to products_id, modify the products_model in your import and the product will take that products_model following the process of last entry for the products_id being the one that will be finally stored. No separate/tedious mysql related query needed.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...
    Upgraded to Zen Cart V1.5.3 from V1.5.1 from V1.5.0 from V1.3.9h

  6. #6
    Join Date
    Nov 2005
    Location
    los angeles
    Posts
    962
    Plugin Contributions
    1

    Default Re: Using MySQL to Update products_model

    Quote Originally Posted by Design75 View Post
    The 'orders_products' will not change. That would be wrong, and will destroy the order audit trail.
    i'm a little confused by that.... if there are only 2 tables that make use of the products_model, how would changing orders_products destroy the audit trail? i would think just changing the products table w/o changing the orders_products would do more potential damage..... and i thought the two tables get joined via the products_id field?

    just curious your thought process on that...
    feel free to donate money to carl or to the Zen-Cart Team

  7. #7
    Join Date
    Jan 2014
    Location
    United States
    Posts
    8
    Plugin Contributions
    0

    Default Re: Using MySQL to Update products_model

    My model is always different. No two models are the same. It sounds like simply updating the `products_model` in the `products` table does not hurt anything and I won't need to change any other tables/columns. Is this correct?

    In regards to EP4: I'm using Easy Populate 4.0.36.ZC - 07-05-2016. I'm assuming this is the latest?
    You mention setting the primary key to products_id. I was unaware this setting existed. Thanks for the idea. I will try it although I receive a gateway timeout anytime I use EP. I'm assuming it is because I have over 20,000 products.

  8. #8
    Join Date
    Jul 2012
    Posts
    11,636
    Plugin Contributions
    13

    Default Re: Using MySQL to Update products_model

    Quote Originally Posted by cloud9 View Post
    My model is always different. No two models are the same. It sounds like simply updating the `products_model` in the `products` table does not hurt anything and I won't need to change any other tables/columns. Is this correct?
    Hurt "anything"? Well, that really depends on how/how else it is used on your site, but in a basic install of ZC, the products_model within the product's table could be modified without harm to the rest of ZC. By it being changed, there is a little bit of loss of correlation between ordered product and currently existing product. The need to maintain such a link also goes back to how all of the information is being used/kept/tracked.
    Quote Originally Posted by cloud9 View Post
    In regards to EP4: I'm using Easy Populate 4.0.36.ZC - 07-05-2016. I'm assuming this is the latest?
    You mention setting the primary key to products_id. I was unaware this setting existed. Thanks for the idea. I will try it although I receive a gateway timeout anytime I use EP. I'm assuming it is because I have over 20,000 products.
    That is the latest published to ZC and yes it has that feature. May I suggest taking a look through the instructions, there is information there applicable to both issues. Yes, 20,000 items can cause some sort of issue, though there are some time settings that can be adjusted to make improvements. It is also possible to export in smaller chunks using the dropdown menu(s) along the top. But, it was not identified as to whether import or export is an issue. For import there is also the ability to split a long file into multiple parts so that each chunk can be processed individually rather than trying to import all at once and come across one of the timeouts...
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...
    Upgraded to Zen Cart V1.5.3 from V1.5.1 from V1.5.0 from V1.3.9h

 

 

Similar Threads

  1. Replies: 1
    Last Post: 10 Jan 2013, 07:59 AM
  2. MySQL Update has crashed admin
    By PGlad in forum General Questions
    Replies: 5
    Last Post: 11 Jan 2012, 04:39 AM
  3. using the first word of products_name as products_model
    By yierhan2008 in forum Setting Up Categories, Products, Attributes
    Replies: 1
    Last Post: 20 Jul 2009, 08:14 AM
  4. Tax Update MySQL
    By DiZZ in forum General Questions
    Replies: 3
    Last Post: 20 Nov 2007, 12:46 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR