PHP MYSQL - Filter and sort function

Question

I m working on a filter and sort option for a website and seem to be stuck. The whole code can be seen here: http://pastebin.com/pYfnLiSZ

It's not 100% complete ... parts like this

        if(array_key_exists('number', $sorts))
        {
            $issue = $sorts['number'];

            $setIssue = "AND i.item_number = ?";
        }

still need to be edited a bit, but I hope it's understandable.

The part that I can't figure out is this(line 126 in pastebin):

if( !empty($userIDS) && $letter )
        {
            $ref = array();

            foreach($userIDS as $id)
            {
                $ref[] = $id['followed'];
            }

            $ref[] = $letter;

            $params = implode(",", array_fill(0, count($userIDS), "?"))."?";
            $prep = implode("", array_fill(0, count($userIDS), "i"))."s";

        }

I'm not sure how to make the above if dynamic. I only need it if $userIDS array is not empty and either $letter or $year or $publisher or $number are set. So it can basically be any combination. Items belonging to the users in the $userIDS array and filtered by letter, year, publisher, number and maybe in the future other filters. Or it can be year and publisher, or publisher, number and year ... and so on.

Answer 1

I use something similar to this at work, but obviously not as thrown together:

$search = [
    'fields' => [
        'name',
        'phone',
        'email'
    ],
    'filter' => [
        'name LIKE' => 'Joe'
    ]
];

function search(array $search) {
    $allowedFields = [
        'name',
        'phone',
        'email',
        'address',
    ];

    $allowedFilters = [
        'name',
        'phone',
        'email',
    ];

    $allowedActions = [
        '=',
        'LIKE',
        '<',
        '<=',
        '>',
        '>=',
    ];

    $fields = array();
    if(isset($search['fields'])) {
        foreach($search['fields'] as $field) {
            if(in_array($field, $allowedFields)) $fields[] = $field;
        }
    } else {
        $fields = $allowedFields;
    }

    $filters = array();
    if(isset($search['filters'])) {
        foreach($search['filters'] as $filter => $val) {
            $filter_split = explode(' ', $filter);
            $field = $filter;
            $action = '=';
            if(count($filter_split) > 1) {
                $field = $filter_split[0];
                $action = $filter_split[1];
            }

            if(!in_array($action, $allowedActions)) continue;

            if(in_array($field, $allowedFilters)) $filters[] = "{$field} {$action} {$val}";
        }
    }

    $fields_str = implode(',', $fields);
    $query = "SELECT {$fields_str} FROM users "

    if(count($filters) > 0) {
        $first = true;
        foreach($filters as $filter) {
            $action = 'AND';
            if($first) {
                $action = 'WHERE';
                $first = false;
            }
            $query .= "{$action} {$filter} ";
        }
    }

    mysqli_query($db, $query);

}

Answer 2

Since you are trying to "Combination of the constraints or conditions", I can suggest you the below pattern that i've used for shops:

1. define a class
2. make rules
3. get query

4. optimize for cache or other things

    class condition {
   
    public function add($query) {
        $this->query[] = $query;
        return $this;
    }
   
    public function get() {
        return " SELECT * FROM TABLE WHERE " . implode(" and " ,$this->query);
   
    }
   
   
    $con = new condition();
     $query =   $con->add("price > 100 and price < 200")->add("year > 1980")->get();