products_prid

Hi,

I'm doing an upgrade on a heavily modified site, from v1.25 to 1.38a.
I started by installing a freash copy of 1.3.8a and then developed a new template based on the old look. However, I'm now at the stage where I'm moving over the data.

I am comparing each table from the database using a combination of mysql query browser and a file comparison tool, and although slow, it's working well so far :)

However, I've hit a small problem with the 'orders_products' table. It has an extra column 'products_prid'. Having placed test orders on the new site, I can see that this value isn't a simple NULL or 0.

How can I populate this field for each record?

So far the only info I can find is from the Zen Cart wiki DB_changelog...

#added for future orders editing
ALTER TABLE orders_products ADD products_prid TINYTEXT NOT NULL;
ALTER TABLE orders_products_attributes ADD products_prid TINYTEXT NOT NULL;
ALTER TABLE orders_products_download ADD products_prid TINYTEXT NOT NULL;

Is it safe to run these statements in this case?

Many Thanks
d.

deko:

How can I populate this field for each record?

So far the only info I can find is from the Zen Cart wiki DB_changelog...

#added for future orders editing
ALTER TABLE orders_products ADD products_prid TINYTEXT NOT NULL;
ALTER TABLE orders_products_attributes ADD products_prid TINYTEXT NOT NULL;
ALTER TABLE orders_products_download ADD products_prid TINYTEXT NOT NULL;

Is it safe to run these statements in this case?

Many Thanks
d.

Yes, it will be safe to run those commands. They will add the missing fields, however they WON'T be populated with any data. Whether the lack of data will cause any issues or not I really can't say, but at least the fields will be there for any new products.

Cheers
Rod

Great thanks Ron!

But now the plot thickens...

I have looked at another database of a site I did recently and in the 'order_products' table, the values for 'products_prid' are the same as the values for 'products_id'. Which means we should be able to just copy the contents from one to the other.

However, according to this thread by mgraphic, 'products_prid' is a randomly generated hash?

http://www.zen-cart.com/forum/showthread.php?t=51738

Ajeh:

The prid is randomly generated and has no real meaning other than to tie things together ...

If you look in the orders_products_attributes you will see the values for:
products_options_id
products_options_values_id

My 'orders_products_attributes' table is empty?

Right, well that seemed to work for orders_product table :)

First ran the column update...

ALTER TABLE orders_products ADD products_prid TINYTEXT NOT NULL;

Then copied values from products_id...

UPDATE orders_products SET products_prid = products_id;

Now I can see the list of order totals, which I couldn't see last night.

Now to populate the other two tables, 'orders_products_attributes' and orders_products_download'. My dev versions are empty, so if anyone has values in these tables, can you paste a few lines so we can see if they follow the same route.

Of course, all this might be completely wrong yet :)

deko:

Right, well that seemed to work for orders_product table :)

First ran the column update...

ALTER TABLE orders_products ADD products_prid TINYTEXT NOT NULL;

Then copied values from products_id...

UPDATE orders_products SET products_prid = products_id;

Now I can see the list of order totals, which I couldn't see last night.

Now to populate the other two tables, 'orders_products_attributes' and orders_products_download'. My dev versions are empty, so if anyone has values in these tables, can you paste a few lines so we can see if they follow the same route.

Of course, all this might be completely wrong yet :)

Based upon the quote you gave from Ajeh, it seems to me that all you'll need to do is copy those same values into the other two tables.

This is assuming that there is a one-to-one matching across all three tables.

I suspect this may not be the case though, in which case the orders_products entries will need to be matched with the corresponding entry(s) in the other two tables before pasting the random hashes into them.

I would also assume that not all products will have a corresponding 'attributes' or 'downloads' entry anyway, and this being the case you'll only need to do those that do exist.

As per your comment, this could be completely wrong, but it does make sense from a coding perspective.. The hash being little more than a randomly generated 'foreign key' to make things a lot more efficient than a database search when relating the attributes/downloads tables back to the products table.

Cheers
Rod.

Here's what I'm thinking on this one Rod...

Anyone who had an active store before those columns were added, will have empty values for all products added before the update, and the appropriate values for all products added since. So it should be safe.

If I go ahead and try to manually populate those fields myself, I'm in unknown territory.
Given that this site is my friends sole income and receives thousands of visits a day, I cannot risk an imbalance. So until I know exactly what these new elements do, I best tread carefully.

Many Thanks
d.

deko:

Here's what I'm thinking on this one Rod...

Anyone who had an active store before those columns were added, will have empty values for all products added before the update, and the appropriate values for all products added since. So it should be safe.

If I go ahead and try to manually populate those fields myself, I'm in unknown territory.
Given that this site is my friends sole income and receives thousands of visits a day, I cannot risk an imbalance. So until I know exactly what these new elements do, I best tread carefully.

Many Thanks
d.

I agree with you almost 100%, especially if things all appear to be working correctly.

The only thing I can think of where this may fall apart, is if when the store(s) were upgraded to include these additional fields, the upgrade scripts retroactively populated them with data at the same time. If this is the case (and I'm only suggesting it as a possiblity) then it should be a matter of identifying which version introduced the additions, and locating the SQL script that added them, then just run the script :-)

Meanwhile, I do still agree that unless you are experiencing any problems then it probably is best/safe to leave well enough along.

Cheers
Rod

ps. Perhaps one of the zencart dev team might like to step in here with a definitive "it is safe to leave them empty" or "no, these fields do need to be populated for correct operation" (or possibly some other insight)

Without a great deal of work this is almost impossible to populate the field with the proper products_prid ...

The new orders will hold the correct values ...

But, without building an updater for that field, it will be very hard to try to come up with a simple solution ...

Note: in standard upgrades that field has the same issue ... but again, new orders will be built with the data filled in ...

NOTE: this is also a good time to get the Known Bug Fixes to v1.3.8 installed:
http://www.zen-cart.com/forum/showthread.php?t=82619

as well as the Security Patches ...
http://www.zen-cart.com/forum/showthread.php?t=131115

NOTE: on the Security Patches ... install them for v1.3.8 after you have upgraded your database and into the new code for v1.3.8 ...

Hi deko,

deko:

Given that this site is my friends sole income and receives thousands of visits a day, I cannot risk an imbalance. So until I know exactly what these new elements do, I best tread carefully.

I just sent Ajeh a private message to thank her for her input. and hopefully she doesn't mind me reposting her reply to me, but she had this to say.

"It can be done (retrocatively adding the prids) ... it is just a pain to try to build the products_prid after the fact ... unless of dire need, it should not be important on the older orders ...

The new orders will be current ... "

So there you have it.... not really any need for you to do anything more, other than follow the sage advice to follow the threads in regards to bugfixes and security patches.

Cheers
Rod

(and thanks again Ajeh)

Thank you so much Rod and Linda! That has made my day :)

I'll work my way through the bug fixes and security patches now as suggested.

Once again, thank you.
d.

I am completely agree with Rodg, he is absolutely right.