$products_array = func_query_first("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[products].productid='$productid') and ($avail_condition $sql_tbl[pricing].quantity<=$amount and $sql_tbl[pricing].membership='$membership' or $sql_tbl[pricing].membership='') AND ($sql_tbl[pricing].variantid = 0) group by ($sql_tbl[products].productid) order by ($sql_tbl[pricing].quantity) desc");
I keep getting this error when I try to display the page:
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 '*, min(xcart_pricing.price)) as price from (xcart_products, xcart_pricing) where' at line 1
SQL QUERY FAILURE: select (xcart_products.*, min(xcart_pricing.price)) as price from (xcart_products, xcart_pricing) where (xcart_pricing.productid=xcart_products.productid) and (xcart_products.productid='') and ((xcart_products.avail>= OR xcart_products.product_type='C') AND xcart_pricing.quantity<= and xcart_pricing.membership='Premium' or xcart_pricing.membership='') AND (xcart_pricing.variantid = 0) group by (xcart_products.productid) order by (xcart_pricing.quantity) desc
Because the code was written for MySQL4 and I have MySQL5 and from what I've read I have to go through and seperate everything with parentheses to get it to function properly on MySQL5.
If you look more closely at that post, there are no parentheses surrounding selected fields. Individual fields have parentheses to manipulate them or run functionality on them..
$products_array = func_query_first("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[products].productid='$productid' and $avail_condition $sql_tbl[pricing].quantity<=$amount and ($sql_tbl[pricing].membership='$membership' or $sql_tbl[pricing].membership='') AND $sql_tbl[pricing].variantid = 0 group by $sql_tbl[products].productid order by $sql_tbl[pricing].quantity desc");
It's the original and I still get the same 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 'OR xcart_products.product_type='C') AND xcart_pricing.quantity<= and (xcart_pri' at line 1 SQL QUERY FAILURE: select xcart_products.*, min(xcart_pricing.price) as price from xcart_products, xcart_pricing where xcart_pricing.productid=xcart_products.productid and xcart_products.productid='' and (xcart_products.avail>= OR xcart_products.product_type='C') AND xcart_pricing.quantity<= and (xcart_pricing.membership='Premium' or xcart_pricing.membership='') AND xcart_pricing.variantid = 0 group by xcart_products.productid order by xcart_pricing.quantity desc
Am I not seeing something? The two error messages you show don't appear to even be referring to the SQL statement you quoted. The error messages refer to "xcart_products" which I don't even see in your SQL. Maybe the error is coming from an entirely different part of your script...???
tristanlee85 wrote:It all has to do with the way MySQL5 handles sutff. If it were easy enough to switch back to MySQL4, it would work, but I have no idea how to do that.
Nope. This query
tristanlee85 wrote:
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 'OR xcart_products.product_type='C') AND xcart_pricing.quantity<= and (xcart_pri' at line 1 SQL QUERY FAILURE: select xcart_products.*, min(xcart_pricing.price) as price from xcart_products, xcart_pricing where xcart_pricing.productid=xcart_products.productid and xcart_products.productid='' and (xcart_products.avail>= OR xcart_products.product_type='C') AND xcart_pricing.quantity<= and (xcart_pricing.membership='Premium' or xcart_pricing.membership='') AND xcart_pricing.variantid = 0 group by xcart_products.productid order by xcart_pricing.quantity desc
is invalid in mysql4 (and certainly all previous versions), too.
$products_array = func_query_first("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[products].productid='$productid' and $avail_condition $sql_tbl[pricing].quantity<=$amount and ($sql_tbl[pricing].membership='$membership' or $sql_tbl[pricing].membership='') AND $sql_tbl[pricing].variantid = 0 group by $sql_tbl[products].productid order by $sql_tbl[pricing].quantity desc");
?
Obviously it is not set or holds no (string) value.
I do not know. I didn't write the software. I just know the files were transferred from a MySQL4 server to a MySQL5 server and that's when stuff started not working. If I had a MySQL4 server right here, the shopping cart would run flawlessly.