CODEWITHSUNDEEP
php
user1221768
user1221768

Reputation: 45

php - Need a foreach loop to display only 1 set of values

I'm trying to write a script that takes results from an SQL query and as it goes through each result it checks to see what the name of $row[13] should be and then Im wanting as it goes through each query result it identifies any names that are similar and keeps a count of how many times it sees that name. I thought I had a loop figured out that would count how many times the same name shows up in an array and then display it. The end result I am looking for is 

Operations - 87
Training - 32
HR - 12

But after I run this code:

      while($row = mysql_fetch_array($result_id))
  {
        $profile8 = mysql_query
                    ("
                        SELECT org
                        FROM cost_centers
                        WHERE cost_center = '$row[13]'
                    ")
                    or die(mysql_error());
                    $anymatches=mysql_num_rows($profile8);
                        while($pro8 = mysql_fetch_array($profile8))
                        {
                            $orgnames = $pro8[0];
                        }


                            $names[] = $orgnames;

                            $newArray = array_count_values($names);

                            foreach ($newArray as $key => $value) {
                                echo "$key - $value <br />";
                            }

  }

I end up with it continuiously counting like this:

HR - 1 
HR - 1 
Communications - 1 
HR - 1 
Communications - 1 
- 1 
HR - 1 
Communications - 1 
- 2 
HR - 1 
Communications - 1 
- 3 
HR - 1 
Communications - 1 
- 4 
HR - 2 
Communications - 1 
- 4 
HR - 3 
Communications - 1 
- 4 
HR - 3 
Communications - 1 
- 4 
Operations - 1 
HR - 4 
Communications - 1 
- 4 
Operations - 1 
HR - 4 
Communications - 1 
- 5 
Operations - 1 
HR - 4 
Communications - 1 
- 6 
Operations - 1 
HR - 4 
Communications - 1 
- 6 
Operations - 2 
HR - 4 
Communications - 1 
- 6 
Operations - 3 
HR - 4

So it almost looks like its grouping then counting, then regrouping as it keeps adding its count. I hope this makes sense and thanks in advance.

Upvotes: 2

Views: 1089

Answers (4)

WackyWalrus
WackyWalrus

Reputation: 324

You're on the right track.

Before the while make an array:

$org = array();

Now, in the foreach, make a key in your new array using $key, and then add one 

foreach ($newArray as $key => $value) {
    $org[$key] += 1;
}

And when you're done with the while($row = mysql_fetch_array($result_id)) loop, make another foreach loop and show the results.

foreach($org as $name => $count){
    echo $name . " = " . $count . "<br />";
}

Upvotes: 0

berty
berty

Reputation: 2206

Try this :

SELECT cost_centers.org, count(*) FROM cost_centers WHERE cost_center = what_you_want GROUP BY cost_centers.org

Upvotes: 0

ozahorulia
ozahorulia

Reputation: 10084

That happans because you are printing counters everytime inside the loop. That how it must to be:

<?php

while($row = mysql_fetch_array($result_id))
{
    $profile8 = mysql_query("
        SELECT org
        FROM cost_centers
        WHERE cost_center = '$row[13]'")
    or die(mysql_error());

    $anymatches=mysql_num_rows($profile8);
    while($pro8 = mysql_fetch_array($profile8))
    {
        $orgnames = $pro8[0];
        $names[] = $orgnames;
    }
}

$newArray = array_count_values($names);

foreach ($newArray as $key => $value) {
    echo "$key - $value <br />";
}

Anyway it's not a good choice to make a new query for every row in the first row. You should learn SQL better, especially about JOIN and (in your case) COUNT operator.

Upvotes: 1

AmazingDreams
AmazingDreams

Reputation: 3204

Why don't you use the following query:

SELECT 
    SUM(IF(org='HR', 1, 0)) AS HRCount,
    SUM(IF(org='Communications', 1, 0)) AS ComCount,
    SUM(IF(org='Operations', 1, 0)) AS OpCount,
FROM cost_centers
WHERE cost_center = '$row[13]'

Then you can just acces the values by calling $row["HRCount"];

Upvotes: 1

Related Questions