close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

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

Thread began 12/23/2009 4:29 pm by Infotraveler | Last modified 1/04/2010 4:41 pm by Jimmy Wu | 1518 views | 6 replies |

Infotraveler

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

Sign in to reply to this post

Jason ByrnesWebAssist

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


should be:

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

Sign in to reply to this post

Infotraveler

Using PowerStore SQL

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

Sign in to reply to this post

Eric Mittman

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.

Sign in to reply to this post

Jimmy Wu

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.

Sign in to reply to this post

Infotraveler

If you want to ORDER BY other fieldnames

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

Sign in to reply to this post

Jimmy Wu

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

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...