Complex Sql Statement

Anon

I am having problems figuring this one out..

I am doing a select and I want to know how many of FOO.ROOM members exist with each query.

$select = "select FOO.NAME AS NAME, FOO.EMAIL as EMAIL, FOO.DESC as DESC from FOOBAR FOO where (FOO.JOB = '$this_job');

there will be multiple rows that are accessed in this table. There is a column named FOO.ROOM which contains the room number of the FOO Participant. How can I tell how many people are in each FOO.ROOM without using a for statement, with multiple select statements.

I don't know if this is possible but any help would be appreciated.

MikeJ

Anon

I can't promise that the syntax here is correct but you basically could construct an index array(key => value) as you loop through the result set. For each record,it loops through the array and determines if the room yet exists in the array, if it doesn't it pushes the key value of $room with a value of 1 into the array. If the room does already exist in the array it adds 1 to the current value of of the $room key. You would end up with an array in the format of $room => number of users. You could then access the number by saying $number_of_users = $roomArray[$room]

$roomArray = array();
$count = 0;
$select = "select FOO.NAME AS NAME, FOO.EMAIL as EMAIL, FOO.DESC as DESC, FOO.ROOM as ROOM from FOOBAR FOO where (FOO.JOB = '$this_job')";
$results = mysql_query($select);
while($row = mysql_fetch_into($results)) {
$name = $row[0];
$email = $row[1];
$desc = $row[2];
$room = $row[3];
for(reset($roomArray); $key = key($roomArray); next($roomArray)) {
if($key == $room) {
$roomArray[$key] = ($roomArray[$key] + 1);
$count++;
}
}
if($count == 0) {
array_push($roomArray, $room => 1)
}
$count = 0;

//do something with the data?
}

Anon

You can do this in one SQL query by using a COUNT :

select foo.room
,count('*')
from FOOBAR FOO
GROUP BY FOO.ROOM

For each room you'll get the count of room.

Hope I've understand your needs.

If the database can do the job, let it do. It'll be faster.

JBL