Mysql syntax error most popular

Question

I am trying to retrieve the top 5 values in a certain column in a mysql database.

I have the following query:

SELECT `dep_aerodrome`,
       Count(`dep_aerodrome`) AS `cnt`
FROM   sectors
WHERE ( `group` = '".$_SESSION['GROUP']."'
        AND ( $bases ) NOT IN dep_aerodrome )
GROUP  BY `dep_aerodrome`
ORDER  BY `cnt` DESC
LIMIT  5

The query contains a WHERE clause and NOT IN to not include airports that are registered as bases.

I am getting the following syntax error:

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 'dep_aerodrome) GROUP BY dep_aerodrome ORDER BY cnt DESC LIMIT 5' at line 1

and I cannot figure out where it is coming from. Can anyone help out?

MySQL does not care about your PHP script, but only about the query it is actually receiving – so please be a little bit smarter and show us the latter one as well, thank you. — C3roe
– C3roe, Commented Jul 29, 2013 at 9:50

Mark · Accepted Answer · 2013-07-29 09:54:46Z

2

Try this:

SELECT `dep_aerodrome`,
       Count(`dep_aerodrome`) AS `cnt`
FROM   sectors
WHERE `group` = '".$_SESSION['GROUP']."'
        AND ( $bases ) 
        AND dep_aerodrome NOT IN ($some_values)
GROUP  BY `dep_aerodrome`
ORDER  BY `cnt` DESC
LIMIT  5

I think you want to do this like this. $some_values can be in the format of 'value1','value2',... as in:

dep_aerodrome NOT IN ('value1','value2',...)

answered Jul 29, 2013 at 9:54

Mark

8,47115 gold badges52 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Gardner Over a year ago

Thank you, I had the format wrong for the NOT IN $bases, was missing single quotes around the csv's.

Community · Accepted Answer · 2017-05-23 10:25:16Z

1

The right syntax for using NOT IN is

WHERE column_name NOT IN ('value1','value2',...)

And If you want to match 1 value with multiple columns check this.

Different approach of using IN clause in MySql

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Jul 29, 2013 at 9:50

Yogesh Suthar

30.5k18 gold badges75 silver badges100 bronze badges

1 Comment

James Gardner Over a year ago

Thank you, I had the format wrong for the NOT IN $bases, was missing single quotes around the csv's.

Collectives™ on Stack Overflow

Mysql syntax error most popular

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related