Site maintenance: Tuesday 17 Nov, from 10:00 UTC, about 4–6 hours. Details

This cutover relies on some testing that is still incomplete so may be cancelled at short notice

Forums / General Questions / how to do global change of several variables for all products?

how to do global change of several variables for all products?

Started by ysteban on 18 Jun 2011, 2:59 AM · Views: 1,795

Results 1 to 8 of 8
18 Jun 2011, 2:59 AM
#1
ysteban avatar

ysteban

New Zenner

Join Date:
Mar 2010
Posts:
10
Plugin Contributions:
0

how to do global change of several variables for all products?

using v1.3.9b of zen cart

i would like to make some global changes to the database, and think it can be done with the menu item Tools - install sql patches option. I just need the code line to put in there to do 2 things:

  1. I would like to set quantity = 1 for ALL products in the database.

  2. If product item = 'Out of Stock' then set quanity = 0 for ALL products in the database.

thanks in advance for your help.
Betsy

18 Jun 2011, 3:03 AM
#2
kobra avatar

kobra

Black Belt

Join Date:
Aug 2005
Location:
Arizona
Posts:
31,500
Plugin Contributions:
4

Re: how to do global change of several variables for all products?

Those 2 are contradictory

set quantity = 1 for ALL products in the database.
set quanity = 0 for ALL products in the database

Zen-Venom Get Bitten

18 Jun 2011, 2:32 PM
#3
ysteban avatar

ysteban

New Zenner

Join Date:
Mar 2010
Posts:
10
Plugin Contributions:
0

Re: how to do global change of several variables for all products?

kobra:

Those 2 are contradictory

set quantity = 1 for ALL products in the database.
set quanity = 0 for ALL products in the database

This isn't an answer how to do it, but thanks for your input.

After I set all quantities for all products to one, then i want to zero out quantity for all products which have OUT OF STOCK = TRUE flag. I only need to do this one time.

If future, when i add new products i set quantity = 1, and when i remove an item, i set out of stock = True, and quantity = 0.

Betsy

18 Jun 2011, 2:45 PM
#4
kuroi avatar

kuroi

Totally Zenned

Join Date:
Apr 2006
Location:
London, UK
Posts:
10,475
Plugin Contributions:
11

Re: how to do global change of several variables for all products?

Zen Cart doesn't have an out of stock flag. It doesn't need one as it can tell whether a product is out of stock by looking at the products_quantity value.

If you set products_quantity to 1 (or anything greater than 1) then the product is by definition in stock.

The situation is complicated by the product_status flag which text on the product maintenance page wrongly suggests is a stock indicator because it's sometimes (but not always) set to off when the product is out of stock. But conversely, if the product comes back into stock, it's not set to on again.

So the answer to your question is that it may not be possible to do what you want to do at all. But is the product_status flag is being set to off when a product goes out of stock you could use the following single line of SQL

