Prices disappear

[rhand-vm]

I addded a new product and price 12.345 an got a 500 Internal Server Error showing:

JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 SQL=SELECT MAX(`product_list`) as list_order FROM `jos_vm_product_category_xref` WHERE `category_id`=

Call stack
#    Function    Location
1    JAdministrator->dispatch()    /opt/local/apache2/htdocs/joomla/administrator/index.php:67
2    JComponentHelper->renderComponent()    /opt/local/apache2/htdocs/joomla/administrator/includes/application.php:136
3    require_once()    /opt/local/apache2/htdocs/joomla/libraries/joomla/application/component/helper.php:162
4    require_once()    /opt/local/apache2/htdocs/joomla/administrator/components/com_virtuemart/admin.virtuemart.php:29
5    vm_ps_product->add()    /opt/local/apache2/htdocs/joomla/components/com_virtuemart/virtuemart_parser.php:246
6    vm_ps_DB->query()    /opt/local/apache2/htdocs/joomla/administrator/components/com_virtuemart/classes/ps_product.php:348
7    JDatabaseMySQL->loadObjectList()    /opt/local/apache2/htdocs/joomla/administrator/components/com_virtuemart/classes/ps_database.php:120
8    JDatabaseMySQL->query()    /opt/local/apache2/htdocs/joomla/libraries/joomla/database/database/mysql.php:458
9    JError->raiseError()    /opt/local/apache2/htdocs/joomla/libraries/joomla/database/database/mysql.php:231
10    JError->raise()    /opt/local/apache2/htdocs/joomla/libraries/joomla/error/error.php:171
11    JException->__construct()    /opt/local/apache2/htdocs/joomla/libraries/joomla/error/error.php:136

Apparently the query could not handle the price 12.345 nor 12,345 . But it somehow does store the product SKU and title, but does not show this data nor does it show the entered price.

[rhand-vm]

When I store a price and it is not displayed again (12.34) when creating a document for the first time it IS stored in the database (checked using PMA). So maybe the new price, is just not retrieved properly after insertion. But on a second time clicking apply the price is shown so I'd say display works on update, but not after the first insertion. Here is all debug data after first insertion when price is not displayed but is stored in the database

Code: [Select]

#
)
#
SELECT MAX(`product_list`) AS list_order
  FROM `jos_vm_product_category_xref`
  WHERE `category_id`=11
#
INSERT INTO jos_vm_product_category_xref (category_id,product_id,product_list)
  VALUES ('11','496', 1)
#
INSERT INTO jos_vm_product_mf_xref
  VALUES ('496', '2')
#
SELECT count(*) AS num_rows
  FROM jos_vm_product_price
  WHERE shopper_group_id = '8'
  AND product_id = '496'
  AND product_currency = 'EUR'
  AND (('0' >= price_quantity_start
  AND '0' <= price_quantity_end) OR ('0' >= price_quantity_start
  AND '0' <= price_quantity_end))
#
INSERT INTO `jos_vm_product_price` (`product_id`,
`shopper_group_id`,
`product_price`,
`product_currency`,
`product_price_vdate`,
`product_price_edate`,
`cdate`,
`mdate`,
`price_quantity_start`,
`price_quantity_end`)
  VALUES (
'496'
,'8'
,'12'
,'EUR'
,''
,''
,'1272808823'
,'1272808823'
,'0'
,'0'
)
#
SELECT `product_id`
  FROM `jos_vm_product_attribute_sku`
  WHERE `product_id`=496
#
SELECT module_name,module_perms
  FROM jos_vm_module
  WHERE module_publish='Y'
  AND module_name <> 'checkout'
  ORDER BY list_order ASC
#
SELECT `product_id`
  FROM `jos_vm_product_attribute_sku`
  WHERE `product_id`=496
#
SELECT vendor_id
  FROM jos_vm_product
  WHERE product_id='496'
