mysql - Efficient SQL statement -

on getting random associative array pairs fields product_option_id:product_option_value_id {"230":"23","228":"19"}, have minimum quantity respective rows in above tablex.

currently, query database through php based on each pair, , store quantity. i.e.- if there 3 pairs, query database 3 times based on each pair 3 quantities. loop through quantities least one.

if random {product_option_id:product_option_value_id} pairs {"230":"23","228":"19"}, can make efficient sql statement gets minimum quantity.

i have tried

select quantity tablex  product_option_id=variable_product_option_id    , product_option_value_id=variable_product_option_value_id 

i run in php loop pair values. store quanity, , least 1 through php

you may try query one:

select product_option_id, product_option_value_id, min(quantity)     product_option_value     (product_option_id = 230 , product_option_value_id = 43)         or (product_option_id = 228 , product_option_value_id = 49)     group product_option_id, product_option_value_id; 

in practice:

> create table product_option_value(product_option_id int, product_option_value_id int, quantity int);  > insert product_option_value values >     (230, 43, 2), >     (230, 32, 1), >     (228, 49, 1), >     (228, 50, 0), >     (229, 50, 1), >     (229, 49, 1), >     (230, 43, 8), >     (230, 32, 9), >     (228, 49, 11), >     (228, 50, 10), >     (229, 50, 5), >     (229, 49, 4);  > select product_option_id, product_option_value_id, min(quantity) >     product_option_value >     (product_option_id = 230 , product_option_value_id = 43) >         or (product_option_id = 228 , product_option_value_id = 49) >     group product_option_id, product_option_value_id; +-------------------+-------------------------+---------------+ | product_option_id | product_option_value_id | min(quantity) | +-------------------+-------------------------+---------------+ | 228               | 49                      | 1             | | 230               | 43                      | 2             | +-------------------+-------------------------+---------------+ 

you should quote query parameters properly. using constructs ...product_option_id=".$key."... (excerpt example comments) error-prone, $key may contain anything, little boby tables.

edit: single minimum such groups, drop group , grouping columns, this:

> select min(quantity) >     product_option_value >     (product_option_id = 230 , product_option_value_id = 43) >         or (product_option_id = 228 , product_option_value_id = 49); +---------------+ | min(quantity) | +---------------+ | 1             | +---------------+