Modifying the keys in each row of a query result set

Question

I am querying a SQL database and returning some values. What I have is Field1 and Field2 which come out with a list of dates (There may be up to 200 values/dates eventually). I then reformat the current dates into the desired format.

// While we have valid rows
while( $row = sqlsrv_fetch_array($rs, SQLSRV_FETCH_ASSOC)) {

    //build an array of all fields and their values
    $outputstring[] = $row; 
}

// for each record, IF the fields exist, reformat the date  
foreach($outputstring as &$each) {
    if($each['Field1']) $each['Field1'] = $each['Field1']->format('m-d H:i');
    if($each['Field2']) $each['Field2'] = $each['Field2']->format('m-d H:i');
}       

// Output as JSON   
echo JSE($outputstring);

What I now want to do is change the names of Field1 and Field2 without modifying the SQL query and before the array is parsed as JSON.

I would normally just modify the SQL with;

Select Field1 as startdate, Field2 as enddate

But this isn't an option here as the value to be used must come from a translation function.

In the final output, Field1 needs to be displayed as the output of

lang('Field1');

This function delivers the translation of the field names in one of 33 languages depending on what the user selected prior to the output being generated.

I could use something like:

SELECT Field1 as ". lang('Field1') .", Field2 as ". lang('Field2') ."

but would prefer a PHP option that applies the language value to all keys in the array. As mentioned, there may be hundreds of fields in the final version and I don't really want to hardcode each possible value if it can be avoided.

any ideas?

https://stackoverflow.com/questions/9605143/how-to-rename-array-keys-in-php — Danny, Aug 10 '18 at 21:01
Thanks Danny however that thread only appears to show how to modify the keys one a one by one basis. Ideally, I need this to be dynamic. — Sammy, Aug 11 '18 at 11:51

Ron van der Heijden · Accepted Answer · 2018-08-10T21:11:29.510

0

If I understand it correctly, can you use this?

$formatted = [];
while( $row = sqlsrv_fetch_array($rs, SQLSRV_FETCH_ASSOC)) {
    foreach($row as $key => $value) {
        $formatted[lang($key)] = $value->format('m-d H:i');
    }
}
// Output as JSON   
echo JSE($formatted);

edited Aug 10 '18 at 21:11

answered Aug 10 '18 at 21:03

Ron van der Heijden

14,803
7
58
82

Thanks Ron - This works for the time being. The problem will come when the next set of fields are added as they won't all be date fields – Sammy Aug 11 '18 at 12:11
Actually, This method adds another layer to the final array which messes up the JSON formatting. Still struggling to find a way to dynamically change the key name based on the language file whilst adding a custom dateformat to *some* fields – Sammy Aug 11 '18 at 12:45
@Sammy Could you add the desired json output in you question? – Ron van der Heijden Aug 11 '18 at 13:58

score 0 · Answer 2 · answered Sep 11 '18 at 00:25

Thanks again Ron for pointing me in the right direction. I went this route;

$outputarray = array();

while( $row = sqlsrv_fetch_array($rs, SQLSRV_FETCH_ASSOC)) {
$outputarray[] = $row; 
}

$outputarray = replaceKey($outputarray,$lang);  
echo JSE($outputarray);

ReplaceKey is a function that looks at the output array and also my language file which is another array of input values with a matching output value;

$lang = array(
$Field1 => 'Time',
$Field2 => 'Hours',
);

With ReplaceKey being;

function replaceKey($o,$m=array())
{
    $t = JSE($o);
    foreach ($m AS $k=>$v)
    {
    $t = str_ireplace('"'.$k.'":','"'.$v.'":', $t);
    }
    $a = true;
    if (is_object($o))
    {
        $a = false;
    }
    return JSD($t, $a);
}

The array is looped over and if Field1 appears in it, it's replaced with the word 'Time' before the output array is rebuilt with the new keys.

Again, Thank you so much for your help!

Modifying the keys in each row of a query result set

2 Answers2