Help make a request

Question

PHP

There are 3 tables: categories, products, options.

category

id | cat_name
1 | phones
2 | laptops
3 | TVs

goods

id | product_name | cat_id
1 | galaxy s3 | 1
2 | Philips | 3
3 | xperia | 1
4 | lenovo | 2
5 | chromebook | 2 id

options |

variant_name | variant_price | product_id
1 | green | 120 | 5
2 | red | 150 | 5
3 | blue | 70 | 5
4 | gray | 200 | 2
5 | green | 130 | 4
6 | black | 250 | 3
7 | white | 300 | 1

Task: Display a list of categories, products in each category, and variants of each product.

Hidden text

Телефоны
    galaxy s3
        белый - 300
    xperia
        черный - 250

Ноутбуки
    lenovo
        зеленый - 130
    chromebook
        зеленый - 120
        красный - 150
        синий - 70

Телевизоры
    philips
        серый - 200

Now the code looks like this:

$query = query('SELECT FROM * категории');
while ($res = array($query)){
    echo $res[cat_name];

    $query2 = query('SELECT * FROM товары WHERE catid=$res[id]');
    while ($res2 = array($query2)){
        echo $res2[product_name];

        $query3 = query('SELECT * FROM варианта WHERE product_id=$res2[id]');
        while($res3 = array($query3)) {

            echo $res3[variant_name];

        }

    }

}

Naturally, this generates a lot of requests. Whether it is possible to make somehow selection 1 request?

Answer 1

<?
$sql = <<< TXT
  SELECT
    cats.id as catsid, prod.id as prodid, cats.cat_name, prod.product_name, vars.variant_name
  FROM категории cats, товары prod, варианты vars
  WHERE
    cats.id = prod.cat_id
    AND prod.id = vars.product_id
  ORDER BY cats.id, prod.id
TXT;

...

$cat_id = -1;
$prod_id = -1;
foreach( ... as $line){
  if($cat_id != $line['catsid']){
    $cat_id = $line['catsid'];
    $prod_id = -1;
    echo $line['cat_name'], "\n";
  }
  if($prod_id != $line['prodid']){
    $prod_id = $line['prodid'];
    echo "\t", $line['product_name'], "\n";
  }
  echo "\t\t", $line['variant_name'], "\n";
}

Answer 2

In such form as it is necessary to deduce - it is impossible. You can make two JOINs, pull out all the data (optionally filtered by category and product codes), and then form an array with the desired structure through foreach (or, if the selection is sorted, immediately display by inserting the necessary ifs inside the foreach).
But in the case of large amounts of data, you need to look for the middle between the number of requests and the amount of data per request. For example, pull out categories and in a loop for each pull out products with options.

Answer 3

I take it you are new to sql? The request is something like this:
select * from категории, товары, варианты where варианты.product_id = товары.id and товары.cat_id = категории.id
But the names of categories and products will be repeated. As mentioned above, you will need to take care in php to display the data correctly. However, keep in mind that performance is often better with several light queries than one heavy one.

Answer 4

The question was asked before leaving and messed up the structure a bit. There is 1 more table there, which is why 2 of the 3 tables from the question in the join query have that 4th table.
I took the request truekenny as an example , and after going through several options, I got what I needed.
In the original, I also have grouping, but in the resulting query I did without it, only sorting.
The execution time for both options is about the same. We need to try another option that UZER2006 wrote in the last sentence.
In general, I will test a little more and sort out the options.
sevka_fedoroff , I know how to make joins, but I haven’t come across the rest
Thanks to everyone who helped