How to write a SQL query for a page?

Question

PHP

Hello everyone, I need to collect certain data from several tables, the data is related, here is an example:
there are tables:
Products:
id | name | desc | price
Recipes:

id | name | own_id | product_use_1 | product_use_2 ... product_use_9

Comments:
id | product_id | comment | own_id
Users:
id | name
We need to connect to extract the following data:
1. There is a product ID
2. We need to find matches of id in Recipes in one of the 9 product_use
2.1 Get all the information about the recipe.
3. Get the comments where the product_id matches the product ID 4. Get the id, username for all comments on this product by
own_id in the comment
queries to the database...
What will be better and how to implement it more correctly?
Please advise, I will be very grateful

Answer 1

If I understand correctly:

select
usr.*,
recept.*
from 
(
  select distinct
  t2.*
  from tovary t1
  inner join recepty t2 	on t2.product_use_1 = t1.id
              or t2.product_use_2 = t1.id
              or t2.product_use_3 = t1.id
              // и тд до t2.product_use_9
      where t1.id = ####
) as recept
inner join 
(
  select
  t1.id,
  t1.name,
  t2.own_id
  from 
  users  t1
  inner join comments t2 on t1.id = t2.own_id
) as usr on usr.own_id = recept.own_id

Answer 2

write what happened, do not forget to issue in CODE

Answer 3

It only depends on your ultimate goals.
If you only need comments, then through join.
If comments can be loaded later, then make several requests.

Answer 4

I was a little confused by the "Recipes" table.
This is your system and base, i.e. Did you design it?
It's just that sheets don't usually do this in databases.
As far as the query is concerned, one can start to get a list of recipes
SELECT DISTINCT r.*
FROM Recipes r,
Products t
WHERE t.id = XXXX AND( t.id = r.product_use_1 OR t.id = rtproduct_use_2 OR ..... ...... t.id = rtproduct_use_9)
But once again I suggest, if possible, to revise and redo the Recipe table