Mysql 5 strict mode no null value for manufacturer id

[jiffy]

I need some help. I've got a 1.3.8 site I've been setting up for a couple of months and it's been mostly humming along well but today I noticed a problem if I select "none" for the manufacturer on a new product or when editing a product. I think this has to do with Mysql 5 running in strict mode. So how do I fix this? As a temporary fix I made a manufacturer called "not specified" and select that but then it calls up the manufacturer side box and also shows it in the product details which I'd rather it didn't. Oh and I should probably mention this is running on a Windows Server 2003 box.

This is the error I'm getting.

1366 Incorrect integer value: '' for column 'manufacturers_id' at row 1
in:
[update products set products_quantity = '2', products_type = '1', products_model = 'NEXY', products_price = '54.9500', products_date_available = null, products_weight = '6', products_status = '1', products_virtual = '0', products_tax_class_id = '1', manufacturers_id = '', products_quantity_order_min = '1', products_quantity_order_units = '1', products_priced_by_attribute = '0', map_enabled = '1', map_price = '0.0000', product_is_free = '0', product_is_call = '0', products_quantity_mixed = '1', product_is_always_free_shipping = '0', products_qty_box_status = '1', products_quantity_order_max = '0', products_sort_order = '0', products_discount_type = '0', products_discount_type_from = '0', products_price_sorter = '54.9500', products_image = 'prods_kites/nexusyellow.jpg', products_last_modified = now(), master_categories_id = '12' where products_id = '16']
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.

[DrByte]

What addons/contributions do you have installed?

What do you see on lines 27-39 of /admin/includes/modules/update_product.php?

It should read this:

    $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($_POST['products_status']),
                            'products_virtual' => zen_db_prepare_input($_POST['products_virtual']),
                            'products_tax_class_id' => zen_db_prepare_input($_POST['products_tax_class_id']),
                            'manufacturers_id' => $manufacturers_id,

Do you perhaps have a customized version of /admin/includes/modules/update_product.php located at /admin/includes/modules/product/update_product.php? (normally you would *not* have this extra file for the "product" prod-type)

[jiffy]

This is what I have for lines 27-39.

    $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' => zen_db_prepare_input($_POST['products_quantity']),
                            'products_type' => zen_db_prepare_input($_GET['product_type']),
                            'products_model' => zen_db_prepare_input($_POST['products_model']),
                            'products_price' => zen_db_prepare_input($_POST['products_price']),
                            'products_date_available' => $products_date_available,
                            'products_weight' => zen_db_prepare_input($_POST['products_weight']),
                            'products_status' => zen_db_prepare_input($_POST['products_status']),
                            'products_virtual' => zen_db_prepare_input($_POST['products_virtual']),
                            'products_tax_class_id' => zen_db_prepare_input($_POST['products_tax_class_id']),
                            'manufacturers_id' => zen_db_prepare_input($_POST['manufacturers_id']),

I just thought I did install the map pricing mod a couple of weeks ago. Could that be causing a problem? I noticed if I leave that box blank instead of entering either "0" or an actual map price value I get a similar error.

Here is the whole update_product.php file if it helps any.

