need help with counting stuff from database..

[opec]

in my ibf_pfields_content table, I want to count the number of times each number occurs, and each number corresponds to a name, 1 is spectator, 3 is trumpet, 4 is horn, and so forth.
here's the code:

$section = Array();
$section[1] = "Spectator";
$section[3] = "Soprano.Trumpet";
$section[4] = "Mellophone.Horn";
... and so on

for($i = 1; $i <=23; $i++) {

$query = "SELECT count(field_2) FROM ibf_pfields_content WHERE field_2 = " .  $i;
$result = mysql_query($query);
while ($info = mysql_fetch_assoc($result)) 
{
	$data[$i] = $info['count(field_2)'];
echo($section[$i] . " has " . $data[$i] . " members. <br />");	
	}

and here is the bit of the skin file which the user reads:

<tr>
		<td class='row2'><font color=888888>&nbsp;&nbsp;Spectator</td>
		<td class="row2" align=center><font color=888888>{$data[1]}</td>
	</tr>
	<tr>
		<td class='row2'><font color=888888>&nbsp;&nbsp;Soprano.Trumpet</td>
		<td class="row2" align=center><font color=888888>{$data[3]}</td>
	</tr>
	<tr>
		<td class='row2'><font color=888888>&nbsp;&nbsp;Mellophone.Horn</td>
		<td class="row2" align=center><font color=888888>{$data[4]}</td>
	</tr>
...and so on

My problem is, it only does the counting for spectator and doesnt do the rest. I wish I weren't such a helpless newbie. 🙁

[eoghain]

Well you should start by changing your query to do all of the work for you instead of selecting from the table 23 different times. Also I would hope that you have the mapping data of field_2 to the textual name somewhere in your database so you don't have to modify that array everytime you add or remove something. If you can set this up you can do all of your work in a single query.

--- ibf_pfields_content ---
field_2 int not null

--- field_2_types ---
id int not null
name varchar(255)

$query = "SELECT field_2_types.name, count(*) AS count
            FROM ibf_pfields_content, field_2_types
           WHERE field_2_types.id = ibf_pfields_content.field_2
           GROUP BY name
           ORDER BY id";

$result = mysql_query($query); 
    while ($info = mysql_fetch_assoc($result))  

    { 
        echo($info["name"] . " has " . $info["count"] . " members. <br />");     

    }