UPDATE products SET products_quantity = products_status;
```(Yes, I know that looks too good to be true, but under the circumstances explained above and the very specific values that you want to set, it will work - just make sure you backup your database first.)

Kuroi Web Design and Development | Twitter

(Questions answered in the forum only - so that any forum member can benefit - not by personal message)

18 Jun 2011, 4:09 PM
#5
ysteban avatar

ysteban

New Zenner

Join Date:
Mar 2010
Posts:
10
Plugin Contributions:
0

Re: how to do global change of several variables for all products?

kuroi:

Zen Cart doesn't have an out of stock flag. It doesn't need one as it can tell whether a product is out of stock by looking at the products_quantity value.

If you set products_quantity to 1 (or anything greater than 1) then the product is by definition in stock.

The situation is complicated by the product_status flag which text on the product maintenance page wrongly suggests is a stock indicator because it's sometimes (but not always) set to off when the product is out of stock. But conversely, if the product comes back into stock, it's not set to on again.

So the answer to your question is that it may not be possible to do what you want to do at all. But is the product_status flag is being set to off when a product goes out of stock you could use the following single line of SQL

UPDATE products SET products_quantity = products_status;


Hi Kuroi,
thank you for the detailed information. product_status flag was what i was referring to, which on interface is set to value of In Stock and Out of Stock. From php code you gave me, then i presume it works because value of In Stock = 1 (true), and value of Out fo Stock = 0 (false). okay. 

I still need to set all quantities of all products = 1 because of a special situation. I have multiple quantities of items in my store, so product_quantity is sometimes > 1, but they are unique products. each is a used product with a grade, which makes them different. when one is sold I don't want the next one to automatically be for sale (with same description and price), or else someone can buy with grade of sold product and associated price for that grade, which is rarely the same. 

I presume then i can do these 2 steps to accomplish what i need:

1. UPDATE products SET products_quantity = 1; 
2. UPDATE products SET products_quantity = products_status; 

Please confirm if #1 will work with fixed value.

Your comment brings up another question.
my product might be sold on another site where i have it listed. in which case i edit the product, and have been setting product_status to out of stock and will now also set quantity to zero. I don't want to delete the product, because i want to save photo and reference number and where it is placed in the store, etc. Later i might get another copy of the same item, then i use bulk upload, with quantity = 1, all other things besides description and price being the same. Will this then reset product_status to In stock again? 

thanks so much for your help, i really appreciate it.
Betsy
18 Jun 2011, 4:27 PM
#6
kuroi avatar

kuroi

Totally Zenned

Join Date:
Apr 2006
Location:
London, UK
Posts:
10,475
Plugin Contributions:
11

Re: how to do global change of several variables for all products?

ysteban:

product_status flag was what i was referring to, which on interface is set to value of In Stock and Out of Stock.As I said above, the product maintenance page is misleading. The setting to which you refer only works if your stock settings have this feature enabled.

ysteban:

From php code you gave me, then i presume it works because value of In Stock = 1 (true), and value of Out fo Stock = 0 (false).
It's not PHP. It's SQL that you would run in Zen Cart's SQL patch facility (under Admin > Tools). But your assumption about how it works is correct ... which makes you next comment a bit surprising.

ysteban:

I still need to set all quantities of all products = 1
The SQL I gave you does that. It sets the quantity for those products with product_status "on" to 1 and those with the status "off" to 0.

ysteban:

I presume then i can do these 2 steps to accomplish what i need:

  1. UPDATE products SET products_quantity = 1;
  2. UPDATE products SET products_quantity = products_status;Well, it won't do any harm, but the first statement is completely unnecessary.

ysteban:

... then i use bulk upload, with quantity = 1, all other things besides description and price being the same. Will this then reset product_status to In stock again?

kuroi:

But conversely, if the product comes back into stock, it's not set to on again.

Kuroi Web Design and Development | Twitter

(Questions answered in the forum only - so that any forum member can benefit - not by personal message)

18 Jun 2011, 5:58 PM
#7
ysteban avatar

ysteban

New Zenner

Join Date:
Mar 2010
Posts:
10
Plugin Contributions:
0

Re: how to do global change of several variables for all products?

kuroi:

As I said above, the product maintenance page is misleading. The setting to which you refer only works if your stock settings have this feature enabled..

Hi Kuroi, thank you again for your expert and concise response. I have this feature enabled.

kuroi:

It's not PHP. It's SQL that you would run in Zen Cart's SQL patch facility (under Admin > Tools). But your assumption about how it works is correct ... which makes you next comment a bit surprising

Gotcha ... SQL ... Your responses tell me my awareness level is on the low side, and you challenge me to be more sharp. Thanks

kuroi:

[The SQL I gave you does that. It sets the quantity for those products with product_status "on" to 1 and those with the status "off" to 0.

Yes, you are absolutely right, very efficient thinking on your part. I bet you are an excellent software developer. I did that for 25 years, but I'm quite rusty now.

kuroi:

Well, it won't do any harm, but the first statement is completely unnecessary.

I have run the SQL command and learned how to log into phpAdmin and do a backup, and discovered while there other things i wanted to know. I really appreciate your help!

Betsy
:clap:

18 Jun 2011, 6:38 PM
#8
kuroi avatar

kuroi

Totally Zenned

Join Date:
Apr 2006
Location:
London, UK
Posts:
10,475
Plugin Contributions:
11

Re: how to do global change of several variables for all products?

Glad it worked for you and that you learned some new stuff. Learning new stuff every single day is what keeps this interesting for me.

And I too date back back to the days when as developers we looked to save every last bit, cut out every unnecessary loop and look scornfully on anybody who suggested using a GOTO.

And despite the massive increase in computing power since then, clean, lean code is still just as valuable and every bit as pleasing to see.

:wink:

Kuroi Web Design and Development | Twitter

(Questions answered in the forum only - so that any forum member can benefit - not by personal message)