For the database experts: can I backup/restore certain products prices

[travbacon]

I have some pricing for a certain manufacturer. The pricing shows the regular price and sale price. Every so often I need to just show the regular price. The sale prices are not a straight across percentage discount but different percentages for each product for this manufacturer.
My question is I tried to figure out the sql statement to just back up the mfg, product, regular price and sale price (I think that is all I need?). This way when I need to add the sale prices back, I can do it via mysql quickly.
Can anyone help with this please? TIA.

[travbacon]

bumpity bump for an answer hopefully

[DrByte]

I looked at this yesterday and didn't reply because I felt like it needed more information and I didn't really want to go thru all the trouble of guiding to get all the necessary info, especially when there was a lot of other stuff going on in my day.

Things like:
- are you really asking just about a "backup"?
-... or is there also a wish to "restore" hidden in there too?
- aren't you really also asking how to manipulate all the values in an automated way?
- are attributes involved?
- why not just use EasyPopulate to pull down the items for that Mfg, edit them, and upload them again?

Sales are stored in separate tables, but "lowest available price, including sales" is a cached value that has to be updated (and is auto-updated whenever you use the Admin to edit a product or sale). So there's more involved with "restoring" the prices than just generically inserting records. And thus so also with the "backup".
(I write "backup" in quotes because I don't think you're really asking for what most would call a "backup" (they usually think of a complete set of data), but rather a snapshot of just certain fields for a filtered list of records.)

It may be easier for others to give useful solutions if the bigger picture is explained more fully.

[travbacon]

So I have MFG A and MFG B (examples)

For MFG B, I have 3 products

PRODUCT A is 10.00. SALE PRICE is 9.00
PRODUCT B is 12.00. SALE PRICE is 8.00
PRODUCT C is 50.00. SALE PRICE is 45.00.

I would like to back these 3 items up. MFG. PRODUCT. REG PRICE. SALE PRICE.

I can easily REMOVE the sale price and just have the REGULAR price shown. But it is a PITA to put the sale price back on for each product especially if you have more than 3, like 300.

So I would like to be able to go into MYSQL, and upload that backup data so the sale prices are shown again.

[travbacon]

This is a good example

https://www.zen-cart.com/showthread....y-Manufacturer

See there is no way to turn off a manufacturer so Linda suggested this. Kinda what I am looking for. To backup the data of those sale prices of a certain mfg, and go to my admin, change that mfg to regular prices and when I need the sale prices, either run a commend in sql or upload the db to overwrite the current data.

[mc12345678]

So I too have waited a little before responding because of the vagueness of the request/expectation, the way it seems like perhaps something else has been attempted and failed and that this is such a complete unique case that perhaps special code has already been written for this store to do what it does.

I'm to the point where I recommend the following for you to 1) see if at least this could work for you and 2) depending on how close it gets to your situation that something be done to help you out.

So: the goal appears not to turn off the product but to turn off the special price or whatever unique term is given to the price. (part of the confusion) This will allow the product to continue to be sold but sold at "standard" price until it is desired to again show it on "special".

So, EasyPopulate has been discussed. Might I suggest EasyPopulate V4. For now it is just to gather some data. Regardless the final tool to use, need to see what it is that is giving you what you want and then determine how easy/difficult it is to hone/reproduce.

So, after installation. To make the data collection needed above, create a temporary category in your store.

Add a product to that category. Set it up with all of your normal information for a product.
Once done, copy that product to the same category (do not make it a linked copy).
Now make that duplicate product a "sale" or whatever it is you have been doing to apply your unique price.

These product can be disabled as well, they do not need to be live/active/in stock product, they just need to be in the database and preferably both in one category so that the data "review" is made easier.

Now inside EP4 (tools->Easy Populate V4) along the upper row of the window is a series of dropdowns. First thing I'd like you to do is select the category associated with these two product. Then the last dropdown select the all product so that if these test product were deactivated they will show up.

Now, the "test(s)". I'd like you to export each type of file from the dropdown(s).

Because of the way the product was populated above, there should be three rows of data in each file generated. The question is which file(s) offer the sale data that you are looking to preserve and include the column name(s) (v_something_or_other).

It may be that all that is necessary is that the manufacturer's name gets added to an export so that you know which is which and can undo the applicable data, or perhaps a setting is changed. Endless options, but right now we need to all be talking the same language in order to be able to provide specific support and perhaps the easiest way to do that is to give a sort of "picture" and have you point at the thing(s) that are right or wrong...

If you go on to try to use that program for other things, then so be it, but for the moment it is being used as an export tool to understand this issue better.

[travbacon]

Ok we are making this more difficult than it sounds. And I understand, everyone interprets what they read differently so sometimes it is hard to explain what one wants.

To simplify things, all I want to do is backup the following

A CERTAIN MFG. THEIR PRODUCT ID.THEIR REG PRICE. THEIR SALE PRICE.

from the database, whether using SQL syntax from SSH or clicking check boxes from MYSQL.

Thanks so much for your responses so far.

[travbacon]

So I am looking in phpMyAdmin under

Table: prouducts

I think I need to back up the following?

products_model (part number)
products_price (regular price)
products_price_sorter (sale price)

Do I even need to back up products_id?

Because all I will be doing is saving this data and uploading it to overwrite the same tables (that show the regular price with no sale prices) with the back up (to show the sale prices again).

Thanks.

[RodG]

So I am looking in phpMyAdmin under

Table: prouducts

I think I need to back up the following?

products_model (part number)
products_price (regular price)
products_price_sorter (sale price)

Do I even need to back up products_id?

Because all I will be doing is saving this data and uploading it to overwrite the same tables (that show the regular price with no sale prices) with the back up (to show the sale prices again).

Thanks.

I'm neither bold or confident enough to answer your questions, but what I can say/suggest is this:


Create a temporary category and populate it with a few products and the data that you are wanting to backup/restore -
Then try your own suggestions as to what you think you need. If it works, you have your answer. If it
doesn't work you'll need to experiment a little further until you do get the desired results.


Basically, you are wanting to do what none of us have done before, and sure we can take educated guesses
as to what tables/records are needed, but at the end of the day, the only way to be sure that you get the desired
results is to experiment.


Cheers
RodG

[mc12345678]

So I am looking in phpMyAdmin under

Table: prouducts

I think I need to back up the following?

products_model (part number)
products_price (regular price)
products_price_sorter (sale price)

Do I even need to back up products_id?

Because all I will be doing is saving this data and uploading it to overwrite the same tables (that show the regular price with no sale prices) with the back up (to show the sale prices again).

Thanks.

So wait, you're saying that the price of your "sale" is placed in the products_price_sorter column inside the products table? Such that if the field is blank then no sale, if populated it is for the price entered in the field?

If so, this is where I was referring to everyone being on the same page, because there is an entirely different table involved with sales/specials and that's where the so called sql query build gets more involved...

As to your questions about the fields above. Products_id is unique to each product in the database. There are no two duplicates. Products_model could be duplicated across multiple product (go ahead and try assigning the same products_model to more than one product if there is any question about it then search the table, will see it twice. Can't do the same for the products_id.)

Without pairing up what you do with what changes are made, I'm getting to be with Rod as it seems understanding/explanation and expectation seem to be diverging.

Trial and error, preferably on a backup of your live database (development location), is most likely to help find the solution. At least one method to support detection of the changes/differences has been provided.