$db, SELECT returning numeric indexes

[mc12345678]

#1 - good job!
#2 - i completely disagree with the above comment. it is a bug. period. the array is wrong.

it looks like your PR removes the numeric index to the array. now someone could take issue with that approach, ie, lets correct the numeric index part of the array as opposed to removing it completely. but i would completely disagree with anyone stating this is not a bug.

best.

The PR removes the numeric index array (and continues to prevent processing fields beginning with a number) when using the iterator class, but sustains other existing operation/functionality that existed with using the Move method. Additionally, the numeric index can be used/recreated/referenced as necessary with no further modification of the class code but instead within the code "needing" the numeric value by stepping through the returned results depending on what they wish to obtain. One could track a number value to be used for the Move method, which would return both the numbered key as well as the key=>value pairing if called just as $db->Move($number_val). If however, the Move method were called with a second parameter that did not exactly equal true then currently the numbered key data (and field beginning with a number) would be removed.

Perhaps it is described elsewhere and/or in more detail or perhaps doesn't matter, but where I originally saw this was in github at issue #406. The use of foreach was to replace the following syntax of moving through records:

$var = $db->Execute($sql_query);
while (!$var->EOF) {
  // do something
  $var->MoveNext();
}

Using the while/MoveNext() method (and not calling $var->Move($new_location) internally nor prior to the while loop) would provide a numericLESS keyed set of results (and exclude any fields that begin with a number). Therefore, the concept for this correction was to implement obtaining the same results when replacing the process and based on how the Move($new_location) method and iterator class interrelate.

There is no "direct" way to use foreach and to identify within the call whether to return result position, key=>value, or both. To do this would require some "standard" method that is then perhaps modified just before its use. Such a "feature" might possibly be implemented by having an additional internal "value" that is set to default on __construct and/or rewind() and possibly when valid() goes false (assuming that valid() ever reaches false) or possibly some secondary value that would also get set and if both are switched (reached valid being false) or one of them switched (initiated but never reached a false condition) that unless one of the "presets" is active the default would be used.
ie.

 $db->intNextLoop();
foreach ($result as $key=>$value) {
  // output of $key would be numeric
}

foreach ($result as $key=>$value) {
  // output of $key would be field name.
}

$db->bothLoop();
foreach ($result as $key=>$value) {
  // output of $key would provide first the field name "list" followed by the numeric key value "list" (assuming the same sequencing as seen on my own test as well as in the OP) matching the position within the field list except if the field name had a number the same as one of the result value "positions".
}

Where it can be seen that the data made available within the foreach loop could be modified for that loop only, though... and without testing I'm not immediately sure about addressing if the inner code were to break out of the loop what the result would be on the next non-described loop.

Even with the existing implementation of returning both values in the Move() method, there appears to run a problem at least if a field is a number. For example:
if a table were defined to have say 2 fields where the second field was identified as `0` and a query written to pull the first field first and the second field second, then the result of the original code would have only 3 results instead of 4. (first field, 0, and 1) The value in index 0 would be the same as the value of index 1. See for example the results of the following php array creation run in PHP 7.1 that illustrates what happens when two keys are the "same" (string value of "1" and integer value of 1) and added to an array:

$key_array = array("white"=>'blue', (string)"1" => 'test', (int)1=> 'why');
echo print_r($key_array, true);

results:

Array
(
    [white] => blue
    [1] => why
)

There is no differentiation in assignment of the string key of 1 and the integer value of 1 such that the second occurrence replaces the first... So, then if going to go all out to support all possibilities, it is not possible without a lot of other changes even if the result array could somehow be generated to have two keys associatively equivalent... The next question becomes why is it necessary and can the desired result(s) be obtained by some other method?

This particular "problem" would not occur if the mysql(i)_fetch_array results were just only one or the other (field name [MYSQL_ASSOC] or numeric index [MYSQL_NUM]). Further while I can't entirely roll back time on the php manual (probably have to reference some printed material from back in the day) even though electronic copies of some of the historic information is available and to then be able to see how implementation was across the board and the existence of those two constants beginning in PHP 4 when the mysql_fetch_array method was first introduced, I do not know why a specific method was not chosen in ZC before. It seems that if the thought was that the database may be setup to not support choosing one but defaulted to MYSQL_NUM that the remainder of the code would be at jeopardy because it would not have been possible to obtain field name data such as $db->fields['products_id'] because that field name key would not exist. So could there possibly be an improvement made to the query_class to only pull the associative index? Maybe depending on the effect of load to the database to determine and then return less data...

The real question is why are fields that begin with a number excluded from results? Is it some sort of mysql database compatibility of days gone by? Was it an oversight in attempting to prevent processing fields that were numeric only and how ZC is generally quoteless (meaning backticks are not required across the board to handle/access database data)? Was it just plainly a deterrent to trying to have a field as only a number? Was there some thought of the necessary processing power to accomplish such "filtering"? Was it really more of an attempt to prevent returning the numeric index from the non-specific call to mysql_fetch_array() and just seemed easy at the time? Is any of this paragraph documented as a requirement for field designation to a table? Lots of questions and things that could be changed... If there is some such documented restriction, then it too would need to be updated.

I neglected to say in my quoted response 'as relates to the base ZC core code...' The current base code does not appear to be swayed by the return of the additional non-changing data. This is why I could also agree to being told that it is not a bug for ZC 1.5.5 (or any previous version of ZC as relates to calling the Move method (Move(0)) followed by MoveNext(). That sequence of operation is not mandatory for a query that is followed by data handling and the direct query result not being used again.

[carlwhat]

this is a bug. period. the array is wrong. nothing anyone can tell me will change that opinion. i will remind anyone of 2 key/values in the array @lat9 posted in post #1:

1 => (Array)
countries_name => Åland Islands
1 => Afghanistan

that is wrong.

@lat9 posted this bug, i'm guessing, because she's working on something that exposed this BUG.

now, if the ZC team, feels that there will be no additional releases of v155, and therefore will not entertain additional PRs on that version, ok. i can accept that.

but please, do not tell me or anyone else, this is not a bug. the queryFactory of the db class for mysql is truly base ZC code. and it should return correct results.

[lat9]

As @carlwhat surmised, I'm updating a client's site with some custom code and I thought that it was time to start going down the 'foreach' path on the $db's SELECT output. I'd pulled a couple of values from the orders table and was surprised to find those numeric indexes when processing through, as it caused my update to error-out.

[lat9]

An extended discussion of this issue can be found on the Zen Cart GitHub site: https://github.com/zencart/zencart/pull/1633

Plugin authors, especially, are encouraged to participate!