Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
1  
What is the exact format of the data in the POST array? Does it have 3 items, each of which is an array? Does it have 9 items? SELECT * is rarely a good idea; select only the columns you need. – outis May 27 '11 at 5:18
The arrays contain only integers. Each of the arrays contain identical numbers of indexes. There are approximately 100 indexes in each array, hence the reason why I was looking for a way to have one query (a really long query using UNION as posted below). Also, I don't use SELECT *. I was only trying to make the query shorter in asking my question. – MKK May 28 '11 at 1:04

4 Answers

up vote 3 down vote accepted

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.

share|improve this answer
+1, nice answer. I will have a look at this for one of my tasks. One little mistake though. You got $arr[2] and $arr[3] with the wrong index – DKSan May 27 '11 at 5:09
Thanks DKSan, fixed it – babonk May 27 '11 at 5:13
Thanks @babonk. This is exactly what I was looking for. – MKK May 28 '11 at 1:03
up vote 0 down vote

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);
share|improve this answer
This will only accept 3 triples, though MKK presumably wants to support an arbitrary number of tuples. Furthermore, it will return an array with only the first generated statement. – outis May 27 '11 at 6:20
@outis, i only give the idea, but this can be further enhance... i.e. dynamic parameters... – ime May 27 '11 at 6:24
it's not a matter of enhancement. The sample code is incorrect. – outis May 27 '11 at 19:58
won't this not work?-- the foreach loop will return before its gon through all of arr_var? – babonk May 28 '11 at 5:40
@babonk: my point exactly. (Note: double negatives lack clarity in English. "Won't this fail" is a better phrasing than "won't this not work".) – outis Jun 1 '11 at 0:59
up vote 0 down vote

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.

share|improve this answer
Since the MySQL query is hard coded and the variables are only integers, it should be good enough (I think) to make sure all the values in the array are integers (if_numeric) and if not then I kill the script. – MKK May 28 '11 at 1:12
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.