CODEWITHSUNDEEP
phpmysqlcodeigniter
Tux
Tux

Reputation: 1843

CodeIgniter ActiveRecord FULLTEXT Multiple Words Search

I'm trying to search for multiple words. I already, tried adding FULLTEXT to my database schema.

This is the code I have now, and only one word returns results.

$term = $this->input->post('term');

$this->db->like('firstname', $term);
$this->db->or_like('lastname', $term);
$this->db->or_like('middlename', $term);

$query = $this->db->get('people');
$data['people'] = $query->result();
$this->load->view('people', $data);

Searching for one word like "John" or "Doe" or "Smith" works.

But when I try searching for "John Doe" or "John Doe Smith" it won't return any results.

How can I achive multiple words search by using CodeIgniter's Active Record or a "$this->get->query".

Upvotes: 2

Views: 3393

Answers (2)

ADev
ADev

Reputation: 686

Try this:

$terms = explode(' ', $term);

foreach($terms as $term){
  $this->db->or_like('firstname', $term);
  $this->db->or_like('lastname', $term);
  $this->db->or_like('middlename', $term);
}


$query = $this->db->get('people');

EDIT: (after your comment)

  $parts = substr_count(trim($term), ' ');

  switch($parts){
      case 0:
          $this->db->or_like('firstname', $term);
          $this->db->or_like('lastname', $term);
          $this->db->or_like('middlename', $term);
          break;
      case 1;
          $this->db->or_like('CONCAT(firstname, " ", middlename)', $term);
          $this->db->or_like('CONCAT(firstname, " ", lastname)', $term);
          $this->db->or_like('CONCAT(middlename, " ", lastname)', $term);
          break;
      case 2:
      default:
          $this->db->or_like('CONCAT(firstname, " ", middlename, " ", lastname)', $term);
          break;

  }

  $query = $this->db->get('people');

Upvotes: 5

Rega
Rega

Reputation: 880

You're getting that result because you run the like sentence over single separated fields. Instead of that, u can try to concat the fields first & then running the query over the result, just like this:

$sql = "SELECT * FROM people WHERE CONCAT(firstname,' ', middlename,' ', lastname) LIKE '%$term%'";

$this->db->query($sql);

Upvotes: 1

Related Questions