PHP counting MYSQL rows that contain the same value in many columns

Question

Here is the content of my table:

How to count all names in different table cells to get a result like this?

Bob-7

Alex-3

Ivan-5

Nina-5
.........

With this code:

<?php
$q= mysqli_query($db, 'SELECT * FROM names');
    while ($all = mysqli_fetch_array($q)) {
        $k1= $all['Name1'];
        $k2= $all['Name2'];
        $k3= $all['Name3'];
        $k4= $all['Name4'];
        $k5= $all['Name5'];
        $k6= $all['Name6'];
        $arr[]= $k1;
        $total_values = array_count_values($arr);
    }
    foreach ($total_values as $key => $value) {
        echo $key .'-'. $value .'<br>';
    }

My output result is:

Bob-3

Alex-1

Ivan-1

Nina-2

When I change my code to:

<?php
$q= mysqli_query($db, 'SELECT * FROM names');
    while ($all = mysqli_fetch_array($q)) {
        $k1= $all['Name1'];
        $k2= $all['Name2'];
        $k3= $all['Name3'];
        $k4= $all['Name4'];
        $k5= $all['Name5'];
        $k6= $all['Name6'];
        $arr=array($k1, $k2);
        $total_values = array_count_values($arr);
        foreach ($total_values as $key => $value) {
            echo $key .'-'. $value .'<br>';
        }
    }

My output result is:

Bob-1

Alex-1

Ivan-1

Nina-1

Nina-1

Bob-1

Bob-1

Ivan-1

Nina-1

Nina-1
.......

What is wrong and what I have to do to add $k2, $k3....$k6 in the array $arr?

Sound like a good use case for crosstab queries. If you do the heavy lifting in the query you will not have to do a lot of manipulation with PHP. — Jay Blanchard
– Jay Blanchard, Commented Jan 25, 2016 at 14:56

Alan · Accepted Answer · 2016-01-25 18:50:19Z

4

This will count the number of occurences for a single name:

$q= mysqli_query($db, 'SELECT * FROM names');
$arr = array();

// 1) loop through rows
while ($all = mysqli_fetch_array($q, MYSQLI_NUM)) {
 // 2) loop through cells in a row
 foreach($all as $val) {
  // 3) 'roll out' the values into a one-dimensional array
  if(empty($val)) { continue; } // if you don't want to count empty cells
  $arr[] = $val;
 }    
}
// 4) count the number of occurences
$names_qty = array_count_values($arr);

// optional loop that shows the results
foreach($names_qty as $name=>$qty) {
 echo $name.'-'.$qty.'<br />';
}

The benefit of this solution is that you call the counting function only once and not in every iteration.

edited Jan 25, 2016 at 18:50

answered Jan 25, 2016 at 15:01

Alan

1,3321 gold badge21 silver badges36 bronze badges

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

5 Comments

RiggsFolly Over a year ago

Why should the OP "try this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer.

Borislav Razvanski Over a year ago

The code double the numbers of the names (after names)!

Alan Over a year ago

@BorislavRazvanski thanks for spotting this! It was happening because mysqli_fetch_array returned both, numerical and string keys so the number of cells in each row was doubled. After adding a second parameter to that function everything works ok.

Borislav Razvanski Over a year ago

Yes it is work now! But before you adding the second parameter in the function i use mysqli_fetch_assoc instead of mysqli_fetch_array and it is work fine! Thank you very much!

Alan Over a year ago

@BorislavRazvanski that's great, you're welcome. It will work with both functions. If my answer helped you please consider accepting it. Thanks :).

Ostin · Accepted Answer · 2016-01-25 15:27:28Z

-1

You have to accumulate data in a loop and print it after all data is processed.

$total = array();
$result = mysqli_query($db, 'SELECT * FROM names', MYSQLI_USE_RESULT);
while (mysqli_fetch_assoc($result) as $row) {
    foreach ($row as $col => $value)
        total[$col]++;
    }
}

print_r($total);

edited Jan 25, 2016 at 15:27

answered Jan 25, 2016 at 15:05

Ostin

1,5511 gold badge12 silver badges25 bronze badges

Comments

Huelfe · Accepted Answer · 2016-01-26 07:45:49Z

-1

This code is tested and should do the trick.

<?php
$arr = array();
$q = mysqli_query($db, 'SELECT * FROM names');
while ($all = mysqli_fetch_assoc($q)) {
    foreach($all as $val) {
        if(empty($val)) {
            continue;
        }
        // saves all names with the name as key
        $arr[$val][] = $val;
    } 
}

// Output
foreach($arr as $k => $v) {
    // count how many times the name was pushed into the array
    echo $k.' - '. count($v);
}

edited Jan 26, 2016 at 7:45

answered Jan 25, 2016 at 15:06

Huelfe

1,83618 silver badges25 bronze badges

5 Comments

RiggsFolly Over a year ago

Why should the OP "try this"? Especially as you admit it is untested! A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer.

Huelfe Over a year ago

Where's the problem? Yep it is tested.

Borislav Razvanski Over a year ago

It is work but The code double the numbers of the names (after names)!

Borislav Razvanski Over a year ago

To prevent the double numbers of the names I change mysqli_fetch_array to mysqli_fetch_assoc and the code worked perfect! I updated your post!

Huelfe Over a year ago

I've updated my answer. Would be great when you vote up this answer. ;)

Collectives™ on Stack Overflow

PHP counting MYSQL rows that contain the same value in many columns

3 Answers 3

5 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related