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

Select Statement

Mythotical

Well-known member
#1
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.
 

Mythotical

Well-known member
#2
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.
 

Mythotical

Well-known member
#3
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.
 

Mythotical

Well-known member
#5
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.
 

Mythotical

Well-known member
#8
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.
 

Mythotical

Well-known member
#9
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.
 

Mythotical

Well-known member
#11
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?
 

Chris D

XenForo developer
Staff member
#13
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?
 

Chris D

XenForo developer
Staff member
#15
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

Well-known member
#16
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');
    }
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