0

I'm trying to create what I would call an "inclusive" search and by that, I'm not referring to "between". I have a snippet of code and it does what I need it to do but it's hacky and inefficient so I'm wanting to know if I can possibly do it with just SQL. I'm using a third party library called MysqliDb but if you want to forego phrasing your answer in that format, it's fine.

        if ($this->data != '') {
        $searchParams = explode(' ', trim($this->data));
        foreach ($searchParams as $s) {
            $this->db->orWhere('customer_name', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_address', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_city', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_zip', '%'.$s.'%', 'like');
            $this->db->orWhere('customer_email1', '%'.$s.'%', 'like');
        }
        $this->db->having('customer_status', 'Active');
        $this->db->having('owner_id', $this->owner_id);
        $binaryArray = array_fill(0, sizeof($searchParams), 0);
        $results = $this->db->get('tblcustomers');
        $filtered = [];
        foreach ($results as $r) {
            $binaryArray = array_fill(0, sizeof($searchParams), 0);
            foreach ($binaryArray as $key=>$b) {
                if (strpos(strtolower($r['customer_name']), strtolower($searchParams[$key])) !== false ||
              strpos(strtolower($r['customer_address']), strtolower($searchParams[$key])) !== false ||
              strpos($r['customer_city'], strtolower($searchParams[$key])) !== false ||
              strpos($r['customer_zip'], strtolower($searchParams[$key])) !== false ||
              strpos($r['customer_email1'], strtolower($searchParams[$key])) !== false
            ) {
                    $binaryArray[$key] = 1;
                }
            }
            if (!in_array(0, $binaryArray)) {
                $filtered[] = $r;
            }
        }
    } else {
        $this->db->where('owner_id', $this->owner_id);
        $this->db->having('customer_status', 'Active');
        $filtered = $this->db->get('tblcustomers');
    }

$this->data is an input box with possible search words separated by spaces so my intent is to split the value of the input box up based on that and perform a query that has to include all of the parameters. By that I mean, I can easily do a query where it retrieves every row with $this->data[0] or $this->data[1] but I want to exclude rows that don't actually have have all of the words in $this->data in one column or another.

What I have does that but it uses a lot of PHP and I'd rather keep the db querying to SQL exclusively.

3
  • Show us the generated SQL. More of us can help you that way. (And you will find out how verbose that package is.) – Rick James Jul 15 '19 at 22:22
  • Read about FULLTEXT indexing. – Rick James Jul 15 '19 at 22:22
  • Yes, I think it is a FULLTEXT thing, I'll give that try. – Joshua Wilkeson Jul 16 '19 at 13:42
0

Well I looked into the Full Text Search but found it a bit cumbersome to perform it on joined tables, additionally, it's not that efficient so I just cleaned up the function I have

public function findByFilterQuery($request)
{
    $dbFields = ['first_name', 'last_name', 'email_address', 'tt_group.name', 'tt_position.name', 'tt_division.name'];
    $params = isset($request['search']) ? explode(' ', trim($request['search'])) : [];
    $users = User::ArrayBuilder()->with('group')->with('position')->with('division');
    foreach ($params as $param) {
        foreach ($dbFields as $field) {
            $users->orWhere($field, '%'.$param.'%', 'like');
        }
    }
    $results = [];
    $request['page'] = isset($reqest['page']) ? $this->data['page'] : 1;
    $users = $users->paginate($request['page']);
    if (sizeof($params) && $users) {
        foreach ($users as $user) {
            $binaryArray = array_fill(0, sizeof($params), 0);
            foreach ($params as $key=>$param) {
                foreach ($dbFields as $dbColumn) {
                    $val = $user;
                    foreach (explode('.', $dbColumn) as $subColumn) {
                        $val = $val[$subColumn];
                    }
                    $val = strtolower($val);
                    $param = strtolower($param);
                    if (strpos($val, $param) !== false) {
                        $binaryArray[$key] = 1;
                    }
                }
            }
            if (!in_array(0, $binaryArray)) {
                $results[] = $user;
            }
        }
    }
    return sizeof($results) ? $results : $users;
}

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.