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

0 votes
1 answer
147 views
                          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 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. 

                        
Asked by dan178 (101 rep)
Jul 15, 2019, 08:10 PM
Last activity: Jul 22, 2025, 11:08 AM
How to perform strictly SQL query that uses multiple values and searches all fields

Related Questions
