Replace column values in result set using a lookup/dictionary array

Question

You can find the entire code below.

I have 2 arrays. The first array looks like this:

// Replace ID_CHAT_ with readable value
$replacements = array(
    'ID_CHAT_ATTACK/DEFEND' => 'Attack/Defend!',
    'ID_CHAT_REQUEST_MEDIC' => 'Request Medic!',
    'ID_CHAT_REQUEST_AMMO' => 'Request Ammo!',
    'ID_CHAT_REQUEST_RIDE' => 'Request Ride!',
    'ID_CHAT_NEGATIVE' => 'Negative!',
    'ID_CHAT_SORRY' => 'Sorry!',
    'ID_CHAT_GOGOGO' => 'Go Go Go!',
    'ID_CHAT_AFFIRMATIVE' => 'Affirmative!',
    'ID_CHAT_REQUEST_ORDER' => 'Request Order!',
    'ID_CHAT_THANKS' => 'Thanks!',
);

The second array is this: (from a mysqli result set)

[31] => Array
    (
        [ID] => 2179
        [logDate] => 2016-05-12 22:10:38
        [ServerID] => 2
        [logSubset] => Team
        [logSoldierName] => OPTIMUS-GOLD
        [logMessage] => ID_CHAT_REQUEST_AMMO
        [logPlayerID] => 1071
    )

[32] => Array
    (
        [ID] => 2178
        [logDate] => 2016-05-12 22:10:34
        [ServerID] => 2
        [logSubset] => Team
        [logSoldierName] => CaptaineGamingFR
        [logMessage] => ID_CHAT_REQUEST_RIDE
        [logPlayerID] => 1531
    )

[33] => Array
    (
        [ID] => 2177
        [logDate] => 2016-05-12 22:10:27
        [ServerID] => 2
        [logSubset] => Team
        [logSoldierName] => CaptaineGamingFR
        [logMessage] => ID_CHAT_GOGOGO
        [logPlayerID] => 1531
    )

[34] => Array
    (
        [ID] => 2176
        [logDate] => 2016-05-12 22:10:11
        [ServerID] => 2
        [logSubset] => Global
        [logSoldierName] => CaptaineGamingFR
        [logMessage] => cool des francais
        [logPlayerID] => 1531
    )

[35] => Array
    (
        [ID] => 2175
        [logDate] => 2016-05-12 22:10:08
        [ServerID] => 2
        [logSubset] => Global
        [logSoldierName] => Minotaures
        [logMessage] => et sa s affiche sur tt les serveur
        [logPlayerID] => 1337
    )

I want to replace the logMessage values in the second array with the corresponding $replacements value.

How it should look:

[logMessage] => **ID_CHAT_REQUEST_AMMO**

Must be

[logMessage] => Request Ammo!

Current code:

public function serverChatlog( $serverID = null ) {
        
    // Replace ID_CHAT_ with readable value
    $replacements = array(
        'ID_CHAT_ATTACK/DEFEND' => 'Attack/Defend!',
        'ID_CHAT_REQUEST_MEDIC' => 'Request Medic!',
        'ID_CHAT_REQUEST_AMMO' => 'Request Ammo!',
        'ID_CHAT_REQUEST_RIDE' => 'Request Ride!',
        'ID_CHAT_NEGATIVE' => 'Negative!',
        'ID_CHAT_SORRY' => 'Sorry!',
        'ID_CHAT_GOGOGO' => 'Go Go Go!',
        'ID_CHAT_AFFIRMATIVE' => 'Affirmative!',
        'ID_CHAT_REQUEST_ORDER' => 'Request Order!',
        'ID_CHAT_THANKS' => 'Thanks!',
    );
        
    // Create an array.
    $output = array();
        
    // Query the database
    $query  = $this->connectDB()->query( 'SELECT * FROM `tbl_chatlog` ORDER BY `ID` DESC' );
    $result = $query->num_rows;
    $array  = $query->fetch_all(MYSQLI_ASSOC);
        
    // Return the results in JSON format
    if( $result > 0 ) {
        $output = json_encode( $array, JSON_PRETTY_PRINT );
        return json_decode( $output, true );
    } else {
        return false;
    }
}

