View Single Post
  #18  
Old 04-30-2006, 09:14 AM
 
geckoday geckoday is offline
 

X-Wizard
  
Join Date: Aug 2005
Posts: 1,073
 

Default

Quote:
Originally Posted by xcell67

...

but now the cart shows this error:

INVALID SQL: 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 'SELECT MIN(xcart_pricing.price) FROM xcart_pricing WHERE xcart_
SQL QUERY FAILURE:SELECT xcart_products.*, (SELECT MIN(xcart_pricing.price) FROM xcart_pricing WHERE xcart_pricing.productid=xcart_products.productid AND xcart_pricing.quantity=1 AND xcart_pricing.variantid = 0) AS price FROM xcart_products,xcart_pricing WHERE forsale='Y' AND avail>0 ORDER BY RAND() LIMIT 6

What version of MySQL are you using? The subquery used requires MySQL 4.1. Also, I found an error in the query that creates duplicate rows. Here's the fixed query also rewritten to work with earlier versions of MYSQL:

Code:
$query = "SELECT $sql_tbl[products].*, MIN($sql_tbl[pricing].price) AS price FROM $sql_tbl[products],$sql_tbl[pricing] WHERE $sql_tbl[pricing].productid=$sql_tbl[products].productid AND $sql_tbl[pricing].quantity=1 AND $sql_tbl[pricing].variantid = 0 AND forsale='Y' AND avail>0 GROUP BY $sql_tbl[products].productid ORDER BY RAND() LIMIT 6 ";
__________________
Manuka Bay Company
X-Cart Version 4.0.19 [Linux]

UGG Boots and other fine sheepskin products
http://www.snowriver.com
Reply With Quote