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.

Alex · Accepted Answer · 2015-06-02 19:14:40Z

1

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

define a class
make rules
get query

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();

edited Jun 2, 2015 at 19:14

answered Jun 2, 2015 at 18:54

Alex

7135 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ciprian Over a year ago

so the way I have it setup is wrong ? or will it be slow?

Alex Over a year ago

It's more about how you will maintain it in future, sometimes simple solution are quick but hard to manage in the portion of time, probably close future!

Ciprian Over a year ago

I understand. I ll try your way. But it's still a little confusing. I have to write it out to see everything. Thank you.

Alex Over a year ago

Understanding the concept of chaining is a major effect on this pattern development.

Jim Wright · Accepted Answer · 2015-06-02 19:18:47Z

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);

}

Collectives™ on Stack Overflow

PHP MYSQL - Filter and sort function

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related