Have you tried anything already? If you need a clue, look at the [array_search](http://php.net/manual/de/function.array-search.php) method — Emanuel Oster, May 17 '16 at 13:35
Yes, i tryed alot of things. Also the awnser from Allen Butler. However, it only replaces 1 value instead of all values. — Red, May 17 '16 at 14:58

Maytyn · Answer 1 · 2016-05-17T13:43:41.957

1

Maybe this helps. It iterrates over log array, checks value of logMessage element and replaces it.

<?php

    foreach($logs as $log){
        $replacementKey =   str_replace('*', '', $log['logMessage']);
        if(array_key_exists($replacementKey, $replacements)){
            $log['logMessage'] = $replacements[$replacementKey];
        }
    }

?>

edited May 17 '16 at 13:43

answered May 17 '16 at 13:37

Maytyn

122
1
10

Allen Butler · Accepted Answer · 2016-05-17T15:24:38.580

Maybe something like the following. Grabs the key values of $replacements and if the value of a key in $array is found, it will replace the the value with the $replacement value.

$array = [
        "ID" => "2179",
        "logDate" => "2016-05-12 22:10:38",
        "ServerID" => "2",
        "logSubset" => "Team",
        "logSoldierName" => "OPTIMUS-GOLD",
        "logMessage" => "ID_CHAT_REQUEST_AMMO",
        "logPlayerID" => "1071"

];
 $replacements = array(
    'ID_CHAT_ATTACK/DEFEND' => 'Attack/Defend!',
    'ID_CHAT_REQUEST_MEDIC' => 'Request Medic!',
    'ID_CHAT_REQUEST_AMMO' => 'Request Ammo!',
    'ID_CHAT_REQUEST_RIDE' => 'Request Ride!',
    'ID_CHAT_NEGATIVE' => 'Negative!',
    'ID_CHAT_SORRY' => 'Sorry!',
    'ID_CHAT_GOGOGO' => 'Go Go Go!',
    'ID_CHAT_AFFIRMATIVE' => 'Affirmative!',
    'ID_CHAT_REQUEST_ORDER' => 'Request Order!',
    'ID_CHAT_THANKS' => 'Thanks!',
);
$keys = array_keys($replacements);
foreach($array as $k => $v) {
    if(in_array($v, $keys)) {
        $array[$k] = $replacements[$v];
    }
}
print_r($array);
//PRINTS: Array ( [ID] => 2179 [logDate] => 2016-05-12 22:10:38 [ServerID] => 2 [logSubset] => Team [logSoldierName] => OPTIMUS-GOLD [logMessage] => Request Ammo! [logPlayerID] => 1071 )

EDIT

So let me apply the same logic to the rest of your array. Suppose we have the following array (I pulled this from your question above):

$array = [
"31" => 
    [
        "ID" => "2179",
        "logDate" => "2016-05-12 22:10:38",
        "ServerID" => "2",
        "logSubset" => "Team",
        "logSoldierName" => "OPTIMUS-GOLD",
        "logMessage" => "ID_CHAT_REQUEST_AMMO",
        "logPlayerID" => "1071"
    ],

"32" => 
    [
        "ID" => "2178",
        "logDate" => "2016-05-12 22:10:34",
        "ServerID" => "2",
        "logSubset" => "Team",
        "logSoldierName" => "CaptaineGamingFR",
        "logMessage" => "ID_CHAT_REQUEST_RIDE",
        "logPlayerID" => "1531"
    ],

"33" =>
    [
        "ID" => "2177",
        "logDate" => "2016-05-12 22:10:27",
        "ServerID" => "2",
        "logSubset" => "Team",
        "logSoldierName" => "CaptaineGamingFR",
        "logMessage" => "ID_CHAT_GOGOGO",
        "logPlayerID"  => "1531"
    ],
"34" => [
        "ID" => "2179",
        "logDate" => "2016-05-12 22:10:38",
        "ServerID" => "2",
        "logSubset" => "Team",
        "logSoldierName" => "OPTIMUS-GOLD",
        "logMessage" => "ID_CHAT_REQUEST_AMMO",
        "logPlayerID" => "1071"

   ]
];

Now I can iterate over this array with the following:

$keys = array_keys($replacements); // Grab The Array Keys
foreach($array as $subarray => $a) { // For Each loop to pull out the subarrays
    foreach($a as $k => $v) { // another to loop through those subarrays
        if(in_array($v, $keys)) { // if the value is found as a key in the replacements array do the following
            $array[$subarray][$k] = $replacements[$v]; // replace the current subarray index value with the value in the $replacements array
        }
    }
}

This will output the following:

Array
(
    [31] => Array
        (
            [ID] => 2179
            [logDate] => 2016-05-12 22:10:38
            [ServerID] => 2
            [logSubset] => Team
            [logSoldierName] => OPTIMUS-GOLD
            [logMessage] => Request Ammo!
            [logPlayerID] => 1071
        )

    [32] => Array
        (
            [ID] => 2178
            [logDate] => 2016-05-12 22:10:34
            [ServerID] => 2
            [logSubset] => Team
            [logSoldierName] => CaptaineGamingFR
            [logMessage] => Request Ride!
            [logPlayerID] => 1531
        )

    [33] => Array
        (
            [ID] => 2177
            [logDate] => 2016-05-12 22:10:27
            [ServerID] => 2
            [logSubset] => Team
            [logSoldierName] => CaptaineGamingFR
            [logMessage] => Go Go Go!
            [logPlayerID] => 1531
        )

    [34] => Array
        (
            [ID] => 2179
            [logDate] => 2016-05-12 22:10:38
            [ServerID] => 2
            [logSubset] => Team
            [logSoldierName] => OPTIMUS-GOLD
            [logMessage] => Request Ammo!
            [logPlayerID] => 1071
        )

)

Yes this does work, However, it only works with one value. So for example: only Attack/Defend! will be replaced. The rest will stay the same. Thanks anyway for the awnser. I hope u can help me futher now :) — Red, May 17 '16 at 14:58
Shouldn't be the case. I did notice that you use `**$command**` to delineate that a command should be replaced with its respective value, and i didn't write code to parse that. But the above code should work. Now what you will need to do is iterate through your primary array then iterate using the code I provided. I'll make an edit so you can see this in action. — Allen Butler, May 17 '16 at 15:04
I appreciate that. Another thing for you. Its inside a class. I dont print the result in the class, i do it ouside the class in another PHP file. If you want, i can send you the whole class — Red, May 17 '16 at 15:08
Oh, here can you see the current result i get from you awnser. http://site1.h2505194.stratoserver.net/5/test.php — Red, May 17 '16 at 15:09
Yeah exactly, you can decide where you want to print it, I just put that there for you to see the output — Allen Butler, May 17 '16 at 15:10
No worries! Go ahead and take a look at my edit and see if that helps. — Allen Butler, May 17 '16 at 15:25
I really appriciate your awnser. It does now exacly what i wanted. I also can learn from this awnser!. Thanks for the help! — Red, May 17 '16 at 15:29
No problem! Glad I could help! I'll leave parsing those `**` up to you ;) — Allen Butler, May 17 '16 at 15:31

