高度动态的表过滤

Question

I have a problem with filtering a SQL table based on user input; I can’t quite figure out how to make it robust to the range of inputs I need to accommodate.

Essentially, user-input pulled from POST data is used to build an array ($filterarray) from a SQL table containing filter parameters. Three columns are pulled into the array from each selected table row; column 1 is a string corresponding to a column name in 'finaloutputs' SQL table, column 2 contains a comparison operator, column three is an integer value. So, if returned in sequence as a string, each row of the array builds a selection filter, such as “column_x < 10” or "columm_y = 6". $filterarray can have anywhere from 1 to 100+ rows, pointing to various columns in finaloutputs, and using any comparison operator.

A query such as the below works fine:

$cf1 = wombats
$cf2 = “=”
$cf3 = 0

$result = $DBLink->query("SELECT id FROM finaloutputs WHERE $cf1 $cf2 $cf3");

But this is just injecting one set of variables directly. How can I do essentially this with a whole array of parameters? The code needs to work under “and”-style operator; returned results must satisfy ALL filters.

It seems that this should somehow be possible with a combination of “foreach” and “array_filter” or “unset”. I can’t quite determine how to actually do it though.

For example, something like this (but that works…):

//$filterarray contains in each row:
    // string matching a column in finaloutputs table ('cf1')
    // comparator ('cf2')
    // value ('cf3')

$result = $DBLink->query("SELECT * FROM finaloutputs");
$resultarray = $result->fetch_assoc();

foreach ($filterarray as $row){
    unset($resultarray[WHERE $row['cf1'] . $row['cf2'] . $row['cf3']]); 
}

I realize my first example is including based on filter being true, second example is excluding based on true filter. I really don't care which I use--I can swap the comparison operators to suit--I'm just looking for efficient code!

Thanks in advance for any help!

Answer 1

Why not let SQL filter the data so you get the result you want?

This should work:

$sql = 'select * FROM finaloutputs WHERE TRUE';
foreach ($filterarray as $row){
    $sql .= ' AND ' . $row['cf1'] . $row['cf2'] . $row['cf3']; 
}
$resultarray = $DBLink->query($sql)->fetch_assoc();