#
SELECT shopper_group_id
  FROM jos_vm_shopper_group
  WHERE `vendor_id`='1'
  AND `default`='1'
#
SELECT product_price,product_currency,price_quantity_start,price_quantity_end
                               
  FROM jos_vm_product_price
                               
  WHERE product_id='496'
  AND
                                                        shopper_group_id='5'
#
SELECT *
  FROM `jos_vm_product`
  WHERE `product_id`=496
#
SELECT product_id, `jos_vm_product`.*
  FROM `jos_vm_product`
  WHERE `product_id`=496
#
SELECT product_id, `jos_vm_product`.*
  FROM `jos_vm_product`
  WHERE `product_id`=496
#
SELECT category_id
  FROM jos_vm_product_category_xref
  WHERE product_id='496'
#
SELECT manufacturer_id
  FROM jos_vm_product_mf_xref
  WHERE product_id='496'
#
SELECT related_products
  FROM jos_vm_product_relations
  WHERE product_id='496'
#
SELECT shopper_group_id,shopper_group_name
  FROM jos_vm_shopper_group
  WHERE `default`= '1'
  AND vendor_id='1'
I see that shpping_group_id 8 is given on first storage and 5 on update so maybe that is the culprit..

[rhand-vm]

This is the shopper group table content I have currently:

INSERT INTO `jos_vm_shopper_group` (`shopper_group_id`, `vendor_id`, `shopper_group_name`, `shopper_group_desc`, `shopper_group_discount`, `show_price_including_tax`, `default`) VALUES
(5, 1, '-default-', 'This is the default shopper group.', 0.00, 1, 1),
(6, 1, 'Gold Level', 'Gold Level Shoppers.', 0.00, 1, 0),
(7, 1, 'Wholesale', 'Shoppers that can buy at wholesale.', 0.00, 0, 0),
(8, 0, '-default-', 'Our shop', 0.00, 1, 1);

Two times default as shopper_groups_name should not really be the issue (even though default can only be chosen as group once), but one shopper having vendor ID 0 connected to it might be an issue. And then there is the strange business of the usage of two shopper groups IDs for storage and display of prices when the price is added to the database for the first time. I think that there are two default groups and in the second half of the process query looks for shopper group with vendor ID 1 and one that is default. Then you get shopper group 5. The fact that shopper group 5 and 8 are both default (not just as a group name) is not good..

[shv_rk]

so, having more than 1 vendor is still is not an option?

[rhand-vm]

No it is not an option yet as far as I know. Another issue I had was creating two default shopper groups. Had to remove one as well.

[stAn99]

So you suggest changing /administrator/components/com_virtuemart/html/product.product_form.php (path adjusted myself for my VM installation) around line 156 using

Code: [Select]

// get the default shopper group
$shopper_db = new ps_DB;
// stAn this 2 lines were added and sql modified
$vendor_id = $db->f("vendor_id");
if (!isset($vendor_id)) $vendor_id = 1;
$q =  "SELECT shopper_group_id,shopper_group_name FROM #__{vm}_shopper_group WHERE `default`= '1' AND vendor_id='".$vendor_id."'";
$shopper_db->query($q);

// stAn took me about 3 hours to find out
and that is caused by VM taking the first row in jos_vm_vendor (two vendors in my case)?

Someone else (VM Hero rb) mentioned Virtuemart only supports one vendor. I use VM 1.1.4. and this might be the problem as I have two at the moment. If this is the case VM should come up with patches as soon as possible. I am also checking if I can remove vendor id no 2 as we only need one vendor as far as I can see.

No, the problem is not in jos_vm_vendor, but in jos_vm_shopper_group, because VM takes first row! (which can be for example whole sale price and that's the reason why prices disappear) Therefore you have to modify VM to set the default retail price and not any other defined prices. When you save the product and you think, that your prices has disappeared, just click on price in administration of product and you will see that price is there, but not as default (retail, visible). I cannot imagine, how could somebody rely on taking a first row of a table...