user3980196 user3980196 - 2 months ago 8
MySQL Question

Efficient SQL statement

TableX Data

On getting random associative array pairs from fields product_option_id:product_option_value_id like {"230":"23","228":"19"}, I have to get the minimum quantity from the respective rows in the above TableX.

Currently, I query the database through PHP based on each pair, and store their quantity. i.e.- If there are 3 pairs, I query the database 3 times based on each pair to get the 3 quantities. Then I loop through the quantities to get the least one.

If the random {product_option_id:product_option_value_id} pairs are {"230":"23","228":"19"}, can you make an efficient SQL statement that gets the minimum quantity.

I have tried

SELECT quantity FROM TableX
WHERE product_option_id=variable_product_option_id
AND product_option_value_id=variable_product_option_value_id


I run this in a PHP for loop for all the pair values. I store the quanity, and get the least one through PHP

Answer

You may try a query like this one:

select product_option_id, product_option_value_id, min(quantity)
    from product_option_value
    where (product_option_id = 230 and product_option_value_id = 43)
        or (product_option_id = 228 and product_option_value_id = 49)
    group by 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 into 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)
>     from product_option_value
>     where (product_option_id = 230 and product_option_value_id = 43)
>         or (product_option_id = 228 and product_option_value_id = 49)
>     group by product_option_id, product_option_value_id;
+-------------------+-------------------------+---------------+
| product_option_id | product_option_value_id | min(quantity) |
+-------------------+-------------------------+---------------+
| 228               | 49                      | 1             |
| 230               | 43                      | 2             |
+-------------------+-------------------------+---------------+

You should also quote the query parameters properly. Using constructs like ...product_option_id=".$key."... (excerpt from your example from the comments) is error-prone, as $key may contain anything, like Little Boby Tables.

Comments