If the value inside NOT IN is empty mysql is throwing an error

Question

If m.message_type_id IN () is empty it is giving me an error which says

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 ') ) UNION ( ' at line 16

        SELECT m.id,m.log,message,images,videos,uo.user_id as to_id,owner_id,
        IF(m.owner_id = 45,'sent','received') as type
        FROM messages as m 
        LEFT JOIN events as e ON m.message_type_id = e.id
        LEFT JOIN user_organization as uo ON uo.id = e.org_id
        WHERE m.message_type_id IN ()
        AND m.type = 'event_invite_request_msg'

and if WHERE m.message_type_id IN (61) it gives me the correct response.

What is the solution?

show the code where you are passing id within where condition — Narendrasingh Sisodia, Mar 05 '15 at 05:58
$friendsArray = array(); foreach($friends as $friend){ $friendsArray[] = $friend->friend_id; } $friendsArray = implode(',',$friendsArray); — CoderMax, Mar 05 '15 at 06:01

score 1 · Accepted Answer · answered Mar 05 '15 at 05:58

1

IN Clause expects you to send at least one value, why would you expect it should work even with an empty parameter?

expr NOT IN (value,...)

Syntax

On a broader level this is the case with almost all comparisons, why would you expect a comparison to not result in an error if there is an invalid value to compare against

Would this work for you?

SELECT myField FROM myTable WHERE MyField2 =

Nope it won't

answered Mar 05 '15 at 05:58

Hanky Panky

46,730
8
72
95

is there any other way I can solve this error without having to change all the queries with a condition that appends "NOT IN" only if there are values in my app? because I have used this same query in more than 15 models. – CoderMax Mar 05 '15 at 06:02
There can be, you can check if your array is empty then initialize your IN data with all the message types that you have since thats the intention in that case but that is all patch work and i wouldnt suggest you to do it. Its better to take some time now and fix the query to incorporate for that instead of patching things around. – Hanky Panky Mar 05 '15 at 06:04
I think I solved this issue by appending the array with a default 0 as value.It is not giving me any errors but is this the proper way to do it? – CoderMax Mar 05 '15 at 06:05
If you want no results to show when no type is selected then yes that is fine, If you want all types to show when no type is given then this is the wrong way to do it as it will only give you rows for which the value for that field is 0 – Hanky Panky Mar 05 '15 at 06:12

score 0 · Answer 2 · answered Mar 05 '15 at 06:07

You'll have to use condition for that

if(count($friendsArray[])){
        $qry = "SELECT m.id,m.log,message,images,videos,uo.user_id as to_id,owner_id,
        IF(m.owner_id = 45,'sent','received') as type
        FROM messages as m 
        LEFT JOIN events as e ON m.message_type_id = e.id
        LEFT JOIN user_organization as uo ON uo.id = e.org_id
        WHERE m.message_type_id IN ($ids)
        AND m.type = 'event_invite_request_msg'";
}else{
        $qry = "SELECT m.id,m.log,message,images,videos,uo.user_id as to_id,owner_id,
        IF(m.owner_id = 45,'sent','received') as type
        FROM messages as m 
        LEFT JOIN events as e ON m.message_type_id = e.id
        LEFT JOIN user_organization as uo ON uo.id = e.org_id
        WHERE m.type = 'event_invite_request_msg'";
}

I think I solved this issue by appending the array with a default 0 as value. `http://pastebin.com/DuEKEBAc It is not giving me any errors but is this the proper way to do it? — CoderMax, Mar 05 '15 at 06:08

If the value inside NOT IN is empty mysql is throwing an error

2 Answers2