Page 1 of 2

What's wrong with this syntax?

Posted: Sat Nov 18, 2006 12:13 pm
by tristanlee85

Code: Select all

$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

Posted: Sat Nov 18, 2006 12:20 pm
by feyd
Why do you have parentheses around your selection field specifiers?
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:1. Select the correct board for your query. Take some time to read the guidelines in the sticky topic.

Posted: Sat Nov 18, 2006 12:26 pm
by tristanlee85
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.

Here is the post for reference: viewtopic.php?t=43429&highlight=

Doing this worked in the other queries when there were table joins.

Posted: Sat Nov 18, 2006 12:33 pm
by feyd
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..

Posted: Sat Nov 18, 2006 12:43 pm
by tristanlee85
So then how about this code:

Code: Select all

$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

Posted: Sat Nov 18, 2006 1:05 pm
by feyd
You have an "and" immediately before $avail_condition.

Posted: Sat Nov 18, 2006 1:14 pm
by tristanlee85
Hmm. Not it. Looks I need to save up $200 for the new version.

Posted: Sat Nov 18, 2006 1:19 pm
by feyd
That would say that $avail_condition is bad/wrong. You've actually got several spots in your final SQL that are missing values.

xcart_products.avail>= OR

for example.

Posted: Sat Nov 18, 2006 3:04 pm
by califdon
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...???

Posted: Sat Nov 18, 2006 3:15 pm
by tristanlee85
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.

Posted: Sat Nov 18, 2006 3:24 pm
by volka
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.

Whats $amount supposed to be in
tristanlee85 wrote:

Code: Select all

$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.

Posted: Sat Nov 18, 2006 3:44 pm
by tristanlee85
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.

Posted: Sat Nov 18, 2006 3:51 pm
by volka
tristanlee85 wrote:I just know the files were transferred from a MySQL4 server to a MySQL5 server and that's when stuff started not working.
Then maybe not only the mysql version has changed but e.g. the php version/settings as well.

Posted: Sat Nov 18, 2006 4:23 pm
by tristanlee85
Hmm. On my older server I have PHP5 and MySQL4. This server has PHP5 and MySQL5 and I'm using ISPconfig for my server computer. I honestly don't know.

Posted: Sat Nov 18, 2006 5:15 pm
by georgeoc
Perhaps an issue with register_globals?