2
\$\begingroup\$

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
\$\endgroup\$

migrated from programmers.stackexchange.com Jan 13 '16 at 19:16

This question came from our site for professionals, academics, and students working within the systems development life cycle.

  • 1
    \$\begingroup\$ Next step, look into Bound Parameters for MySQL. \$\endgroup\$ – JeffO Jan 13 '16 at 18:42
2
\$\begingroup\$

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;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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