count with array vs count with mysql

[phpscott]

I am working with a single table, that has 3 basic elements.  Name, Team, Position.  There are 10 teams and 4 positions. 

 

nameteamposition

bil1f

mike3g

sue9s

al7f

I need to make a grid of results.

 

Team#Pos1Pos2Pos3Pos4Total on Team

1341211

2432212

3513312

Totals1286735

 

I shortened the grid there but it will end up with all 10 teams in their own row.

 

What I am wondering is if its better to do mysql counts to gather the data or if looping arrays are the way to go.  The database table usually only has 400 rows.

 

Is there an easy way to make the output grid??

 

TIA!

[moselkady]

 

I think it will be better to do counts in mysql. This code may do the trick:

 

<?php
$sql = "SELECT team, position, count(position) AS n FROM mytable GROUP BY team, position";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
  $team[$row['team']][$row['position']] = $row['n'];
}
print_r($team);
?>

 

Then you can use $team array to print your table

 

 

 

 

 

 

 

[phpscott]

Dang it, I am not having any luck going from an array that has

 

Team, Pos, Count to table output of

Team, count pos1, count pos2, count pos3, count pos4, total#

 

any suggestions?

[moselkady]

Try something like this:

 

<?php
$sql = "SELECT team, position, count(position) AS n FROM mytable GROUP BY team, position ORDER BY team, position";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
  $count[$row['team']][$row['position']] = $row['n'];
}
foreach($count as $team=>$pos_counts)
{
  echo "$team\t";
  $team_total = 0;
  foreach($pos_counts as $pos=>$val)
  {
    $total[$pos] += $val;
    $team_total  += $val;
    echo "$val\t";
  }
  echo "$team_total\n";
}
foreach($total as $x) echo "\t$x";
echo "\t".array_sum($total)."\n";
?>

[phpscott]

Try something like this:

 

<?php
$sql = "SELECT team, position, count(position) AS n FROM mytable GROUP BY team, position ORDER BY team, position";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
  $count[$row['team']][$row['position']] = $row['n'];
}
foreach($count as $team=>$pos_counts)
{
  echo "$team\t";
  $team_total = 0;
  foreach($pos_counts as $pos=>$val)
  {
    $total[$pos] += $val;
    $team_total  += $val;
    echo "$val\t";
  }
  echo "$team_total\n";
}
foreach($total as $x) echo "\t$x";
echo "\t".array_sum($total)."\n";
?>

 

That is an AWESOME answer except that if a team is lacking players in a position, then the table does not come out right.

 

I was looking at making an array that would be:  Array(team, pos1count, pos2count, pos3count, pos4count) since there are only 4 positions , once all the data in read in, could make a table and include a 0 for what ever team does not have someone at the position.  My problem currently is I do not know how to make that array.  The places I have gone to learn about multidimensional and associated arrays did not  ??? make the light bulb come on for me.

[phpscott]

print_r($count) gives me

 

Array (

[0017] => Array ( [A] => 9 [D] => 14 [G] => 1 [M] => 20 )

[0018] => Array ( [A] => 10 [D] => 3 [G] => 5 [M] => 9 )

[0019] => Array ( [A] => 3 [D] => 3 [M] => 9 )

)

 

 

foreach($count as $theteam=>$pos_counts){
echo "	<tr><td>$theteam</td>
	<td>A $count[$theteam]['A']</td>
	<td>M $count[$theteam]['M']</td>
	<td>D $count[$theteam]['D']</td>
	<td>G $count[$theteam]['G'] </td>

 

gives me

 

0017  A Array['A']  M Array['M']  D Array['D']  G Array['G']

0018 A Array['A'] M Array['M'] D Array['D'] G Array['G']

0019 A Array['A'] M Array['M'] D Array['D'] G Array['G']

 

For some reason I thought that would give me the number in the array and not the array.

 

I feel like a box of hammers would do better at this than I would...