How to fill a column based on first column

Question

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 the correct pattern? (so I dont have to be doing it manually)

Answer 1

Insert-After triggers!

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

Answer 2

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.

Answer 3

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];
   }
}