0

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.

TheCarver
  • 19,391
  • 25
  • 99
  • 149
masih arastooee
  • 49
  • 5
  • Did you think about, in your query, changing the word `or` to the word `and` – Jessica Dec 12 '13 at 21:49
  • please understand my question name and family is two field in mysql - if u search want search someone with name='alex' and last name='alexian' so u put search alex alexian so not name and not family in table = alex alexian – masih arastooee Dec 12 '13 at 21:52
  • See: http://stackoverflow.com/questions/3183778/how-can-i-search-for-multiple-terms-in-multiple-table-columns – Jessica Dec 12 '13 at 21:57
  • i get 'The used table type doesn't support FULLTEXT indexes' i use innodb table – masih arastooee Dec 12 '13 at 22:04
  • 1
    @Jessica: It's worth noting that the question you linked to suggests FULLTEXT search. There are some issues using this method on a shared hosting environment as the default minimum word length is 4-characters long. This means you will not match any Tom's or Bob's. If the user is not on a shared hosting environment, they can, however, change the configuration file to accept 2-characters or more, but needs to know what they are doing. – TheCarver Dec 12 '13 at 22:04
  • 1
    Searching for this topic on SO reveals plenty of other times it's been asked with other possible solutions as well. OP did no research. – Jessica Dec 12 '13 at 22:07
  • i have server for my own but i use cpanel and cpanel not update mysql for 8 months (still 5.5.34 version) i think this version not support fulltext search so its not have another way? – masih arastooee Dec 12 '13 at 22:08
  • 1
    MySQL full-text indexing was available in version 5.5 for MyISAM tables. It was available for InnoDB in version 5.6.4 (I think). You could either update your MySQL version or convert your table to MyISAM and add `ft_min_word_len = 2` in the `my.cnf` config file to allow 2-character words in search. – TheCarver Dec 12 '13 at 22:19
  • thank you all anyway for your help ..your comments help me alot – masih arastooee Dec 12 '13 at 22:22
  • @masiharastooee, try my suggestion below. – TheCarver Dec 12 '13 at 22:30

1 Answers1

1

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

TheCarver
  • 19,391
  • 25
  • 99
  • 149