correlation matrix

[jackvull]

I have a table with a list of companies and I need to run correlation data on each of the combinations:

CompanyID, Code, Name, sectorcode
1 AAA AAA 4
2 BBB BBB 4
3 CCC CCC 4
4 DDD DDD 4
5 EEE EEE 5
6 FFF FFF 5
7 GGG GGG 5

So, I must only run the sproc on companies in the same group, ie all those in group 4. I have to compare 1 vs 2, 1vs3, 1vs4, 2vs3,2vs4, and 3vs 4 or any combination of up to 10 companies in each group.

What is the best way to write a loop for this to run multiple combinations?
The SQL actually works out the correlations so it's more that I need to run a matrix either in PHP code or in MySQL.

SELECT sectorId, COUNT(*) FROM `companies` GROUP BY sectorId HAVING COUNT(*) > 1;
Take the sectorId from above and get the company codes.
Need something here to work out what combinations to run:
CALL GetStats ('AAA', 'BBB');
end loop
next sectorcode

[coopster]

I would just use a foreach loop on each, combining as you go. You may need to split the string first of course in order to make each Code and Name an array of characters.

$code = str_split('AAA'); 
$name = str_split('BBB'); 
$combos = array(); 
foreach ($code as $c) { 
  foreach ($name as $n) { 
    $combos[] = "{$c}{$n}"; 
  } 
}