CODEWITHSUNDEEP
phpmysql
ndAR
ndAR

Reputation: 371

Mysql search like 2 words order

I would like to ask about an issue i'm facing.

I have the full name in 1 column stored, example: "John Doe".

But on perform a search for example:

SELECT * FROM client WHERE name LIKE '%Doe John%';

Result: 0 Rows.

If i switch to the exact match store in database it found a row.

SELECT * FROM client WHERE name LIKE '%John Doe%';

Result: 1 Row.

My question is, how to do to search in database without taking the order of word.

In OOP framework the code is:

$like = 'Doe John';
$this->db->select('*')->from('client')->like('name', $like)->get();

Also i tested this but i got the same result:

$this->db->select('*')->from('client');
if($this->containTwoWords($like)) {
  $explode = explode(' ', $like);
  foreach($explode as $exploded){
    $this->db->like('name', $exploded);
  }
} else {
  $this->db->like('name', $like);
}
$this->db->get();

function containTwoWords($like){
  if(strpos($like, ' ') !== false) {
    return true;
  }
  return false;
}

If somebody passed trough this and has better solution i appreciate the sharing !.

Upvotes: 0

Views: 173

Answers (4)

Just_Do_It
Just_Do_It

Reputation: 821

Try something like this:

<?php
$search_term = 'john doe abc';
$keywords = explode(" ", preg_replace("/\s+/", " ", $search_term));
foreach($keywords as $keyword){
 $wherelike[] = " name LIKE '%$keyword%' ";
}
$where = implode(" and ", $wherelike);

$query = "select * from client where $where";

echo $query;
//select * from client where name LIKE '%john%' and name LIKE '%doe%' and name LIKE '%abc%'

?>

Upvotes: 1

Scott
Scott

Reputation: 867

If you are wanting to explode the $like variable into first/last name (and you always expect 2 names, separated by a space), then this should work for you.

PHP has the built-in explode() method which does exactly what you are asking.

$like = "John Doe";
$name = explode(" ", $like);

// This created a 0-based array, meaning the first item as split
// by the space between John and Doe is given the array index of 0.
// $name[0] = John
// $name[1] = Doe

So in practice with MySQL that could be used as the following:

$query = "SELECT * FROM client WHERE name LIKE '%" . $name[0] . "%' AND name LIKE '%" . $name[1] . "%'";

For more information on the explode() function, check out the PHP manual at: http://php.net/manual/en/function.explode.php

Hope this helps!

Upvotes: 0

JCK
JCK

Reputation: 1

Try this name like '%John%' and name like '%Doe%'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

One option for your example case would be to check for the first and last names separately:

SELECT * FROM client WHERE name LIKE '%Doe%' AND name LIKE '%John%'

If you have exhausted such tricks with the LIKE operator, then you can look into using MySQL's full text search capabilities.

Upvotes: 1

Related Questions