The correct way to use NOT IN in a mysql query

Question

I have a mysql query that throws an error which I'm guessing is because of my judicious use of the phrase "NOT IN":

$sqlGetCountry = mysqli_query($link, "SELECT * FROM locations WHERE country='$country' AND CURTIME() > time AND '$state' NOT IN state ORDER BY time desc LIMIT 20");
$sqlNumCountry = mysqli_num_rows($sqlGetCountry);

I have a table with city, state, and country and I'm basically trying to find the queries where a given state ($state in this case, which could be Texas, Hawaii, etc.) is not in the results. I get the error:

mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

Anybody have a clue?

Or are you trying to find rows not matching $state? Or if $state doesn't exist in the entire table? — Michael Berkowski
– Michael Berkowski, Commented Jul 26, 2012 at 18:56
I read your question 5 times now, and I think you'd come across a lot clearer with some sample data and desired output — lc.
– lc., Commented Jul 26, 2012 at 19:00
"I have a table with city, state, and country". state is a row in the table locations. city, state, and country are all rows in the table locations. @Michael, I'm trying to find rows not matching $state. — John Bowlinger
– John Bowlinger, Commented Jul 26, 2012 at 19:20

Andomar · Accepted Answer · 2012-07-26 18:56:07Z

2

You can't pass a table to in, but you can pass a subquery:

SELECT  * 
FROM    locations 
WHERE   country='$country' 
        AND CURTIME() > time 
        AND '$state' NOT IN (select state from state)
ORDER BY 
       time desc 
LIMIT  20

answered Jul 26, 2012 at 18:56

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

3 Comments

John Bowlinger Over a year ago

Oddly, I get the same results.

Andomar Over a year ago

On rereading the question, perhaps you mena '$state' <> state? Otherwise it would help to add example in- and output, like @lc. suggests.

John Bowlinger Over a year ago

You are the man @Andomar. '$state' <> state works just fine. Gotta love you SQL gurus!

Collectives™ on Stack Overflow

The correct way to use NOT IN in a mysql query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related