PHP mysql multiple words search query

Question

I made a query for search(max three words) but its not quite helpful when increasing the number of words, hence I need a code for n number of codes

here is my site

 public function search() {
            $s_keywords = $this->input->get('q');           
            $arr = explode(" ", $s_keywords);

            if (count($arr) > 0) {
                $query = $this->db->query("SELECT 
                    vacancy_id, vc_title, vc_experience, vc_closedate, tbl_company_cmp_name 
                    FROM tbl_vacancy
                    WHERE CONCAT(vc_title, ' ', tbl_company_cmp_name)  =  '$s_keywords' ");             
                $result = $query->result();

                return array( 'job_search' => $result,);
            }

            if (count($arr) == 1) {
                $query = $this->db->query("SELECT vacancy_id, vc_title, vc_experience, vc_closedate, tbl_company_cmp_name FROM tbl_vacancy
                WHERE vc_title LIKE '%{$s_keywords}%' OR tbl_company_cmp_name LIKE '%{$s_keywords}%' ");
                $result = $query->result();

                return array( 'job_search' => $result,);
            } else if (count($arr) == 2) {
                $query = $this->db->query("SELECT vacancy_id, vc_title, vc_experience, vc_closedate, tbl_company_cmp_name FROM tbl_vacancy
                WHERE (vc_title LIKE '%{$arr[0]}%' OR tbl_company_cmp_name LIKE '%{$arr[0]}%' ) "
                . "AND (vc_title LIKE '%{$arr[1]}%' OR tbl_company_cmp_name LIKE '%{$arr[1]}%') ");
                $result = $query->result();

                return array( 'job_search' => $result,);
            } else if (count($arr) == 3) {
                $query = $this->db->query("SELECT vacancy_id, vc_title, vc_experience, vc_closedate, tbl_company_cmp_name FROM tbl_vacancy
                WHERE (vc_title LIKE '%{$arr[0]}%' OR tbl_company_cmp_name LIKE '%{$arr[0]}%' ) "
                . "AND (vc_title LIKE '%{$arr[1]}%' OR tbl_company_cmp_name LIKE '%{$arr[1]}%') "
                . "AND (vc_title LIKE '%{$arr[2]}%' OR tbl_company_cmp_name LIKE '%{$arr[2]}%') ");
                $result = $query->result();

                return array( 'job_search' => $result,);
            }
        }

You need to add much more detail as to what you are looking for. — M H, Aug 16 '15 at 08:26
@Hanoncs like what? this is my model for search (I'm using codeigniter) — Sahan Perera, Aug 16 '15 at 08:32

score 2 · Accepted Answer · edited May 23 '17 at 11:52

As you can see here you need either an OR statement (like you did) or use REGEXP for your query.

Either way you need to create the query string and then run the query. I'd loop over each word, e.g.

$query = 'SELECT vacancy_id, vc_title, vc_experience, vc_closedate, tbl_company_cmp_name FROM tbl_vacancy';
$wheres = array();

foreach ($arr as $keyword) {
$wheres[] = "vc_title LIKE '%{$skeyword}%' OR tbl_company_cmp_name LIKE '%{$keyword}%' ";
};

//join the where lines with 'AND'
$where = join(' AND ', $wheres);

$query = $query . ' WHERE ' . $where;
//run the query

$query = $this->db->query($query);
$result = $query->result();
return array( 'job_search' => $result,);

The same way you can build the regex, we don't need the foreach in this case as we have the single words in the array:

$regex = join('|', $arr);
$query = 'SELECT vacancy_id, vc_title, vc_experience, vc_closedate, tbl_company_cmp_name FROM tbl_vacancy';

$where = " WHERE vc_title REGEXP '$regex' OR tbl_company_cmp_name  REGEXP '$regex'";
$query = $this->db->query($query . $where);
$result = $query->result();
return array( 'job_search' => $result,);

score 0 · Answer 2 · answered Aug 16 '15 at 08:37

Something like this?

$arr = array("sads", "test", "do work");

$q = "SELECT vacancy_id, vc_title, vc_experience, vc_closedate, tbl_company_cmp_name FROM tbl_vacancy WHERE ";
foreach($arr as $a) {
    $q .= "AND (vc_title LIKE '%{$a}%' OR tbl_company_cmp_name LIKE '%{$a}%') ";
}

echo $q;

PHP mysql multiple words search query

2 Answers2