Select Statement

Cupara · Oct 26, 2015

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.

Cupara · Nov 2, 2015

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.

Cupara · Nov 2, 2015

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.

Mouth · Nov 2, 2015

Mythotical said:
Using

Mythotical said:
Now if I try using

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.

Cupara · Nov 2, 2015

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.

Cupara · Nov 3, 2015

Still looking for a solution. Any help much appreciated.

Mouth · Nov 4, 2015

Mythotical said:
Still looking for a solution. Any help much appreciated.

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

Cupara · Nov 4, 2015

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.

Cupara · Nov 4, 2015

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.

Mouth · Nov 4, 2015

Mythotical said:
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.

Then create a bug report.

Cupara · Nov 4, 2015

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?

Cupara · Nov 4, 2015

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

Chris D · Nov 4, 2015

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?

Cupara · Nov 4, 2015

EDIT: Don't like leaving my entire code out there when the product is not completed yet.

Chris D · Nov 4, 2015

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');
    }

Cupara · Nov 4, 2015

Chris D said:
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

Select Statement

Cupara

Well-known member

Cupara

Well-known member

Cupara

Well-known member

Mouth

Well-known member

Cupara

Well-known member

Cupara

Well-known member

Mouth

Well-known member

Cupara

Well-known member

Cupara

Well-known member

Mouth

Well-known member

Cupara

Well-known member

Cupara

Well-known member

Chris D

XenForo developer

Cupara

Well-known member

Chris D

XenForo developer

Cupara

Well-known member

Similar threads

We value your privacy