Discovered while viewing a customer's order in admin:
criteria to reproduce:
product has attributes, but product is not tracked by SBA and STOCK_SBA_DISPLAY_CUSTOMID == 'true' (ie. expecting to have the custom_id displayed).
SQL error thrown at line 794 of admin/includes/classes/products_with_attributes_stock.php
Error is equivalent to below:
PHP Fatal error: 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 4 :: select customid as products_model
from products_with_attributes_stock
where products_id = 5
and stock_attributes in () ==> (as called by) path_to_admin/includes/classes/products_with_attributes_stock.php on line 794 <== in path_to_store_includes/classes/db/mysql/query_factory.php on line 155
Error may be slightly different on various ZC versions (ie. the line number associated to query_factory.php); however, the above was from running on ZC 1.5.3. The same issue would occur on any version of ZC that this code is run.
How to fix:
replace line 787 with:
if ([B]$attribute_stock->RecordCount() > 0 && [/B]!$customid->RecordCount()){ // if a customid does not exist for the combination of attributes then perhaps the attributes are individually listed.
Original code in this "area":
if(is_array($attributes) and sizeof($attributes) > 0){
// check if attribute stock values have been set for the product
// if there are will we continue, otherwise we'll use product level data
$attribute_stock = $db->Execute("select stock_id
from " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . "
where products_id = " . (int)$products_id . ";");
if ($attribute_stock->RecordCount() > 0) {
// search for details for the particular attributes combination
$first_search = 'where options_values_id in ("'.implode('","',$attributes).'")';
// obtain the attribute ids
$query = 'select products_attributes_id
from '.TABLE_PRODUCTS_ATTRIBUTES.'
'.$first_search.'
and products_id='.$products_id.'
order by products_attributes_id;';
$attributes_new = $db->Execute($query);
while(!$attributes_new->EOF){
$stock_attributes[] = $attributes_new->fields['products_attributes_id'];
$attributes_new->MoveNext();
}
$stock_attributes_comb = implode(',',$stock_attributes);
}
//Get product model
$customid_model_query = 'select products_model
from '.TABLE_PRODUCTS.'
where products_id = '. (int)$products_id . ';';
//Get custom id as products_model
$customid_query = 'select customid as products_model
from '.TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK.'
where products_id = :products_id:
and stock_attributes in (:stock_attributes:);';
$customid_query = $db->bindVars($customid_query, ':products_id:', $products_id, 'integer');
$customid_query = $db->bindVars($customid_query, ':stock_attributes:', $stock_attributes_comb, 'string');
$customid = $db->Execute($customid_query); //moved to inside this loop as for some reason it has made
if (!$customid->RecordCount()){ // if a customid does not exist for the combination of attributes then perhaps the attributes are individually listed.
$customid_query = 'select customid as products_model
from '.TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK.'
where products_id = :products_id:
and stock_attributes in (:stock_attributes:)';
$customid_query = $db->bindVars($customid_query, ':products_id:', $products_id, 'integer');
$customid_query = $db->bindVars($customid_query, ':stock_attributes:', $stock_attributes_comb, 'passthru');
$customid = $db->Execute($customid_query); //moved to inside this loop as for some reason it has made
}
}
Revised Code:
if(is_array($attributes) and sizeof($attributes) > 0){
// check if attribute stock values have been set for the product
// if there are will we continue, otherwise we'll use product level data
$attribute_stock = $db->Execute("select stock_id
from " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . "
where products_id = " . (int)$products_id . ";");
if ($attribute_stock->RecordCount() > 0) {
// search for details for the particular attributes combination
$first_search = 'where options_values_id in ("'.implode('","',$attributes).'")';
// obtain the attribute ids
$query = 'select products_attributes_id
from '.TABLE_PRODUCTS_ATTRIBUTES.'
'.$first_search.'
and products_id='.$products_id.'
order by products_attributes_id;';
$attributes_new = $db->Execute($query);
while(!$attributes_new->EOF){
$stock_attributes[] = $attributes_new->fields['products_attributes_id'];
$attributes_new->MoveNext();
}
$stock_attributes_comb = implode(',',$stock_attributes);
}
//Get product model
$customid_model_query = 'select products_model
from '.TABLE_PRODUCTS.'
where products_id = '. (int)$products_id . ';';
//Get custom id as products_model
$customid_query = 'select customid as products_model
from '.TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK.'
where products_id = :products_id:
and stock_attributes in (:stock_attributes:);';
$customid_query = $db->bindVars($customid_query, ':products_id:', $products_id, 'integer');
$customid_query = $db->bindVars($customid_query, ':stock_attributes:', $stock_attributes_comb, 'string');
$customid = $db->Execute($customid_query); //moved to inside this loop as for some reason it has made
if ([B]$attribute_stock->RecordCount() > 0 && [/B]!$customid->RecordCount()){ // if a customid does not exist for the combination of attributes then perhaps the attributes are individually listed.
$customid_query = 'select customid as products_model
from '.TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK.'
where products_id = :products_id:
and stock_attributes in (:stock_attributes:)';
$customid_query = $db->bindVars($customid_query, ':products_id:', $products_id, 'integer');
$customid_query = $db->bindVars($customid_query, ':stock_attributes:', $stock_attributes_comb, 'passthru');
$customid = $db->Execute($customid_query); //moved to inside this loop as for some reason it has made
}
}
FWIW, the above is expected to be revised again, there are some additional logic functions that can be applied to provide a smoother operation and would have prevented finding that error. Ie., the error was present because any check performed regarding if the products_id in question was tracked by SBA did not properly/promptly address that condition... Now there is actually a function that returns a true/false condition if a product is tracked by SBA and therefore, that can be incorporated up front and any "special" custom_id handling after that. To be addressed in a later change.
