Can someone help me with a MySQL fix ?

[milobloom]

Sometimes my products get messed up.

Probably because of the Products With Attributes Stock Mod.

I get this error in the product listing

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 pa2.products_attributes_id IN (7142) ' at line 3
in:
[SELECT count(DISTINCT pa1.options_id) AS matches FROM products_attributes pa1, products_attributes pa2 WHERE pa1.products_attributes_id IN () AND pa2.products_attributes_id IN (7142) AND pa1.options_id = pa2.options_id]

Size

Is there something somewhere I should remove???

[DrByte]

"WHERE pa1.products_attributes_id IN ()"
You're telling it to lookup a products_attributes_id in the list of values specified inside the parentheses, but your list is empty, thus you're creating a MySQL syntax error. Hence the error message.

[milobloom]

WHERE pa1.products_attributes_id IN ()

Oh Ok I see that! But where do I go to change it? I am trying to use the developers tool kit but cannot seem to come up with anything.

[DrByte]

try searching for WHERE pa1.products_attributes_id IN
or just pa1.
or pa2.

You won't find the () directly because the list is probably being built dynamically (it wouldn't make sense to hard-code an empty list which causes a syntax error)

[milobloom]

2 Instances of pa2.products_attributes_id

admin//includes/functions/extra_functions/functions_qty_attribute.php

[QUOTE]<?php
/*
* Returns the nubmer of attributes that have a quantity associated.
* jstephens added.
*/
function zen_qty_product_attributes_count($products_id) {
global $db;
$query = "SELECT stock_attributes
FROM " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . "
WHERE products_id = " . (int)$products_id . "
LIMIT 1";
$res = $db->Execute($query);
if($res->EOF) return 0;
return count(explode(',',$res->fields['stock_attributes']));
}


/*
* Tells if the needed options are selected for an attribute stock item.
*/
function zen_product_has_attributes_needed_for_qty($products_id, $attibutes) {
global $db;
$query = "SELECT stock_attributes
FROM " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . "
WHERE products_id = " . (int)$products_id . "
LIMIT 1";
$res = $db->Execute($query);
if($res->EOF) return true;//not qty by attrib, don't need anything
if(!is_array($attributes)) return false;//is qty by attrib, but no attrib specified, so "no".

//check if product_options_id is the same, we have products_attributes_id
//Okay, so I made a query the would give me the info:
/* SELECT pov1.products_options_id
FROM

(products_attributes pa1 INNER JOIN products_options_values_to_products_options pov1
ON pa1.options_values_id = pov1.products_options_values_id)

INNER JOIN

(products_attributes pa2 INNER JOIN products_options_values_to_products_options pov2
ON pa2.options_values_id = pov2.products_options_values_id)

WHERE pa1.products_attributes_id IN ( $atr )
AND pa2.products_attributes_id IN ( $atr2 )
AND pov1.products_options_id = pov2.products_options_id;


Only to find out that options_id is in products_attributes.
I hope you get a laugh out of that.
*/
$needed = count(explode(',',$res->fields['stock_attributes']));
$atr = implode(',',$attributes);
$atr2 = $res->fields['stock_attributes'];
$res2 = $db->Execute("SELECT count(DISTINCT pa1.options_id) AS matches
FROM products_attributes pa1, products_attributes pa2
WHERE pa1.products_attributes_id IN ($atr)
AND pa2.products_attributes_id IN ($atr2)
AND pa1.options_id = pa2.options_id;");
if($res2->fields['count'] == $needed) return true;
if($res2->fields['count'] > $needed) echo("Internal database attribute quantity error.<br>");
return false;
}

/* Takes products_attributes_id and returns if it is an option that is selctable (like dropdown, but not like text) */
function zen_attribute_can_be_qty($attribute)
{
global $db;
$res = $db->Execute("SELECT pot.products_options_types_name AS name
FROM products_attributes pa INNER JOIN
(products_options po INNER JOIN products_options_types pot
ON po.products_options_type = pot.products_options_types_id)
ON pa.options_id = po.products_options_id
WHERE pa.products_attributes_id = " . (int)$attribute);
$type = $res->fields['name'];
$good = array("Dropdown","Radio");
if(in_array($type,$good)) return true;
return false;
}

2nd was to big... Ill put it the next one...

[milobloom]

From includes/functions/functions_lookups.php

$res2 = $db->Execute("SELECT DISTINCT pa1.products_attributes_id AS needed
FROM products_attributes pa1, products_attributes pa2
WHERE pa1.products_attributes_id IN ($atr)
AND pa2.products_attributes_id IN ($atr2)
AND pa1.options_id = pa2.options_id
ORDER BY pa1.products_attributes_id");

[milobloom]

Not sure what to do.... Is there a statement I can do to try to ignore that list or something?

[DrByte]

a) $atr = implode(',',$attributes);
Apparently $attributes is an empty array, else the list wouldn't be empty.

b) $attributes comes from here: function zen_product_has_attributes_needed_for_qty($products_id, $attibutes) {
So, that tells you that whatever line of code is calling zen_product_has_attributes_needed_for_qty() is passing an empty array.

[milobloom]

This is a little out of my realm..

Can I comment out

$atr = implode(',',$attributes);

Or what would be a better fix?

[DrByte]

Things like this aren't a quick fix. You need to trace the code back to what called it and see what the intention was, and whether the data being passed is wrong because of bad data in the database, or something wrong with the form data being output to the screen and then submitted back to the script when the page is submitted, or whether there's a bug in the code that's doing the calling of the function that's discovering the bad data, etc.
VERY SELDOM is it appropriate to just "comment out" a line that's reporting a problem. That's a very bad approach to troubleshooting.

This is ONLY a guess based on a hunch, as I've not studied the addon in extensive detail: Maybe change this line:

if(!is_array($attributes)) return false;//is qty by attrib, but no attrib specified, so "no".

to this:

if(!is_array($attributes) || sizeof($attributes) == 0) return false;//is qty by attrib, but no attrib specified, so "no".

That may get rid of the error condition, but I've no idea if that's actually going to accomplish the correct end result for the case you're encountering. USE AT OWN RISK.