How to implement an adjacency matrix in MySql?

Question

I am looking for the best possible way to implement the following problem in mysql .. i am working on phpmyadmin . Like 1 have 2 columns "id" and "ingredients" in my table .. let say the table is ..

id        ingredient
201        Garlic
201        Ginger
202        tomato
203        Garlic
203        potato
203        Ginger....so on

I want to create the adjacency matrix with ingredient as column header and row header .. like ginger and garlic both have a relation in id 201 and id 203 where as garlic and potato in id 203 so according to this we will form adjacency matrix and so on with all other ingredients

              Garlic   Ginger    Tomato        potato
  Garlic      0          2           0            1
  Ginger      2          0           0            1
  Tomato      0          0           0            0
  Potato      1          1           0            0....so on

and I also wanna store this in database like ingredient-ingredient relation

  ingredient     ingredient    frequency
   Garlic            Garlic        0
   Garlic            Ginger        2
   Garlic            Tomato        0
   Garlic            Potato        1
   Ginger            Garlic        2    .... and so on

Answer 1

Once you got a table with the ingredient-ingredient frequencies, you can pivot on that.

--
-- Setup test data
--
create table ingredients (
 id int not null, 
 ingredient varchar(30) not null
);

insert into ingredients (id, ingredient) values
  (201, 'Garlic') 
, (201, 'Ginger') 
, (202, 'Tomato') 
, (203, 'Garlic') 
, (203, 'Potato') 
, (203, 'Ginger') 

--
-- a table for ingredient combinations
--
create table ingredient_combinations (
 id int auto_increment primary key, 
 ingredient1 varchar(30) not null, 
 ingredient2 varchar(30) not null, 
 frequency int default 0 not null
)

--
-- insert all unique ingredient combinations
--
insert into ingredient_combinations (ingredient1, ingredient2)
select distinct i1.ingredient, i2.ingredient
from (select distinct ingredient from ingredients) i1
cross join (select distinct ingredient from ingredients) i2
where i1.ingredient < i2.ingredient
order by i1.ingredient, i2.ingredient

--
-- update the frequencies
--
update ingredient_combinations combo
left join (
  select 
    i1.ingredient as ingredient1
  , i2.ingredient as ingredient2
  , count(*) as frequency
  from ingredients as i1
  join ingredients as i2 
    on i1.id = i2.id 
   and i1.ingredient < i2.ingredient
  group by i1.ingredient, i2.ingredient
  having count(*) > 0
) q on combo.ingredient1 = q.ingredient1
  and combo.ingredient2 = q.ingredient2
set combo.frequency = coalesce(q.frequency, 0);

-- what do we have so far
select * from ingredient_combinations order by id;

id | ingredient1 | ingredient2 | frequency
-: | :---------- | :---------- | --------:
 1 | Garlic      | Ginger      |         2
 2 | Garlic      | Potato      |         1
 3 | Garlic      | Tomato      |         0
 4 | Ginger      | Potato      |         1
 5 | Ginger      | Tomato      |         0
 6 | Potato      | Tomato      |         0

--
-- pivot the combos
--
with CTE_COMBOS as
(
  select ingredient1, ingredient2, frequency
  from ingredient_combinations
  union
  select ingredient2, ingredient1, frequency
  from ingredient_combinations
)
select ingredient1
, max(case when ingredient2 = 'Garlic' then frequency else 0 end) as Garlic
, max(case when ingredient2 = 'Ginger' then frequency else 0 end) as Ginger
, max(case when ingredient2 = 'Potato' then frequency else 0 end) as Potato
, max(case when ingredient2 = 'Tomato' then frequency else 0 end) as Tomato
from CTE_COMBOS
group by ingredient1
order by ingredient1

ingredient1 | Garlic | Ginger | Potato | Tomato
:---------- | -----: | -----: | -----: | -----:
Garlic      |      0 |      2 |      1 |      0
Ginger      |      2 |      0 |      1 |      0
Potato      |      1 |      1 |      0 |      0
Tomato      |      0 |      0 |      0 |      0

db<>fiddle here