Php Mysql NOT IN array only affecting first result in array

Question

So I have a query to create an array of ID's that I do not want to be included in the data that populates my Select List. Both the select list and the array of excluded ID's are being pulled from a mysql database. The problem I am having is that when I echo out $exclude, it appears correctly in a comma separated list (1,2,3). However, when I attempt to add it in my NOT IN statement, it is only excluding the first number. I need it to exclude the entire array. Any ideas?

<?php
$excludeSql = "SELECT member_id FROM appointments WHERE joint_id = '$jointid'";
$excludeData = mysql_query($excludeSql, $link);
while($excluderow = mysql_fetch_array($excludeData)){

    $excludearray[] = $excluderow['member_id'];

                              }
$exclude = implode(',',$excludearray);
echo $exclude;
?>
<select name="attendees[]">             
    <option value="">--Select--</option>

        <?php 
        $memberSql = "SELECT id, firstName, lastName FROM members WHERE id NOT IN('".$exclude."') && compid = '$compid' ORDER BY lastName ASC";
        $memberResult = mysql_query($memberSql, $link);
            while($memberRow = mysql_fetch_assoc($memberResult)){?>
                <option value="<?php echo $memberRow['id'];?>" ><?php echo "".$memberRow['lastName'].", ".$memberRow['firstName'].""; ?></option>
        <?php } ?>              
</select>

Life would be so great if MySQL worked that way. It will only take the first value of the array. You'd need a OR NOT IN(or several for that matter) in your query. — Andrei P., Sep 02 '14 at 20:53

score 5 · Accepted Answer · answered Sep 02 '14 at 20:55

5

You don't need the quote here:

WHERE id NOT IN('".$exclude."')
                ^            ^

Also you could achieve the same result in one query:

SELECT 
  id, 
  firstName, 
  lastName 
FROM members 
WHERE id NOT IN
  (SELECT 
     member_id 
   FROM appointments 
   WHERE joint_id = '$jointid')

answered Sep 02 '14 at 20:55

Ende Neu

15,581
5
57
68

@doublesharp I don't agree with you, the implode is wrapped in quotes ending up with '1,2,3', you can easily try it [here](http://sandbox.onlinephpfunctions.com/code/76af87fa6a4c8c2f59c88e2b05460d204fa595e2). – Ende Neu Sep 02 '14 at 21:01
It worked when I just added the second SELECT statement into the NOT IN clause. Thanks!! – Paige Rose Figueira Sep 02 '14 at 21:06

score 3 · Answer 2 · answered Sep 02 '14 at 20:55

3

You quoted your exclude IDS, so you've turned that comma separated list of numbers into a monolithic string:

WHERE id NOT IN ('1,2,3,4')
WHERE id NOT IN (1,2,3,4)

The above two are two completely DIFFERENT statements. One is a single string, which makes it the equivalent of id <> '1,2,3,4'. The other is a list of 4 numbers for the IN operation.

answered Sep 02 '14 at 20:55

Marc B

356,200
43
426
500

This is not the issue, the explode will account for this. – doublesharp Sep 02 '14 at 20:56
what explode? There's only an implode in there. And what's the matter with the while loop? The indenting is crap, but the loop itself is valid. – Marc B Sep 02 '14 at 20:57
Sorry, I meant implode - it is using `','` for the glue. – doublesharp Sep 02 '14 at 20:58
So? That has nothing to do with the fact taht those comma-separated values then get quoted with `'` when used in the actual query string. – Marc B Sep 02 '14 at 20:59
And you are correct about the while loop - didnt see the closing curly brace hanging out there. – doublesharp Sep 02 '14 at 20:59
Actually, you are just right - I completely misread the code. I'm tired :/. – doublesharp Sep 02 '14 at 21:00

Php Mysql NOT IN array only affecting first result in array

2 Answers2

Linked