Not a bug Select Statement with Where Not In Clause

[Cupara]

Using a select statement with a where not in clause returns all data instead of data that does not match the not in clause.

Main cron function:

                $roster = json_decode($client, true);
                $character = $roster['members'];

                $c = array();

                $rosterModel = XenForo_Model::create('xenWow_Model_Roster');

                foreach($character AS $c)
                {
                    $name[] = $c['character']['name'];
                }

                $names = implode(", ", $name);
                $guildRoster = $rosterModel->getAllNonMatch($names);

Model function:

public function getAllNonMatch($names)
    {
        return $this->_getDb()->fetchAll('SELECT char_id, char_name FROM xwow_roster WHERE char_name NOT IN('. $this->_getDb()->quote($names) .') ORDER BY char_name ASC');
    }

The result is in the screenshot which is should return only one name I put in the database for testing purposes. I'm using var_dump() to get the result otherwise if I enable my delete statement it clears that table.


I'm not reporting an issue with an addon but an issue with a feature that should be built into XenForo from the Zend Framework. Using NOT IN doesn't return the correct results, instead it returns all results including those it should exclude.

The second screenshot shows the exact query I'm running and the result I'm expecting in var_dump().

 

[Chris D]

You are technically running different queries.

$names = implode(", ", $name);

I think this line is to blame.

You have an array of names, and you're joining that array into a string using ', '.

You're later running that string through quote, so your actual query is something like:

IN('name1, name2, name3, name4')

You're literally querying for any name that isn't an exact match of the above.

If you remove the implode line above you will be querying for:

IN('name1', 'name2', 'name3', 'name4')

 

[Cupara]

I have tried using that same method but it returns an error as well so in turn I had to use $names = implode(", '", $name); noticed the single quote after the comma in double quotes.