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?