What's wrong with this syntax?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

tristanlee85
Forum Contributor
Posts: 172
Joined: Fri Dec 19, 2003 7:28 am

What's wrong with this syntax?

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
tristanlee85
Forum Contributor
Posts: 172
Joined: Fri Dec 19, 2003 7:28 am

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
tristanlee85
Forum Contributor
Posts: 172
Joined: Fri Dec 19, 2003 7:28 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You have an "and" immediately before $avail_condition.
tristanlee85
Forum Contributor
Posts: 172
Joined: Fri Dec 19, 2003 7:28 am

Post by tristanlee85 »

Hmm. Not it. Looks I need to save up $200 for the new version.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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...???
tristanlee85
Forum Contributor
Posts: 172
Joined: Fri Dec 19, 2003 7:28 am

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
tristanlee85
Forum Contributor
Posts: 172
Joined: Fri Dec 19, 2003 7:28 am

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
tristanlee85
Forum Contributor
Posts: 172
Joined: Fri Dec 19, 2003 7:28 am

Post 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.
georgeoc
Forum Contributor
Posts: 166
Joined: Wed Aug 09, 2006 4:21 pm
Location: London, UK

Post by georgeoc »

Perhaps an issue with register_globals?
Post Reply