Get all rows where 2 fields exist In Array for PDO SQL?

Question

I have an array structured like this:

Array
(
    [0] => Array
        (
            [0] => 1 //x
            [1] => 3 //y
        )

    [1] => Array
        (
            [0] => 8 //x
            [1] => 7 //y
        )

    [2] => Array
        (
            [0] => 9 //x
            [1] => 9 //y
        )
)

What I want to know is there a way to make a query to get all rows where 2 fields match any pair of values for the second level of arrays for example say i have 2 rows with:

   |  uid  |  id  |  x  |  y  |
    - - - - - - - - - - - - - 
   |   1   |  1   |  1  |  3  |  //both x and y exist together 
   |   1   |  1   |  9  |  9  |  //both x and y exist together
   |   1   |  1   |  9  |  5  |  //no combination do not select this

I'm trying to avoid looping the array and using SELECT every iteration, but would rather some way to do it directly in my query to lower the amount of looping.

IS this at all possible or is my only option to loop the array and query each one at a time...this to me seems quite intensive as the array grows in length!!

I was hoping maybe there is some in_array method for SQL?

Answer 1

My suggestion would be to generate a long query from the array you've provided.

<?php
$arr = array(
    array(1,3),
    array(8,7),
    array(9,9)
);

function wherify($val) {
    return "(`x` = ".$val[0]." AND `y` = ".$val[1].")";
}

$criteria = implode(" OR ", array_map("wherify", $arr));

$query = "SELECT * FROM `table` WHERE $criteria";
echo $query;

This would create a query that would look something like the following.

SELECT * FROM `table`
    WHERE (`x` = 1 AND `y` = 3)
       OR (`x` = 8 AND `y` = 7)
       OR (`x` = 9 AND `y` = 9)

(execution)