CODEWITHSUNDEEP
mysqlsql
masih arastooee
masih arastooee

Reputation: 49

SELECT query to find rows with a partial match of multi-word term in one of two columns

I have a table name 'user' for example. I have field 'name' and field 'family' in this table.

I use jQueryUI auto-complete for top search in my site for search people just like Facebook.

Example of jQueryUI I use (half code): 

$( "#mainsearch" ).bind( "keydown", function( event ) {
    if ( event.keyCode === $.ui.keyCode.TAB &&
    $( this ).data( "ui-autocomplete" ).menu.active ) {
        event.preventDefault();
    }
})

And I have PHP files get search result code like this:

$name = $_GET['term'];
$results = array();
$s = qselectall("select * from user where(name LIKE '%$name%' or family LIKE '%$name%' ) limit 15",$db);
while($f = mysqli_fetch_array($s,MYSQLI_ASSOC)){
    if($userid != $f['id']){
        $name = $f['name'].' '.$f['family'];
        $url = $siteurl.$f['username'].'/';
        array_push($results, array('id' => $f['id'],'value' => $name,'url' => $url));
    }
}
echo json_encode($results);

But it has 1 problem. User's cannot search with name and family. They must insert name OR family just in input box for it to work.

Is there any SQL code for search LIKE where( name and family = $text) ?

EXAMPLE:
We have someone with name 'alex' and with last name 'alexian'. So user search for 'alex alexian' but they get no results why?
Because not name and not family in table = 'alex alexian'. So they must search 'alex' or 'alexian' for it to work.

Upvotes: 0

Views: 274

Answers (1)

TheCarver
TheCarver

Reputation: 19713

Try this:

SELECT * FROM user
WHERE concat_ws(' ',name,family) 
LIKE '%$name%';

The concat_ws function concatenates multiple columns 'with separator' (_ws). In this case, the separator is a space (' '). See the MySQL documentation for further information.

Demo: http://www.sqlfiddle.com/#!2/aac0f/8

Upvotes: 1

Related Questions