SQL Error

[Cyberpunk]

Hi,

 

I'm getting an error (when I'm in one of my categories) that I'm not sure how to deal with. Anyone has an idea?

 

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 ') and (ptf.products_extra_fields_id = '8' or ptf.products_extra_fields_id = '8')' at line 5

SELECT DISTINCT pef.products_extra_fields_id as pef_id, pef.products_extra_fields_status as status, pef.products_extra_fields_name as name, ptf.products_extra_fields_value as value FROM products_extra_fields pef LEFT JOIN products_to_products_extra_fields ptf ON ptf.products_extra_fields_id= pef.products_extra_fields_id WHERE ptf.products_id in () and (ptf.products_extra_fields_id = '8' or ptf.products_extra_fields_id = '8') and ptf.products_extra_fields_value<>'' and (pef.languages_id='0' or pef.languages_id='1') order by ptf.products_extra_fields_value asc

[aiyou]

It would appear that you have one or two variables missing from your statement. It may be easier to look at it formatted as follows:

SELECT DISTINCT 
pef.products_extra_fields_id as pef_id, 
pef.products_extra_fields_status as status, 
pef.products_extra_fields_name as name, 
ptf.products_extra_fields_value as value 
FROM products_extra_fields pef 
LEFT JOIN products_to_products_extra_fields ptf 
ON ptf.products_extra_fields_id= pef.products_extra_fields_id 
WHERE ptf.products_id in () 
and (ptf.products_extra_fields_id = '8' or ptf.products_extra_fields_id = '8') 
and ptf.products_extra_fields_value<>'' 
and (pef.languages_id='0' or pef.languages_id='1') 
order by ptf.products_extra_fields_value asc

 

Issue 1: You're missing one or more values in your ptf.products_id list...line 1 of your WHERE clause

WHERE ptf.products_id in () 

 

Issue 2: You may be missing a value in your ptf.products_extra_fields_value (line 3) of the WHERE clause (not sure if this should contain an actual value, of if it is testing for an empty value)

and ptf.products_extra_fields_value<>'' 

 

I'm not familiar with this SQL statement specifically, but it looks as if some variable(s) was/were missed when installing the contribution.

[MrPhil]

I'm not familiar with this SQL statement specifically, but it looks as if some variable(s) was/were missed when installing the contribution.

 

It's worse than that. osC has a number of places that have caused problems for people when a list to check against is empty: WHERE ... field IN (). The proper solution is to build the list in a separate variable, and check that it is not empty before putting field IN (list) into the SELECT.

 

For example, instead of

tep_db_query(
  "DELETE FROM " . TABLE_CONFIGURATION . 
	 " WHERE configuration_key IN ('" . implode("', '", $this->keys()) . "')");

, we would

$where_in = tep_IN_list('configuration_key', implode("', '", $this->keys()), "'");
  // do not do deletion if list is empty
  if ($where_in != '') {
	tep_db_query(
			"DELETE FROM " . TABLE_CONFIGURATION . 
			" WHERE " . $where_in );
  }

 

tep_IN_list (added to general.php):

// build IN or NOT IN phrase for a WHERE clause, so can test if empty and omit
//  $field is name to compare against $list
//  $quote is extra ' or " to wrap around first and last members
//  $not may be empty or NOT
function tep_IN_list($field, $list, $quote='', $not='') {
// first, is $list empty or nothing but blanks?
if (trim($list) == '') return '';

// $list has something, so use it and build a phrase
$result = $field;
$result .= ($not == '')? '': ' NOT';
$result .= ' IN (' . $quote . $list . $quote . ')';

return $result; 
 }

[Cyberpunk]

Thanks!

I'm gonna give this a try!

[MrPhil]

Caution: while use of 'tep_IN_list()' will prevent SQL errors, it's a case-by-case judgment call when it's used, as to what should be done when the list turns out to be empty. Sometimes the "IN (list)" clause can simply be dropped from the query, while other times it should cause the whole query to be scrapped (or even worse, fail the whole operation). What I gave was just one example.