CODEWITHSUNDEEP
phpmysqlsyntax
James Gardner
James Gardner

Reputation: 29

Mysql syntax error most popular

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?

Upvotes: 1

Views: 46

Answers (2)

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

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

Upvotes: 1

Mark
Mark

Reputation: 8441

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',...)

Upvotes: 2

Related Questions