<?php
/**
 * @package admin
 * @copyright Copyright 2003-2006 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: update_product.php 4178 2006-08-20 18:47:54Z ajeh $
 */
  if (!defined('IS_ADMIN_FLAG')) {
    die('Illegal Access');
  }
  if (isset($_POST['edit_x']) || isset($_POST['edit_y'])) {
    $action = 'new_product';
  } else {
    if (isset($_GET['pID'])) $products_id = zen_db_prepare_input($_GET['pID']);
    $products_date_available = zen_db_prepare_input($_POST['products_date_available']);

    $products_date_available = (date('Y-m-d') < $products_date_available) ? $products_date_available : 'null';

    // 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' => zen_db_prepare_input($_POST['products_quantity']),
                            'products_type' => zen_db_prepare_input($_GET['product_type']),
                            'products_model' => zen_db_prepare_input($_POST['products_model']),
                            'products_price' => zen_db_prepare_input($_POST['products_price']),
                            'products_date_available' => $products_date_available,
                            'products_weight' => zen_db_prepare_input($_POST['products_weight']),
                            'products_status' => zen_db_prepare_input($_POST['products_status']),
                            'products_virtual' => zen_db_prepare_input($_POST['products_virtual']),
                            'products_tax_class_id' => zen_db_prepare_input($_POST['products_tax_class_id']),
                            'manufacturers_id' => zen_db_prepare_input($_POST['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']),
							//MAP Pricing by SlickRicky Design : http://www.slickricky.com : START
                            'map_enabled' => zen_db_prepare_input($_POST['map_enabled']),
                            'map_price' => zen_db_prepare_input($_POST['map_price']),
							//MAP Pricing by SlickRicky Design : http://www.slickricky.com : END
                            'product_is_free' => zen_db_prepare_input($_POST['product_is_free']),
                            'product_is_call' => zen_db_prepare_input($_POST['product_is_call']),
                            'products_quantity_mixed' => zen_db_prepare_input($_POST['products_quantity_mixed']),
                            'product_is_always_free_shipping' => zen_db_prepare_input($_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' => 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'])
                            );

    // when set to none remove from database
    // if (isset($_POST['products_image']) && zen_not_null($_POST['products_image']) && ($_POST['products_image'] != 'none')) {
    if (isset($_POST['products_image']) && zen_not_null($_POST['products_image']) && (!is_numeric(strpos($_POST['products_image'],'none'))) ) {
      $sql_data_array['products_image'] = zen_db_prepare_input($_POST['products_image']);
      $new_image= 'true';
    } else {
      $sql_data_array['products_image'] = '';
      $new_image= 'false';
    }

