Complex SQL statement: how do I select rows that match conditions of two columns where conditions are provided by an array

Question

I have two tables

CREATE TABLE IF NOT EXISTS products ( 
   product_id int(11) NOT NULL AUTO_INCREMENT
   , name varchar(30) NOT NULL
   , PRIMARY KEY (product_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; 

CREATE TABLE IF NOT EXISTS product ( 
 product_id int(11) NOT NULL
  , size decimal(7,2) NOT NULL
  , price decimal(7,2) NOT NULL
  , PRIMARY KEY (product_id,size) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO product (product_id, size, price) 
VALUES (1, '150', '3')
, (2, '1000', '50')
, (2, '1500', '70')
, (3, '500', '35'); 

INSERT INTO products (product_id, name) 
VALUES 
(1, 'protein-bar')
, (2, 'whey-protein')
, (3, 'casein');

product_id | name        
---------: | :-----------
         1 | protein-bar 
         2 | whey-protein
         3 | casein      

product_id |    size | price
---------: | ------: | ----:
         1 |  150.00 |  3.00
         2 | 1000.00 | 50.00
         2 | 1500.00 | 70.00
         3 |  500.00 | 35.00

db<>fiddle here

that I merge into one, giving me the following table to work with:

product_id |   name     |  size  | price
   1        protein-bar    150       3
   2        whey-protein   1000      50
   2        whey-protein   1500      70
   3        casein         500       35
etc...

Now as you can see, product_id is not unique and the key is product_id and size.

What I want to do is fetch all products with a certain id and size match which is picked by the user.

For example, I have the following order:

Array ( [1] => Array ( [Quantity] => 3 [Size] => 150) [2] => Array ( [Quantity] => 1 [Size] => 1500) )

Keys of this array are equal to product_id.

What I want to do is fetch those user selected products from the database, the output I would expect is:

product_id |   name     |  size  | price
   1        protein-bar    150       3
   2        whey-protein   1500      70

To attempt doing this I did the following:

1. merge the order of the user in an array where the first values are the product_id and the last values are the product size, giving me the following array:

Array ( [0] => 1 [1] => 2 [2] => 150 [4] => 1500)

2. SQL statements:

    $array_to_question_marks = implode(',', array_fill(0, count($products_in_cart), '?'));
    $stmt = $pdo->prepare('SELECT * FROM products INNER JOIN product ON (products.product_id = product.product_id) WHERE products.product_id IN (' . $array_to_question_marks . ') AND product.size IN (' . $array_to_question_marks . ')');
    $fill_question_marks = array_merge(array_keys($products_in_cart), array_column($products_in_cart, 'Size'));
    $stmt->execute($fill_question_marks);

Now, what this does is return all products who have the id's and sizes provided. With an issue being that it doesn't match the product id to size. Giving me the following output:

product_id |   name     |  size  | price
   1        protein-bar    150       3
   2        whey-protein   1000      50
   2        whey-protein   1500      70

Whey-protein with size 1000 shouldn't be there since the user didn't select it.

I have been trying to solve this for days. While doing so I came along a stackoverflow answer with this command:

SELECT * FROM table WHERE (column1, column2) IN ( (A, X), (B, Y), (C, Z) );

Which seems to answer my question, but I couldn't get it to work. (The command might not be correct, don't limit your answer to it; I just included it to show that I have put a lot of effort into finding an answer and tried several methods, none which worked for me).

**This is my first stackoverflow question, tried to make everything clear through using bold statements and code blocks. As well as trying to abide by the stackoverflow 'conventions' such as showing your effort. This problem has been annoying me for days and I finally decided to create a stackoverflow account and ask about it, your answers are GREATLY appreciated. THANKS.

Answer 1

Finally found a solution. Used the SQL statement provided by @SOS and @Akina which is:

SELECT * FROM products JOIN product USING (product_id) WHERE (product_id, product.size) IN ((1, 150), (2,1500))

But since I don't know the items/I have to pull them from an array in the form (?, ?), (?, ?), etc.... I changed my $array_to_question_marks code to the following:

$array_to_question_marks = implode(',', array_fill(0, count($products_in_cart), '(?, ?)'));

and used the aforementioned variable as an input to the SQL statement as such:

$stmt = $pdo->prepare('SELECT * FROM products JOIN product USING (product_id) WHERE (product_id, product.size) IN (' . $array_to_question_marks . ')');

Now to populate the data I did the following:

$population_array = array();
for($i=0; $i<count($products_in_cart); $i++){
    array_push($population_array, array_keys($products_in_cart)[$i]);
    array_push($population_array, $products_in_cart[array_keys($products_in_cart)[$i]]['Size']);
}

Which pushes the array key (which is equal to product_id) followed by the corresponding size in the form product_id, size, product_id, size etc.. which matches the SQL statement form.

Finally my complete SQL command looks like this:

    $array_to_question_marks = implode(',', array_fill(0, count($products_in_cart), '(?, ?)'));
    $stmt = $pdo->prepare('SELECT * FROM products JOIN product USING (product_id) WHERE (product_id, product.size) IN (' . $array_to_question_marks . ')');
    $stmt->execute($population_array);
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

Answer 2

For example, I have the following order:

Array ( [1] => Array ( [Quantity] => 3 [Size] => 150) [2] => Array ( [Quantity] => 1 [Size] => 1500) )

Keys of this array are equal to product_id.

SELECT *
FROM products
JOIN product USING (product_id)
WHERE (product_id, product.size) IN ((1, 150), (2,1500))

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e841464f422774042ae1a8ca5ba0478e

PS. I don't know how Quantity value of the array should be applied - no such column in source tables.