V
V
Vadim2014-06-17 22:18:33
MySQL
Vadim, 2014-06-17 22:18:33

How to join tables in MySQL?

Good evening.
There are two tables:
The products table, which contains the name and description of the product.
Products_variants table, which contains trade offers: product_id, price, count.
One product can have several offers.

SELECT * 
FROM products
LEFT JOIN products_variants ON products_variants.product_id = products.id

This query displays a list of all offers, how to display only products and, for example, in the offers variable, all records from the products_variants table for this product?
Thank you.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
F
Facetrollex, 2014-06-17
@Facetrollex

2 solutions:
1)
We do everything with one request (you can not change the current request), i.e. you will have many rows with the same product_id (because the product_variants table is tied to product, like 1 : M ).
then on the backend you do something like this:

$prevId = 0;
$resultArr = array();
foreach($result as $row){
if($prevId != $row['product_id'])
{
   $resultArr[$row['product_id']] = $row; //сюда ложим поля таблицы product
   $resultArr[$row['product_id']]['offers'] = $row['...']//ложим оффер первой строки, если нет- пустой массив
} else
{
   $resultArr[$row['product_id']]['offers'] = $row['...']//ложим только необходимые поля
}
$prevId = $row['product_id'];
}

print_r($resultArr);

2)
almost the same as the first one, only in 2 requests. In the 1st, you get all the product, in the second, product_variants, and after executing the query, merge these 2 arrays by the product_id key (as in the 1st case)

A
AxisPod, 2014-06-18
@AxisPod

As usual, instead of reading the documentation, it's better to ask. GROUP CONCAT.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question