if ($_POST['image_delete'] == 1) {
      $sql_data_array['products_image'] = '';
      $new_image= 'false';
}

    if ($action == 'insert_product') {
      $insert_sql_data = array( 'products_date_added' => 'now()',
                                'master_categories_id' => (int)$current_category_id);

      $sql_data_array = array_merge($sql_data_array, $insert_sql_data);

      zen_db_perform(TABLE_PRODUCTS, $sql_data_array);
      $products_id = zen_db_insert_id();

      // reset products_price_sorter for searches etc.
      zen_update_products_price_sorter($products_id);

      $db->Execute("insert into " . TABLE_PRODUCTS_TO_CATEGORIES . "
                    (products_id, categories_id)
                    values ('" . (int)$products_id . "', '" . (int)$current_category_id . "')");

      ///////////////////////////////////////////////////////
      //// INSERT PRODUCT-TYPE-SPECIFIC *INSERTS* HERE //////


      ////    *END OF PRODUCT-TYPE-SPECIFIC INSERTS* ////////
      ///////////////////////////////////////////////////////
    } elseif ($action == 'update_product') {
      $update_sql_data = array( 'products_last_modified' => 'now()',
                                'master_categories_id' => ($_POST['master_category'] > 0 ? zen_db_prepare_input($_POST['master_category']) : zen_db_prepare_input($_POST['master_categories_id'])));

      $sql_data_array = array_merge($sql_data_array, $update_sql_data);

      zen_db_perform(TABLE_PRODUCTS, $sql_data_array, 'update', "products_id = '" . (int)$products_id . "'");

      // reset products_price_sorter for searches etc.
      zen_update_products_price_sorter((int)$products_id);

      ///////////////////////////////////////////////////////
      //// INSERT PRODUCT-TYPE-SPECIFIC *UPDATES* HERE //////


      ////    *END OF PRODUCT-TYPE-SPECIFIC UPDATES* ////////
      ///////////////////////////////////////////////////////
    }

    $languages = zen_get_languages();
    for ($i=0, $n=sizeof($languages); $i<$n; $i++) {
      $language_id = $languages[$i]['id'];

      $sql_data_array = array('products_name' => zen_db_prepare_input($_POST['products_name'][$language_id]),
                              'products_description' => zen_db_prepare_input($_POST['products_description'][$language_id]),
                              'products_url' => zen_db_prepare_input($_POST['products_url'][$language_id]));

      if ($action == 'insert_product') {
        $insert_sql_data = array('products_id' => $products_id,
                                 'language_id' => $language_id);

        $sql_data_array = array_merge($sql_data_array, $insert_sql_data);

        zen_db_perform(TABLE_PRODUCTS_DESCRIPTION, $sql_data_array);
      } elseif ($action == 'update_product') {
        zen_db_perform(TABLE_PRODUCTS_DESCRIPTION, $sql_data_array, 'update', "products_id = '" . (int)$products_id . "' and language_id = '" . (int)$language_id . "'");
      }
    }

    // add meta tags
    $languages = zen_get_languages();
    for ($i=0, $n=sizeof($languages); $i<$n; $i++) {
      $language_id = $languages[$i]['id'];

      $sql_data_array = array('metatags_title' => zen_db_prepare_input($_POST['metatags_title'][$language_id]),
                              'metatags_keywords' => zen_db_prepare_input($_POST['metatags_keywords'][$language_id]),
                              'metatags_description' => zen_db_prepare_input($_POST['metatags_description'][$language_id]));

      if ($action == 'insert_product_meta_tags') {

        $insert_sql_data = array('products_id' => $products_id,
                                 'language_id' => $language_id);

        $sql_data_array = array_merge($sql_data_array, $insert_sql_data);

        zen_db_perform(TABLE_META_TAGS_PRODUCTS_DESCRIPTION, $sql_data_array);
      } elseif ($action == 'update_product_meta_tags') {
        zen_db_perform(TABLE_META_TAGS_PRODUCTS_DESCRIPTION, $sql_data_array, 'update', "products_id = '" . (int)$products_id . "' and language_id = '" . (int)$language_id . "'");
      }
    }


    // future image handler code
    define('IMAGE_MANAGER_HANDLER', 0);
    define('DIR_IMAGEMAGICK', '');
    if ($new_image == 'true' and IMAGE_MANAGER_HANDLER >= 1) {
      $src= DIR_FS_CATALOG . DIR_WS_IMAGES . zen_get_products_image((int)$products_id);
      $filename_small= $src;
      preg_match("/.*\/(.*)\.(\w*)$/", $src, $fname);
      list($oiwidth, $oiheight, $oitype) = getimagesize($src);

      $small_width= SMALL_IMAGE_WIDTH;
      $small_height= SMALL_IMAGE_HEIGHT;
      $medium_width= MEDIUM_IMAGE_WIDTH;
      $medium_height= MEDIUM_IMAGE_HEIGHT;
      $large_width= LARGE_IMAGE_WIDTH;
      $large_height= LARGE_IMAGE_HEIGHT;

      $k = max($oiheight / $small_height, $oiwidth / $small_width); //use smallest size
      $small_width = round($oiwidth / $k);
      $small_height = round($oiheight / $k);

      $k = max($oiheight / $medium_height, $oiwidth / $medium_width); //use smallest size
      $medium_width = round($oiwidth / $k);
      $medium_height = round($oiheight / $k);

      $large_width= $oiwidth;
      $large_height= $oiheight;

      $products_image = zen_get_products_image((int)$products_id);
      $products_image_extension = substr($products_image, strrpos($products_image, '.'));
      $products_image_base = ereg_replace($products_image_extension, '', $products_image);

      $filename_medium = DIR_FS_CATALOG . DIR_WS_IMAGES . 'medium/' . $products_image_base . IMAGE_SUFFIX_MEDIUM . '.' . $fname[2];
      $filename_large = DIR_FS_CATALOG . DIR_WS_IMAGES . 'large/' . $products_image_base . IMAGE_SUFFIX_LARGE . '.' . $fname[2];

      // ImageMagick
      if (IMAGE_MANAGER_HANDLER == '1') {
        copy($src, $filename_large);
        copy($src, $filename_medium);
        exec(DIR_IMAGEMAGICK . "mogrify -geometry " . $large_width . " " . $filename_large);
        exec(DIR_IMAGEMAGICK . "mogrify -geometry " . $medium_width . " " . $filename_medium);
        exec(DIR_IMAGEMAGICK . "mogrify -geometry " . $small_width . " " . $filename_small);
      }
    }

    zen_redirect(zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products_id . (isset($_GET['page']) ? '&page=' . $_GET['page'] : '')));
  }
?>

[DrByte]

Compare your line 39 vs the one I posted, which also comes from the original Zen Cart files.

[jiffy]

You rock! That fixed it. Any idea why that line was not correct?

[DrByte]

Probably some addon, or maybe a step missed in an upgrade.