[Done v1.3.9] incorrect integer value : products_virtual

[DevlshOne]

I have installed ZC 1.3.8 on a windows server 2008 (IIS6) machine with PHP 5.2.6 and MySQL Server version: 5.0.51b-community-nt.
Unfortunately, I can't find a SEARCH function in these forums, so if this is a repeat question, I am terribly sorry.

I've had no problems with the install and have done some great CSS/Template mods but when trying to add products under categories, I get the error as mentioned above. I've looked at update_product.php, the functions contained in database.php and even inject the SQL query manually but I keep coming up with the same error.... here's the actual error response:

1366 Incorrect integer value: '' for column 'products_virtual' at row 1
in:
[insert into zc_products (products_quantity, products_type, products_model, products_price, products_date_available, products_weight, products_status, products_virtual, products_tax_class_id, manufacturers_id, products_quantity_order_min, products_quantity_order_units, products_priced_by_attribute, product_is_free, product_is_call, products_quantity_mixed, product_is_always_free_shipping, products_qty_box_status, products_quantity_order_max, products_sort_order, products_discount_type, products_discount_type_from, products_price_sorter, products_image, products_date_added, master_categories_id) values ('0', '3', '', '0', null, '0', '1', '', '', '0', '1', '1', '', '', '', '', '', '', '', '1', '0', '0', '0', 'whistle01.jpg', now(), '69')]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

One more question.. in my leftcolumn sidebox, I have added a background-image and am trying to get rid of the blank line (BR) between the category list. Any ideas?

TIA!! and thanks for the great forums and package!!

[DevlshOne]

For future users with this problem, it is related to MySQL 5.x.x STRICT mode and can be fixed by replacing the following lines in your update_product.php file located in /admin/includes/modules:

    // Data-cleaning to prevent MySQL5 data-type mismatch errors:
    $tmp_value = zen_db_prepare_input($_POST['products_quantity']);
    $products_quantity = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_price']);
    $products_price = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_weight']);
    $products_weight = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_quantity']);
    $products_virtual = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_virtual']);
    $products_tax_class_id = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_tax_class_id']);
    $products_priced_by_attribute = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_priced_by_attribute']);
    $product_is_free = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['product_is_free']);
    $product_is_call = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['product_is_call']);
    $products_quantity_mixed = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_quantity_mixed']);
    $product_is_always_free_shipping = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['product_is_always_free_shipping']);
    $products_qty_box_status = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_qty_box_status']);
    $products_quantity_order_max = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_quantity_order_max']);
    $manufacturers_id = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;

    $sql_data_array = array('products_quantity' => $products_quantity,
                            'products_type' => zen_db_prepare_input($_GET['product_type']),
                            'products_model' => zen_db_prepare_input($_POST['products_model']),
                            'products_price' => $products_price,
                            'products_date_available' => $products_date_available,
                            'products_weight' => $products_weight,
                            'products_status' => zen_db_prepare_input($_POST['products_status']),
                            'products_virtual' => $products_virtual,
                            'products_tax_class_id' => $products_tax_class_id,
                            'manufacturers_id' => $manufacturers_id,
                            'products_quantity_order_min' => zen_db_prepare_input($_POST['products_quantity_order_min']),
                            'products_quantity_order_units' => zen_db_prepare_input($_POST['products_quantity_order_units']),
                            'products_priced_by_attribute' => $products_priced_by_attribute,
                            'product_is_free' => $product_is_free,
                            'product_is_call' => $product_is_call,
                            'products_quantity_mixed' => $products_quantity_mixed,
                            'product_is_always_free_shipping' => $product_is_always_free_shipping,
                            'products_qty_box_status' => $products_qty_box_status,
                            'products_quantity_order_max' => $products_quantity_order_max,
                            'products_sort_order' => (int)zen_db_prepare_input($_POST['products_sort_order']),
                            'products_discount_type' => zen_db_prepare_input($_POST['products_discount_type']),
                            'products_discount_type_from' => zen_db_prepare_input($_POST['products_discount_type_from']),
                            'products_price_sorter' => zen_db_prepare_input($_POST['products_price_sorter'])
                            );

[enzo-ita]

I am using zen cart 1.38B Italian version. with Mysql 5.1.35 community and I got the same problem: incorrect integr value.
I patched the code and that fixed it, but it created a new problem with real products.
The problem is that if I try to create a new product with the patched update_product.php the products are created with defaults like: No taxes, Free shipping and another in the quantity that isset to 1 by default. These settings can not be changed after.
I ended up having two update_product.php files that I manually switch renaming the file I want to use as update_product.php when I have to create documents or real products.
Is there any definitive patch so that only one update_product.php can be used?

Thanks

enzo

[DrByte]

The posted "fix" by DevlshOne contains coding errors, and thus will cause problems.

[DrByte]

I would suggest that something like the following might be a more reliable fix than what was suggested by DevlshOne:

/admin/includes/modules/update_product.php
around lines 20-53:

    // Data-cleaning to prevent MySQL5 data-type mismatch errors:
    $tmp_value = zen_db_prepare_input($_POST['products_quantity']);
    $products_quantity = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_price']);
    $products_price = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['products_weight']);
    $products_weight = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;
    $tmp_value = zen_db_prepare_input($_POST['manufacturers_id']);
    $manufacturers_id = (!zen_not_null($tmp_value) || $tmp_value=='' || $tmp_value == 0) ? 0 : $tmp_value;

    $sql_data_array = array('products_quantity' => $products_quantity,
                            'products_type' => zen_db_prepare_input($_GET['product_type']),
                            'products_model' => zen_db_prepare_input($_POST['products_model']),
                            'products_price' => $products_price,
                            'products_date_available' => $products_date_available,
                            'products_weight' => $products_weight,
                            'products_status' => zen_db_prepare_input((int)$_POST['products_status']),
                            'products_virtual' => zen_db_prepare_input((int)$_POST['products_virtual']),
                            'products_tax_class_id' => zen_db_prepare_input((int)$_POST['products_tax_class_id']),
                            'manufacturers_id' => $manufacturers_id,
                            'products_quantity_order_min' => zen_db_prepare_input($_POST['products_quantity_order_min']),
                            'products_quantity_order_units' => zen_db_prepare_input($_POST['products_quantity_order_units']),
                            'products_priced_by_attribute' => zen_db_prepare_input($_POST['products_priced_by_attribute']),
                            'product_is_free' => zen_db_prepare_input((int)$_POST['product_is_free']),
                            'product_is_call' => zen_db_prepare_input((int)$_POST['product_is_call']),
                            'products_quantity_mixed' => zen_db_prepare_input($_POST['products_quantity_mixed']),
                            'product_is_always_free_shipping' => zen_db_prepare_input((int)$_POST['product_is_always_free_shipping']),
                            'products_qty_box_status' => zen_db_prepare_input($_POST['products_qty_box_status']),
                            'products_quantity_order_max' => zen_db_prepare_input($_POST['products_quantity_order_max']),
                            'products_sort_order' => (int)zen_db_prepare_input($_POST['products_sort_order']),
                            'products_discount_type' => zen_db_prepare_input($_POST['products_discount_type']),
                            'products_discount_type_from' => zen_db_prepare_input($_POST['products_discount_type_from']),
                            'products_price_sorter' => zen_db_prepare_input($_POST['products_price_sorter'])
                            );