MySQL multiple selects in one query

Question

for example I have few tables:
Products:

| product_id | name   | price |
| 1          | apple  | 20.32 |
| 2          | pear   | 9.99  |
| 3          | banana | 1.5   |

Product attribute:

| attr_id | name   | value |
| 1       | weight | 10 kg |
| 2       | date   | 2013  |
| 3       | color  | red   |

...and so on.
Finally product-attribute relations table:

| product_id | attr_id |
| 1          | 3       |
| 2          | 1       |
| 1          | 2       |
| 3          | 2       |

My question : is there available construct ONE select request query that returns product 1 and 2 in following data structure(or similar)? Now I should run deveral select requests first "where product_id IN (1, 2)" and then throught loop select them attributes.
Sorry for bad English :]

array(
    [0] = array(
          product_id = 1,
          name = apple,
          attributes= array(
                        [0] => array(
                           attr_id = 3,
                           name = color,
                           value = red,
                        ),
                        [0] => array(
                           attr_id = 2,
                           name = date,
                           value = 2013,
                        ) 

                      ),
    ),
    [1] = array(
          product_id = 2,
          name = apple,
          attributes= array(
                        [0] => array(
                           attr_id = 1,
                           name = veight,
                           value = 10 kg,
                        ),
                      ),
    )  
)

Do you want to get nested array with product and it's properties data, like the one on your example? I'm just not sure whether I understood you. — MihanEntalpo
– MihanEntalpo, Commented Aug 26, 2013 at 12:08
Yes, I need get multiple products with their attributes in one query. If it posible :) — Mindaugas
– Mindaugas, Commented Aug 26, 2013 at 12:16
The better design would be if you save all attributes in Products table... — Justin
– Justin, Commented Aug 26, 2013 at 12:18

Alma Do · Accepted Answer · 2013-08-26 12:16:14Z

This is not a matter of only query, but also PHP code. This will fit:

$rSelect = mysqli_query('SELECT
     products.id AS record_id,
     products.name AS products_name,
     products.price AS product_price, 
     attributes.id AS attribute_id,
     attributes.name AS attribute_name,
     attributes.value AS attribute_value
   FROM
     products 
     LEFT JOIN products_attributes
       ON products.id=products_attributes.product_id
     LEFT JOIN attributes
       ON products_attributes.attr_id=attributes.id', $rConnect);

$rgResult = [];
while($rgRow = mysqli_fetch_array($rSelect))
{
   $rgResult[$rgRow['record_id']]['product_id']   = $rgRow['record_id'];
   $rgResult[$rgRow['record_id']]['name']         = $rgRow['product_name'];
   $rgResult[$rgRow['record_id']]['price']        = $rgRow['product_price'];
   $rgResult[$rgRow['record_id']]['attributes'][] = [
      'attr_id' => $rgRow['attribute_id'],
      'name'    => $rgRow['attribute_name'],
      'value'   => $rgRow['attribute_value'],
   ];
};
//var_dump($rgResult);

nl-x · Accepted Answer · 2013-08-26 12:27:57Z

0

The query that you are looking for is:

select
    p.product_id,
    p.name as product_name,
    a.attr_id,
    a.name as attr_name,
    a.value
from
    products as p
    inner join `product-attribute` as pa on p.id = pa.product_id
    inner join attribute as a on pa.attr_id = a.attr_id

That will return a simple result table from where you can create a multidimensional array.

You may notice 2 things in this query:

I use backticks ` around table name product-attribute because it contains a dash - in the name, which is reserved. So you need to escape the name by putting it in backticks.
because field name name is ambiguous, I give them an alias (product_name/attr_name) so they can later still be referenced to by alias name.

edited Aug 26, 2013 at 12:27

answered Aug 26, 2013 at 12:22

nl-x

11.9k7 gold badges36 silver badges67 bronze badges

Collectives™ on Stack Overflow

MySQL multiple selects in one query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related