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

Question

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.

couldn't this be done easily in SQL? Send your table-info, pls — michi
– michi, Commented Feb 27, 2013 at 23:06
It could yes, but im trying this method to recycle a previously called SQL that cant have the results Grouped by anything. — user1221768
– user1221768, Commented Feb 27, 2013 at 23:09

ozahorulia · Accepted Answer · 2013-02-27 23:08:28Z

1

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.

answered Feb 27, 2013 at 23:08

ozahorulia

10k8 gold badges53 silver badges77 bronze badges

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

Comments

AmazingDreams · Accepted Answer · 2013-02-27 23:08:01Z

1

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"];

answered Feb 27, 2013 at 23:08

AmazingDreams

3,2342 gold badges24 silver badges32 bronze badges

Comments

berty · Accepted Answer · 2013-02-27 23:08:41Z

0

Try this :

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

answered Feb 27, 2013 at 23:08

berty

2,20612 silver badges20 bronze badges

Comments

WackyWalrus · Accepted Answer · 2013-02-27 23:21:54Z

0

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 />";
}

answered Feb 27, 2013 at 23:21

WackyWalrus

3241 silver badge7 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related