Replace mysql coloumn values when adding them to an php array

Question

I have a table called "Current state", it has three columns known as:

id  name    state
1   FXD     1
2   GFX     3
3   ATOM    2
4   FARB    3
5   REX     1
6   FRX     2

In the following code I get it into array and print it:

$exc = $conn->prepare("SELECT name,state from current_state");
        $exc->execute();
            while($finalResult = $exc->fetch(PDO::FETCH_ASSOC))
             {
                        $tables[] = $finalResult;
             }
var_dump($tables);

But the problem is though numbers represent the state column values each of those numbers have a meaning:

1 = running
2 = finished
3 = dimnished

So what I want is to add the above string values right away to the tables array by comparing the values of the state column.

The current print result of a column would look like

array (size=2)
  'name' => string 'FRX' (length=11)
  'state' => string '2' (length=1)

But what I want is:

array (size=2)
      'name' => string 'FRX' (length=11)
      'state' => string 'finished' (length=8)

Barmar · Accepted Answer · 2015-02-24 16:51:10Z

1

You can either do it in SQL with a CASE expression:

SELECT name,
        CASE state
            WHEN 1 THEN 'running'
            WHEN 2 THEN 'finished'
            WHEN 3 THEN 'diminished'
        END AS state
FROM current_state

Or you could do it in PHP with an array:

$statemap = array(1 => 'running', 2 => 'finished', 3 => 'diminished');

Then your PHP loop would do the mapping:

while ($finalResult = $exc->fetch(PDO::FETCH_ASSOC)) {
    $finalResult['state'] = $statemap[$finalResult['state']];
    $tables[] = $finalResult;
}

Finally, you could have another table that contains the mappings, and join with it:

CREATE TABLE state_mappings (
    state_num INT PRIMARY KEY,
    state_name VARCHAR(20)
);

SELECT name, state_name
FROM state AS s
JOIN state_mappings AS m ON s.state = m.state_num

answered Feb 24, 2015 at 16:51

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace mysql coloumn values when adding them to an php array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related