mickmackusa · Answer 3 · 2023-04-12T08:25:36.667

If you are hardcoding $replacements in your function anyhow, then you might as well build it directly into your SQL. Use a CASE statement.

Let fetch_all() return a json-encoded array (even if it is empty). It is a good idea to maintain consistent data types in your return values when possible.

public function serverChatlog(): string
{
    $sql = "SELECT ID,
                   logDate,
                   ServerID,
                   logSubset,
                   logSoldierName,
                   CASE logMessage
                       WHEN 'ID_CHAT_ATTACK/DEFEND' THEN 'Attack/Defend!'
                       WHEN 'ID_CHAT_REQUEST_MEDIC' THEN 'Request Medic!'
                       WHEN 'ID_CHAT_REQUEST_AMMO'  THEN 'Request Ammo!'
                       WHEN 'ID_CHAT_REQUEST_RIDE'  THEN 'Request Ride!'
                       WHEN 'ID_CHAT_NEGATIVE'      THEN 'Negative!'
                       WHEN 'ID_CHAT_SORRY'         THEN 'Sorry!'
                       WHEN 'ID_CHAT_GOGOGO'        THEN 'Go Go Go!'
                       WHEN 'ID_CHAT_AFFIRMATIVE'   THEN 'Affirmative!'
                       WHEN 'ID_CHAT_REQUEST_ORDER' THEN 'Request Order!'
                       WHEN 'ID_CHAT_THANKS'        THEN 'Thanks!'
                       ELSE logMessage
                   END logMessage,
                   logPlayerID
            FROM `tbl_chatlog`
            ORDER BY `ID` DESC";
    return json_encode($this->connectDB()->query($sql)->fetch_all(MYSQLI_ASSOC), true);
}

Look at how clean, professional, direct, and readable your method becomes.

To enjoy more flexibility, create a new database table to contain the translations and merely JOIN that data on the logMessage column. This way you can maintain/extend your translation data without having to touch your codebase -- WIN!

Ranjit Shinde · Answer 4 · 2021-02-26T09:29:20.463

-2

I hope this help. Search array key , if key exist then just replace with it.

foreach ($secondArray as &$value) {
    $key = array_search ( trim($value['logMessage'], '*') , $value );
    if (!empty($key)) {
        $value['logMessage'] = $replacements[$key]
    }
}

edited Feb 26 '21 at 09:29

answered May 17 '16 at 13:43

Ranjit Shinde

1,121
1
7
22

1

Not only is `isset($key) && !empty($key)` unnecessary when `$key` is guaranteed to be declared, `isset($key) && !empty($key)` is an antipattern that should not exist in anyone's code for any reason. https://stackoverflow.com/a/4559976/2943403 – mickmackusa Feb 26 '21 at 07:29
`empty()` doesn't know the difference between zero and false. There is no need to check if the variable is declared, so `empty()` is a poor choice. – mickmackusa Feb 26 '21 at 10:57

Replace column values in result set using a lookup/dictionary array

4 Answers4

EDIT