Mysql – How to perform strictly SQL query that uses multiple values and searches all fields

MySQLmysqliPHP

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.

Best Answer

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;
}
Related Question