How to fill a column based on first column

I am making a mySQL table which lists ~70 products and information on whether they are compatible or not. For the sake of simplifying the question, I will pretend there were only four products.

Product1    Product2    Compatible?
A           A           Yes
A           B           No
A           C           Maybe
A           D           Yes
B           A           Yes
B           B           Yes
B           C           Yes
B           D           No
C           A           Yes
C           B           Maybe
C           C           Yes
C           D           Yes
D           A           Yes
D           B           No
D           C           Yes
D           D           Yes

If I already have a table like (every product is obviously compatible with itself)

Product1    Product2    Compatible?
A           A           Yes
B           B           Yes
C           C           Yes
D           D           Yes

Is there a way I can quickly fill out the first two columns so they follow t开发者_JAVA百科he correct pattern? (so I dont have to be doing it manually)

One way to do this would be to use nested loops: If you know how many products you have, lets call it n products.

2^n total rows will be in your table. Additionally, product 1 will have each inventory item n times. (In your example 4 items, so 2^4 = 16 total rows and each item occurs in product1 column n=4 times.

Thus a nested loop can be achieved to do the insert...

$inventory = array(A, B, C, D); 
for(i=0;i<2^n;i++){
   for(j=0; j<n; j++){
       //insert Column1=$inventory[i], Column2=$inventory[j];
   }
}

Insert-After triggers!

And when inserting with phpMyadmin leave the column blank. let the trigger fill last column.

insert into compatibleProducts
select distinct p1.ProductID, p2.ProductID, 'Maybe'
from productTable p1
join productTable p2 on p1.ProductID <> p2.ProductID

I assumed that you already have them compatible with themselves, based on your second list.