Product prices added directly to DB, not showing [solved]

[d0ublezer0]

For some automation we use our own CSV processing. The script adds prices to products.
But prices are not displayed in the backend, despite the fact that they are presented in the database

We tried to compare the lines with each other, and did not see significant differences. Moreover, attempts to bring the lines to the same form did not help us. Prices are visible only (!) After saving in the administration panel.

Why could this be? What fields are required for storing prices?

Here are some of our observations:
1. product had no prices at all

2. adding prices directly to table `prefix_virtuemart_product_prices`

Code Select Expand

INSERT INTO caux8_virtuemart_product_prices
(virtuemart_product_id,product_price,product_currency, product_override_price,
override, price_quantity_start, price_quantity_end)
VALUES (1230,'9.50',131, NULL, 0, 0, 1871)


3. see it in the table
https://prnt.sc/wkbzdm

but not see at backend/frontend
https://prnt.sc/wkc115

4. trying to add some random price at backend: this action will overwrite all imported prices on step 2, and we see only this added price in backend and database table
https://prnt.sc/wkc1v4

and now:
5. repeat step 2 (import form csv), when at least one price exists - all prices added and displayed normally
https://prnt.sc/wkc56c

step 5 ident to step 2, byte-to-byte, as we see in database
how this is possible?

[jenkinhill]

No idea about this, but in table (3) virtuemart_product_id is the same for the 3 examples?

[Jörgen]

The three prices belong to the same product. What keeps them apart? Is the product id of the product example 1230 ?
Jörgen

[d0ublezer0]

No idea about this, but in table (3) virtuemart_product_id is the same for the 3 examples?

yes, the same.
these are prices in the range of the products quantity

[d0ublezer0]

The three prices belong to the same product. What keeps them apart? Is the product id of the product example 1230 ?
Jörgen

yes, same id. i used product quantity prices

[d0ublezer0]

like this

in database
https://prnt.sc/wkext4

in backend
https://prnt.sc/wkeyju

[AH]

Show the complete rows - your screen shot is not helpful

I suspect currency - but cannot see

[d0ublezer0]

Show the complete rows - your screen shot is not helpful

I suspect currency - but cannot see

currency has presented - you can see it in INSERT query at first message

as i say before: in one of attempts, i tried to fill all fields directly in DB, similar the right one

[d0ublezer0]

you can see process at this screencast video:
https://youtu.be/f90Qx6uBKTg
1- imported prices not showed
2- i add some random price
3- all values in db disappears, instead of added from backend
4- reimport
5- same values in db, as in step 1
6- but showed in back/frontend

An attentive viewer may notice that not all values in the database fields are completely identical to those created from the admin panel.
But, believe me, I tried to store values
product_override_price,
tax_rate,
product_tax_id,
created_by,
modified_by

for the same values, as you can see on step 3
result is the same - prices not shown.

it is a mystical

[AH]

Thanks for the video - that really helped me get an idea and saved many questions

I think you need to check in the virtuemart_products table to see that "has_prices" is also updated accordingly.

http://docs.virtuemart.net/tutorials/development/241-reduced-database-access-by-using-booleans-for-reference-tables.html.

Please look at the value of your product table "has_prices"field when prices do not display.

If the product did not have a price when added - and you added by just a csv insert - then this product field will be 0 and the system will not look at the prices table

Code Select Expand

UPDATE ##__virtuemart_products SET has_prices = 1 WHERE virtuemart_product_id = 'nnn'

Not sure what sense check you are doing on the prices:-

Valid product_id
Valid shopper group
Pricing config already exist (update rather than insert)

[GJC Web Design]

further to AH's suggestion ( and i think he is on the right road here) you could try with the setting 'Optimise sql requests for products'  VM config->config->shop unchecked that should disable this boolean check

[d0ublezer0]

Please look at the value of your product table "has_prices"field when prices do not display.

Thank you very much! This is the solution!

[AH]

Good to see you have confirmed the reason for the issue and understand the solution