Results Page MySQL error for Price Range Search after ORDER BY mod

I have followed your directions for ordering product results (on the Product_Results.php page) from forum post: showthread.php?t=2077 . The results do get listed in alphabetical order. However; when I use the search form and search for a price range, I get the following error back:

"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 'HAVING (ProductPrice >= 10) AND (ProductPrice <= 3000) LIMIT 0, 5' at line 1"

Any suggestions to fix this ?

Thank you,

Steve

You are using bad SQL for a price range search:
HAVING (ProductPrice >= 10) AND (ProductPrice <= 3000)


should be:

WHERE (ProductPrice >= 10) AND (ProductPrice <= 3000)

Thank you for your quick response and your recognition of the error cause. I am using the Product_Results.php page that comes with PowerStore 2. The only change was to add in the "ORDER BY products.ProductName ASC" at the end of the query string starting at line 155. I added this in based on the forum post referenced in the original message.

How do I fix the SQL in the PowerStore 2 provided page ?

Thank you,

Steve

I have opened you a ticket on this issue so that we can look into the problem further. Please login to WebAssist.com and visit your support history to update the ticket.

The query to get this to work should look like this:
mysql_select_db($database_localhost, $localhost);
$query_WADAProducts = "SELECT *, (ProductStock - (SELECT Coalesce(Sum(DetailQuantity),0) FROM orderdetails INNER JOIN orders ON OrderID = DetailOrderID WHERE DetailProductID = ProductID AND OrderDate > ProductUpdateDate)) AS NumLeft, (SELECT MAX(ProductPrice) FROM products as prod2 INNER JOIN productoptions ON productoptions.ProductID = prod2.ProductID WHERE productoptions.GroupingID = products.ProductID AND prod2.ProductLive <> 0) AS TopPrice, (SELECT MIN(ProductPrice) FROM products as prod2 INNER JOIN productoptions ON productoptions.ProductID = prod2.ProductID WHERE productoptions.GroupingID = products.ProductID AND prod2.ProductLive <> 0) AS BottomPrice FROM products LEFT OUTER JOIN productcategories ON ProductCategoryID = CategoryID WHERE ProductLive <> 0 AND COALESCE((SELECT GroupingID FROM productoptions WHERE productoptions.ProductID = products.ProductID LIMIT 1), ProductID) = ProductID";
setQueryBuilderSource($query_WADAProducts,$WADbSearch2,false);
setQueryBuilderSource($query_WADAProducts,$WADbSearch1,false);
$query_WADAProducts.= preg_replace("/ AND /", " HAVING ", $WADbSearch2->whereClause, 1);
$query_limit_WADAProducts = sprintf("%s %s LIMIT %d, %d", $query_WADAProducts, " ORDER BY ProductPrice ASC", $startRow_WADAProducts, $maxRows_WADAProducts);
$query_WADAProducts.= " ORDER BY ProductPrice ASC";
$WADAProducts = mysql_query($query_limit_WADAProducts, $localhost) or die(mysql_error());
$row_WADAProducts = mysql_fetch_assoc($WADAProducts);

The HAVING clause cannot be after the ORDER BY clause, which was causing an error.

Thanks go to Jimmy and the WebAssist engineering staff for solving this SQL issue.

By the way, it may be obvious but, if one wants to ORDER BY ProductName so that the product get listed alphabetically in ascending order (which was what I wanted to do ), then just replace the ProductPrice with ProductName in the ORDER BY additions.

Steve

Thanks for the input. If you run into any additional issues, feel free to open a new thread.