MySQL/PHP - Count the specific values in columns

Question

In a table unit, I have 18 columns which can contain one of three different values in each of the columns. The possible values are 'N', 'I' or 'ETP'.

Three of those columns are:

|------------|-------------|------------|
|gcc_1_1     |gcc_1_2      |gcc_1_3     |
|------------|-------------|------------|

I want to return counts of each of those three values for each column to put into their own variables, e.g. the following variables would be for the first column and the three possible values.

$gcc_1_1_n
$gcc_1_1_i
$gcc_1_1_etp

What would be the simplest way of achieving this in PHP using one MySQL query?

  $query = "SELECT * FROM unit";
    $result = $connection->query( $query );
    while ($row = mysqli_fetch_array($result)) {
       ...
    }

The best method in terms of performance and ease would be to do it directly in PHP. — Skyd
– Skyd, Commented Nov 3, 2021 at 7:30

R4ncid · Accepted Answer · 2021-11-03 07:45:45Z

1

you could do something like this

<?php
$data = [...]; //array of data in the form of [['gcc_1_1' => 'n',... ], ...]
 
$groupedData = array_reduce(
   $data,
   function(array $res, array $row){
      foreach($row as $column => $value) {
        $key = $column.'_'.$value;
        $existing = $res[$key] || 0;
        $res[$key] = $existing + 1;
       }
      return $res;
    }, 
  []
);

Now in your $groupedData you have an array with keys that matches your request. You can use extract() to create n variables but I think you shouldn't do it because it's harder to handle and debug for future issues

answered Nov 3, 2021 at 7:45

R4ncid

7,1651 gold badge8 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

MySQL/PHP - Count the specific values in columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related