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

Select Statement

Discussion in 'XenForo Development Discussions' started by Mythotical, Oct 26, 2015.

  1. Mythotical

    Mythotical Well-Known Member

    I'm trying to select data from my table that does not match a JSON returned array.

    In my table I have various information but the important information is character name since they are unique. When I run the JSON array, I need the select to grab only those entries that don't match character names returned in my JSON array then delete them maintaining only the names that match JSON returned character names.

    PHP:
        $dw XenForo_DataWriter::create('xenWow_DataWriter_Roster');
        
    $guildRoster $db->fetchRow("SELECT * FROM xwow_roster");
                    foreach(
    $character AS $c)
                    {
                       if(
    $guildRoster['char_name'] !== $c['character']['name'])
                        {
                            continue;
                        }
                       
    $dw->setExistingData($guildRoster);
                       
    $dw->delete();
                    }
    $character is defined as the returned JSON array.

    So I would need to have it find all names that don't match $character array and delete them.

    Problem is that every query I have ran either deletes everything that matches or doesn't delete anything.

    DataWriter works fine as it does delete but deletes wrong data. LOL

    EDIT: I posted in the General PHP and MySQL Discussion just in case others have another method without using a DataWriter.
     
  2. Mythotical

    Mythotical Well-Known Member

    So I have figured it out to a point. I needed to grab the names and put them into a string then do a database call that would only list those not in the string. Problem is the return is still every name when it should be returning one name for the test.
     
  3. Mythotical

    Mythotical Well-Known Member

    Ok so here is the new problem.

    Using
    PHP:
    $db->fetchAll('SELECT char_id, char_name FROM xwow_roster WHERE char_name NOT IN(?) ORDER BY char_name ASC'$names);
    Returns
    Code:
    array(11) { [0]=> array(2) { ["char_id"]=> int(23) ["char_name"]=> string(9) "Angelicca" } [1]=> array(2) { ["char_id"]=> int(19) ["char_name"]=> string(7) "Anyaloi" } [2]=> array(2) { ["char_id"]=> int(16) ["char_name"]=> string(9) "Chameltow" } [3]=> array(2) { ["char_id"]=> int(17) ["char_name"]=> string(7) "Felzzak" } [4]=> array(2) { ["char_id"]=> int(15) ["char_name"]=> string(8) "Gragoria" } [5]=> array(2) { ["char_id"]=> int(14) ["char_name"]=> string(8) "Gwaeddan" } [6]=> array(2) { ["char_id"]=> int(22) ["char_name"]=> string(7) "Kâhlia" } [7]=> array(2) { ["char_id"]=> int(24) ["char_name"]=> string(8) "Miranysa" } [8]=> array(2) { ["char_id"]=> int(21) ["char_name"]=> string(7) "Raizher" } [9]=> array(2) { ["char_id"]=> int(18) ["char_name"]=> string(6) "Tallon" } [10]=> array(2) { ["char_id"]=> int(20) ["char_name"]=> string(5) "Xarel" } }
    It should only return one name that I inserted manually for testing.

    Now if I try using
    PHP:
    $db->query('SELECT char_id, char_name FROM xwow_roster WHERE char_name NOT IN(?) ORDER BY char_name ASC'$names);
    It returns
    Code:
    object(Zend_Db_Statement_Mysqli)#92 (12) { ["_keys":protected]=> array(2) { [0]=> string(7) "char_id" [1]=> string(9) "char_name" } ["_values":protected]=> array(2) { [0]=> &int(0) [1]=> &NULL } ["_meta":protected]=> object(mysqli_result)#97 (5) { ["current_field"]=> int(0) ["field_count"]=> int(2) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) } ["_stmt":protected]=> object(mysqli_stmt)#85 (9) { ["affected_rows"]=> int(11) ["insert_id"]=> int(0) ["num_rows"]=> int(11) ["param_count"]=> int(1) ["field_count"]=> int(2) ["errno"]=> int(0) ["error"]=> string(0) "" ["sqlstate"]=> string(5) "00000" ["id"]=> int(7) } ["_adapter":protected]=> object(Zend_Db_Adapter_Mysqli)#6 (12) { ["_numericDataTypes":protected]=> array(16) { [0]=> int(0) [1]=> int(1) [2]=> int(2) ["INT"]=> int(0) ["INTEGER"]=> int(0) ["MEDIUMINT"]=> int(0) ["SMALLINT"]=> int(0) ["TINYINT"]=> int(0) ["BIGINT"]=> int(1) ["SERIAL"]=> int(1) ["DEC"]=> int(2) ["DECIMAL"]=> int(2) ["DOUBLE"]=> int(2) ["DOUBLE PRECISION"]=> int(2) ["FIXED"]=> int(2) ["FLOAT"]=> int(2) } ["_stmt":protected]=> *RECURSION* ["_defaultStmtClass":protected]=> string(24) "Zend_Db_Statement_Mysqli" ["_config":protected]=> array(9) { ["host"]=> string(9) "localhost" ["port"]=> string(4) "3306" ["username"]=> string(5) "Steve" ["password"]=> string(12) "Mythot1c@l82" ["dbname"]=> string(13) "admin_Xenforo" ["charset"]=> string(4) "utf8" ["persistent"]=> bool(false) ["options"]=> array(3) { ["caseFolding"]=> int(0) ["autoQuoteIdentifiers"]=> bool(true) ["fetchMode"]=> int(2) } ["driver_options"]=> array(0) { } } ["_fetchMode":protected]=> int(2) ["_profiler":protected]=> object(Zend_Db_Profiler)#21 (4) { ["_queryProfiles":protected]=> array(6) { [0]=> object(Zend_Db_Profiler_Query)#26 (5) { ["_query":protected]=> string(105) " SELECT session_data FROM xf_session_admin WHERE session_id = ? AND expiry_date >= ? " ["_queryType":protected]=> int(32) ["_startedMicrotime":protected]=> float(1446460983.4227) ["_endedMicrotime":protected]=> float(1446460983.4232) ["_boundParams":protected]=> array(2) { [1]=> string(32) "d837c74753d42711700f542cd75ff21b" [2]=> int(1446460983) } } [1]=> object(Zend_Db_Profiler_Query)#27 (5) { ["_query":protected]=> string(56) "SELECT admin_language_id FROM xf_admin WHERE user_id = ?" ["_queryType":protected]=> int(32) ["_startedMicrotime":protected]=> float(1446460983.4236) ["_endedMicrotime":protected]=> float(1446460983.4239) ["_boundParams":protected]=> array(1) { [1]=> int(1) } } [2]=> object(Zend_Db_Profiler_Query)#30 (5) { ["_query":protected]=> string(648) " SELECT user.* , user_profile.*, user_option.*, user_privacy.*, permission_combination.cache_value AS global_permission_cache FROM xf_user AS user LEFT JOIN xf_user_profile AS user_profile ON (user_profile.user_id = user.user_id) LEFT JOIN xf_user_option AS user_option ON (user_option.user_id = user.user_id) LEFT JOIN xf_user_privacy AS user_privacy ON (user_privacy.user_id = user.user_id) LEFT JOIN xf_permission_combination AS permission_combination ON (permission_combination.permission_combination_id = user.permission_combination_id) WHERE user.user_id = ? " ["_queryType":protected]=> int(32) ["_startedMicrotime":protected]=> float(1446460983.4305) ["_endedMicrotime":protected]=> float(1446460983.432) ["_boundParams":protected]=> array(1) { [1]=> int(1) } } [3]=> object(Zend_Db_Profiler_Query)#96 (5) { ["_query":protected]=> string(59) " SELECT * FROM xf_cron_entry WHERE entry_id = ? " ["_queryType":protected]=> int(32) ["_startedMicrotime":protected]=> float(1446460983.4387) ["_endedMicrotime":protected]=> float(1446460983.4391) ["_boundParams":protected]=> array(1) { [1]=> string(20) "xenWow_Daily_CleanUp" } } [4]=> object(Zend_Db_Profiler_Query)#93 (5) { ["_query":protected]=> string(54) " SELECT * FROM xf_addon WHERE addon_id = ? " ["_queryType":protected]=> int(32) ["_startedMicrotime":protected]=> float(1446460983.4415) ["_endedMicrotime":protected]=> float(1446460983.442) ["_boundParams":protected]=> array(1) { [1]=> string(6) "xenWow" } } [5]=> object(Zend_Db_Profiler_Query)#86 (5) { ["_query":protected]=> string(91) "SELECT char_id, char_name FROM xwow_roster WHERE char_name NOT IN(?) ORDER BY char_name ASC" ["_queryType":protected]=> int(32) ["_startedMicrotime":protected]=> float(1446460983.7012) ["_endedMicrotime":protected]=> float(1446460983.7018) ["_boundParams":protected]=> array(1) { [1]=> string(109) "Gwaeddan', 'Gragoria', 'Chameltow', 'Felzzak', 'Tallon', 'Anyaloi', 'Xarel', 'Raizher', 'Kâhlia', 'Angelicca" } } } ["_enabled":protected]=> bool(true) ["_filterElapsedSecs":protected]=> NULL ["_filterTypes":protected]=> NULL } ["_defaultProfilerClass":protected]=> string(16) "Zend_Db_Profiler" ["_connection":protected]=> object(mysqli)#11 (17) { ["affected_rows"]=> int(11) ["client_info"]=> string(6) "5.1.73" ["client_version"]=> int(50173) ["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(0) ["error"]=> string(0) "" ["field_count"]=> int(2) ["host_info"]=> string(25) "Localhost via UNIX socket" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(6) "5.1.73" ["server_version"]=> int(50173) ["sqlstate"]=> string(5) "00000" ["protocol_version"]=> int(10) ["thread_id"]=> int(91444) ["warning_count"]=> int(0) } ["_caseFolding":protected]=> int(0) ["_autoQuoteIdentifiers":protected]=> bool(true) ["_allowSerialization":protected]=> bool(true) ["_autoReconnectOnUnserialize":protected]=> bool(false) } ["_fetchMode":protected]=> int(2) ["_attribute":protected]=> array(0) { } ["_bindColumn":protected]=> array(0) { } ["_bindParam":protected]=> array(0) { } ["_sqlSplit":protected]=> array(0) { } ["_sqlParam":protected]=> array(0) { } ["_queryId":protected]=> int(5) }
    So from reasoning, the second method using $db->query() is wrong but I don't understand why the first returns everything instead of just that one name that doesn't match the array.
     
  4. Mouth

    Mouth Well-Known Member

    Both will return every record in table wow_roster where char_name isn't in your array. Without showing some example db table records and what's in your $names array before the select is run, it's difficult to understand what you are expecting.
     
  5. Mythotical

    Mythotical Well-Known Member

    Example here using phpMyAdmin.

    http://awesomescreenshot.com/0d15de54a9

    The statement I used is the same I'm running, it grabs the names from a JSON returned array, I managed to get it down to a comma separated array in single quotes as it shows in the statement from the screenshot. By using NOT IN it should only return Miranysa row or any row that is not within that array.

    Now what I showed you in the first example of my post you quoted is the result that should not happen, it returns every row in the table instead of just those that are not in the array.
     
  6. Mythotical

    Mythotical Well-Known Member

    Still looking for a solution. Any help much appreciated.
     
  7. Mouth

    Mouth Well-Known Member

    Try explaining differently and providing examples and further data .... your posts so far are not revealing enough to be able to help.
     
  8. Mythotical

    Mythotical Well-Known Member

    I've given examples and data along with a screenshot. Not sure what else I can provide to clarify. The problem I'm having is simple, the NOT IN array is not returning only names not in the array, instead it returns all the names including names from the array it shouldn't return.
     
  9. Mythotical

    Mythotical Well-Known Member

    From above code, I do a var_dump() to get results to make sure it returns the right one. Doing it in phpMyAdmin works fine but when I run my first query using fetchAll method, I get this returned:
    Code:
    array(11) { [0]=> array(2) { ["char_id"]=> int(23) ["char_name"]=> string(9) "Angelicca" } [1]=> array(2) { ["char_id"]=> int(19) ["char_name"]=> string(7) "Anyaloi" } [2]=> array(2) { ["char_id"]=> int(16) ["char_name"]=> string(9) "Chameltow" } [3]=> array(2) { ["char_id"]=> int(17) ["char_name"]=> string(7) "Felzzak" } [4]=> array(2) { ["char_id"]=> int(15) ["char_name"]=> string(8) "Gragoria" } [5]=> array(2) { ["char_id"]=> int(14) ["char_name"]=> string(8) "Gwaeddan" } [6]=> array(2) { ["char_id"]=> int(22) ["char_name"]=> string(7) "Kâhlia" } [7]=> array(2) { ["char_id"]=> int(24) ["char_name"]=> string(8) "Miranysa" } [8]=> array(2) { ["char_id"]=> int(21) ["char_name"]=> string(7) "Raizher" } [9]=> array(2) { ["char_id"]=> int(18) ["char_name"]=> string(6) "Tallon" } [10]=> array(2) { ["char_id"]=> int(20) ["char_name"]=> string(5) "Xarel" } }
    That is the main issue, it should only return Miranysa.
     
  10. Mouth

    Mouth Well-Known Member

    Then create a bug report.
     
  11. Mythotical

    Mythotical Well-Known Member

    Ok you said my statements should work correctly in XenForo, since they seem to be failing, then it is possible to be a bug within XenForo?
     
  12. Mythotical

    Mythotical Well-Known Member

    So I reported it as a bug and I was told it is not a bug therefore I'm back to square one.
     
  13. Chris D

    Chris D XenForo Developer Staff Member

    Your most recent error talks about prepared statements.

    In the code you gave in the previous thread, you aren't even using prepared statements. That leads me to believe the code has since changed since you posted the code in the bug report thread.

    What is the current code which is preparing the names, and running the query?
     
  14. Mythotical

    Mythotical Well-Known Member

    EDIT: Don't like leaving my entire code out there when the product is not completed yet.
     
    Last edited: Nov 4, 2015
  15. Chris D

    Chris D XenForo Developer Staff Member

    I did tell you to remove the implode, this is correct. But then you completely changed the meaning of the query.

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

    Mythotical Well-Known Member

    Do you realize how much I'm smacking myself right now? I was getting an error from my server regarding the use of quote() so I thought that may be the issue now I see it was trying to tell me that I formed the entire thing wrong using implode.

    Thanks a bunch for clearing this up, now I can move on with my next issue I'm working on which happens to be lack of sleep. LOL
     
    Mouth and Chris D like this.

Share This Page