add to cart button MM_ERROR

[email377288]

hi, when i try and edit the add to cart button on the Product_Detail.php page the general tab shows

Look up item ID from recordset

Recordset: WADAProducts
ID Column: MySQL 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 2

My database connection is good, and when i test the WADAProducts Recordset Query in dreamweaver and MySQL it works fine.

any ideas?

[Ray Borduin]

What is your recordset Query? Push the test button to refresh it from the database.

[email377288]

the error shows on a fresh install of powerstore, the recordset query is still the default, i have tried refreshing using the test button but the error remains. here is the query:

<?php
$ParamProductID_WADAProducts = "-1";
if (isset($_GET['ProductID'])) {
$ParamProductID_WADAProducts = (get_magic_quotes_gpc()) ? $_GET['ProductID'] : addslashes($_GET['ProductID']);
}
$ParamSessionProductID_WADAProducts = "-1";
if (isset($_SESSION['WADA_Insert_Products'])) {
$ParamSessionProductID_WADAProducts = (get_magic_quotes_gpc()) ? $_SESSION['WADA_Insert_Products'] : addslashes($_SESSION['WADA_Insert_Products']);
}
$ParamProductID2_WADAProducts = "-1";
if (isset($_GET['ProductID'])) {
$ParamProductID2_WADAProducts = (get_magic_quotes_gpc()) ? $_GET['ProductID'] : addslashes($_GET['ProductID']);
}
mysql_select_db($database_localhost, $localhost);
$query_WADAProducts = sprintf("SELECT * , (ProductStock - (SELECT Coalesce(Sum(DetailQuantity),0) FROM orderdetails INNER JOIN orders ON OrderID = DetailOrderID WHERE DetailProductID = ProductID AND OrderDate > ProductUpdateDate)) AS NumLeft FROM products WHERE ProductID = %s OR ( -1= %s AND ProductID= %s) AND ProductLive <> 0", GetSQLValueString($ParamProductID_WADAProducts, "int"),GetSQLValueString($ParamProductID2_WADAProducts, "int"),GetSQLValueString($ParamSessionProductID_WADAPro ducts, "int"));
$WADAProducts = mysql_query($query_WADAProducts, $localhost) or die(mysql_error());
$row_WADAProducts = mysql_fetch_assoc($WADAProducts);
$totalRows_WADAProducts = mysql_num_rows($WADAProducts);
?>

[Ray Borduin]

OK, I am able to reproduce this problem. It appears this is a bug in ecart where it won't work properly with complex SQL statements.

The temporary fix would be to update your SQL to:
SELECT * FROM products WHERE ProductID = %s OR ( -1= %s AND ProductID= %s) AND ProductLive <> 0

Then after editing the add to cart you can change the SQL query back so that it can properly track inventory. It appears the add to cart interface is getting confused by the nested select statements.

I will log this as a bug in eCart that we can look into in the next release.