problem with assigning sql result to php variable

[want2php]

Hi,

 

i have a table which contains hundreds of data.

 

some of the fields were named 'cur_stat' and 'user_id'.  'cur_stat' has 10 different statuses in integer (1... 10).  'user_id' contains user ids in integer.  They are not unique so same data can occur many for both fields.  My intention is to count and group the same type of 'cur_stat' for each 'user_id'.

 

This is my code so far.

 

$sql = "SELECT count(cur_stat) as tot, cur_stat, user_id FROM user_statuses GROUP BY cur_stat, user_id";
$result = mysql_query($sql);
$while ($row=mysql_fetch_array($result) {
     switch ($row['user_id']) {
         case '1';
             	if ($row['cur_stat'] == '1') {
		$count_stat1 = $row['tot'];			
	} elseif ($row['cur_stat'] == '2') {
		$count_stat2 = $row['tot'];
	} elseif ...... // and so on.
	break;

case // and and so on and so forth.
                     
    }
}


<table>
<tr>
	<td>User No</td>
	<td>status 1</td>
	<td>Status 2</td>
	<td>Status 3</td>
	<td></td> // and so on...		
</tr>
<tr>	
	<td>echo $count_stat1</td>
	<td>echo $count_stat2</td>
	<td> //// and so on
</tr>
</table>

 

 

As you can see, it will be a very tedious coding.  I have hundreds of users and loads of statuses and if i continue my

coding as above, that would mean i have to assign a varialbe for each count for each user. 

 

If anyone can help me simplify and optimize this task would be great.

 

Thanks all for your help

[.josh]

If I'm understanding you correctly, you could just use a multi-dim array:

 

// use _assoc instead of _array because _array returns both numerical and associative index and you don't need both
while ($row = mysql_fetch_assoc($result)) {
  $count_stat[$row['user_id']][$row['cur_stat']] = $row['cur_stat'];
}
// example to see structure:
echo "<pre>";print_r($count_stat);