MySQL: How to create a query that executes using array variables on array indexes?

Question

I have a query with 3 variables that I run over and over. How can I create a query that will accept arrays of values such that I can run the query once and get all the data in one result? Here is an example:

• SELECT * FROM table WHERE column 1 = 3 AND column 2 != 35 AND column 3 > 10
• SELECT * FROM table WHERE column 1 = 9 AND column 2 != 12 AND column 3 > 293
• SELECT * FROM table WHERE column 1 = 6 AND column 2 != 96 AND column 3 > 39

I need the query to execute such that the first values (index 0) of each array get run together, then the second values (index 1) of each array get run together and so on. In other words, I want the query to run using the values [based on above example] (3,35,10) then (9,12,293) and so on.

The query needs to be stand alone meaning I need to be able to pass the 3 arrays via $_POST to a remote server that will get directly plugged into the query and executed on the remote server.

Using IN and NOT IN will not work because those comparison operators do not go in order of array indexes.

Any thoughts would be greatly appreciated. I have tried searching many places for solutions and cannot find anything. This type of query might be called something, so maybe that is why I have found nothing.

Answer 1

In php, you could build a UNION ALL query as follows:

$vals = array(array(3,35,10), array (9,12,293)) //this can be built from $_POST or wherever

$queryArr = array();
foreach ($vals as $arr)
{
 $queryArr[] = "SELECT * FROM table WHERE column 1 = $arr[0] AND column 2 != $arr[1] AND column 3 > $arr[2]";
}

$query = implode(' UNION ALL ', $queryArr);  //note: if you want to do separate queries, rather than a UNION of them, just use $queryArr

PS: As outis mentioned in his comments, you should avoid SELECT * and specify the fields for the purpose of clarity.

Answer 2

I think you need to execute the query for different array value.... So you need to have a function which accept array variable, and then execute all the array items in this query right!.

 function result_table($arr_var){
      $sizeof_arr = sizeof($arr_var);
      if($sizeof_arr == 3){
         foreach ($arr_var as $val)
        {
           $qry_array[] = "SELECT * FROM table WHERE column 1 = $val[0] AND column 2 !=    $val[2] AND column 3 > $val[3]";

           return $qry_array;
        }
      }else{
           echo "Array values number is wrong";
      }

 }

 $arr_val = array((3,35,10), array (9,12,293), array(6, 96, 39));
 $union_qry = result_table($arr_val);

Answer 3

Since you'll need to prevent SQL injection, you'd do well to use a prepared statement.

If $_POST contains arrays of triples:

# sample data
$_POST = array(array(3,35,10), array(9,12,293), array(6, 96, 39));

# utility functions
function array_flatten_into($source, &$target) {
    foreach ($source as $key => $val) {
        if (is_array($val)) {
            array_flatten_into($val, $target);
        } else {
            $target[] = $val;
        }
    }
    return $target;
}
function array_flatten($arr) {
    $flat = array();
    return array_flatten_into($arr, $flat);
}

# create the query
$statement = 'SELECT ... FROM table WHERE ' 
             . implode(' OR ', 
                       array_fill(0, count($_POST), '(col1 = ? AND col2 != ? AND col3 > ?)'));
$query = $db->prepare($statement);
$query->execute(array_flatten($_POST));

If $_POST is a flat array:

$_POST = array(3,35,10, 9,12,293, 6, 96, 39);

$statement = 'SELECT ... FROM table WHERE ' 
             . implode(' OR ', 
                       array_fill(0, count($_POST)/3, '(col1 = ? AND col2 != ? AND col3 > ?)'));
$query = $db->prepare($statement);
# call to `array_values` isn't necessary in this particular example, but may be
# in the actual code if $_POST is sparse or has non-integer keys
$query->execute(array_values($_POST));

Once you have more than 1 triple, performance will degrade as MySQL will need to run a table scan.

Answer 4

I think you need to take a look into prepared statements. You can use PDO extension . Here when you are running the same query over and over again, the query will be compiled when executed first time and we can bind the values many time.