PHP mysql多词搜索查询

Question

我做了一个搜索查询(最多三个单词)，但是当增加单词数时没有多大帮助，因此我需要一个n个代码的代码。

这是我的网站

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

Answer 1

因为您可以请看这里，所以您需要一个OR语句(就像您以前做的那样)，或者使用REGEXP进行查询。

无论哪种方式，您都需要创建查询字符串，然后运行查询。我会循环每一个单词，例如。

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

与构建regex的方式相同，在本例中我们不需要foreach，因为数组中只有一个单词：

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

Answer 2

像这样吗？

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