Build a dynamic SQL statement via $_POST
<p>So this is more about getting your opinion on the best approach for this.</p>

<p>I have what I think is quite an elegant way of building a simple dynamic SQL statement with a straightforward WHERE clause. The WHERE clause can include multiple fields but it is limited as it does not allow for <strong>different</strong> operators (comparative or logical).</p>

<p>I can build the following with this:</p>

<pre><code>SELECT * from table_name WHERE field_1 = "value_1" AND field_2 = "value_2";
//or I can do
SELECT * from table_name WHERE field_1 = "value_1" OR field_2 = "value_2";
//or I can do
SELECT * from table_name WHERE field_1 &lt;&gt; "value_1" AND field_2 &lt;&gt; "value_2";
</code></pre>

<p>I can <strong>not</strong> build the following:</p>

<pre><code>SELECT * from table_name WHERE field_1 = "value_1" AND field_2 &lt;&gt; "value_2";
//nor can I do
SELECT * from table_name WHERE field_1 = "value_1" AND field_2="value_2" OR field_3 = "value_3
</code></pre>

<p>It becomes a real problem when working with numbers and dates when I want to look for records with values between meaning I need to pass the same filed in twice with two separate values.... doesnt it?</p>

<pre><code>SELECT * from table_name WHERE price BETWEEN 10 AND 20;
SELECT * from table_name WHERE date BETWEEN "2016-08-01" AND "2016-08-15";
</code></pre>

<p>And not forgetting multiple criteria with "IN" or LIKE statements which this also does not build, i.e.:</p>

<pre><code>SELECT * from table_name WHERE field_1 IN("value_1","value_2, "value_3");
SELECT * from table_name WHERE field_1 LIKE "val%";
</code></pre>

<p>Here is what my current code looks like:</p>

<pre><code>// db contains my DB connection
$db = new DB();

$where = 'WHERE';
$criteria = array();

foreach ($_GET as $key =&gt; $value) {
    $where = $where.' '.$key.'=? AND';
    array_push($criteria,$value);
}

if(count($_GET) &gt; 0){
   // $sql will look like: SELECT * FROM table_name WHERE field_1 = ? AND field_2 = ?
   // $criteria is an array of values to pair with the above prepared statement. 
   // Will look like: $criteria("value_1", "value_2")
   $sql = 'SELECT * FROM mcl_data_gap '.$where;
   $results = $db-&gt;query($sql,$criteria);
} else {
    $sql = 'SELECT * FROM mcl_data_gap';
    $results = $db-&gt;query($sql);
}
// .... continue on using above SQL statement
</code></pre>

<p>In the above code I have used get but my assumption is post would also work.</p>

<p>The only idea I have come up with is to insert more key value pairs that contain the operators required in a coded format that would allow me to then look for these operators and build the statement based on them but I just feel like there is a better way and that is what I am hoping you can help with.</p>

<p>Another option I have just thought of is building the SQL before passing it to the server and just executing that.</p>

<p>Or can I post objects that contain the whole segment of the WHERE statement?</p>