Build SQL query criteria from form input

Question

I am using a <form> to send various input parameters as a GET to retrieve data from a very simple two table MySQL db.

After filtering the inputs (resulting in $inputs), I'm using the following code to build the WHERE clause which I've adapted from a MATLAB function we use to build XQueries.

It works fine, but I'm just curious if there's a better way to do this in PHP, or if there is common practice for doing such.

public function parseInvoiceConstraints($inputs) {
 //pulled from different location but literals for this example
 $invTable = "invoices"
 $custTable = "customers"
 //used for queries
 $constraints = "";
 $sql_conj = "WHERE ";
 //parseargs into SQL WHERE 
 foreach ($inputs as $param => $val) {
 if ($val == false) {
 continue;
 }
 switch ($param) {
 case 'inv_num':
 $constraints = "$constraints"
 . "$sql_conj"
 . "$invTable.$param=$val";
 $sql_conj = " AND ";
 break;
 case 'name':
 $constraints = "$constraints"
 . "$sql_conj"
 . "$custTable.$param LIKE \"%$val%\"";
 $sql_conj = " AND ";
 break;
 case 'customer_id':
 $constraints = $constraints
 . "$sql_conj"
 . "$invTable.$param=$val";
 $sql_conj = " AND ";
 break;
 case 'pcs':
 $constraints = $constraints
 . "$sql_conj"
 . "$invTable.$param=$val";
 $sql_conj = " AND ";
 break;
 case 'tag_num':
 $constraints = $constraints
 . "$sql_conj"
 . "$invTable.$param=$val";
 $sql_conj = " AND ";
 break;
 }
 }
 return $constraints;
}

For the URL:

/invoice.php?inv_num=&name=gon&customer_id=&pcs=2&tag_num=72

the function will return:

WHERE customers.name LIKE "%gon%" AND invoices.pcs=2 AND invoices.tag_num=72

• JeffO: Next step, look into Bound Parameters for MySQL.

Answer 1

If it works, it's good but it's not very easy to understand. It is a Loop-switch_sequence and can be seen as an anti-patern. See: https://en.wikipedia.org/wiki/Loop-switch_sequence

If this is working, don't touch it, but if you have to simplify it, you can get rid of the loop and the switch and have just blocs like this.

if(!empty($inputs['inv_num']){
 if(!empty($constraints)){
 $constraints .= ' AND '; 
 }
 $constraints .= "$invTable.$param='$val'";
}
//....... other variables name, customer_id, pcs ...
$constraints = $sql_conj . ' ' . $constraints;