1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Not a Bug Select Statement with Where Not In Clause

Discussion in 'Resolved Bug Reports' started by Mythotical, Nov 4, 2015.

  1. Mythotical

    Mythotical Well-Known Member

    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:
    PHP:
                    $roster json_decode($clienttrue);
                    
    $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:
    PHP:
    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.
    Admin CP   Real Mythotical.png

    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().
    phpMyAdmin.png
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    You are technically running different queries.

    PHP:
    $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:

    Code:
    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:

    Code:
    IN('name1', 'name2', 'name3', 'name4')
     
  3. Mythotical

    Mythotical Well-Known Member

    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.
     

Share This Page