How to find similar results from all rows in DB with MySQL query

Question

I would like to make "Search you'r LOGIN" like in facebook :

Eg. searching for "Stock Overflow" would return

Stack Overflow
SharePoint Overflow
Math Overflow
Politic Overflow
VFX Overflow

Eg. searching for "LO" would return:

pabLO picasso
michelangeLO
jackson polLOck

Eg. searching for username "user123" would return :

 user123
 user1234
 and etc ...

My Database rows :

userid  |   username  |  useremail   |  user_fname   |  user_lname

I would like to make a search input that search the word in any of this rows like above examples,

Here my php till now :

$string = $purifier->purify(@$_POST['string']); 

          $query = "SELECT * FROM users WHERE user_fname = '".$string."' OR user_lname = '".$string."' OR username = '".$string."' OR useremail= '".$string."'";
          mysql_query("SET NAMES 'utf8'");
          $result2 = mysql_query($query);
          $num = mysql_num_rows($result2);

          if($num == 1)
          {
              //true
          }else{

              //not found nothing
          }

this way is not working good , and its not return all the similar reuslts of the word that i put in search input. plus how i return it with foreach if there is more then 1 similar result ?

Thanks allot.

Update :

Thanks all , my updated code to fix it :

 $query = "SELECT * FROM users WHERE user_fname like '%".$string."%' OR user_lname like '%".$string."%' OR username like  '%".$string."%' OR useremail like '%".$string."%'";

and i am not using mysql , just for the examples i had more easy to do like this..

have a look at this http://stackoverflow.com/a/20186289/829533 — zzlalani, Nov 25 '13 at 06:47
also you must know that mysql_* functions are depreciated http://stackoverflow.com/q/12859942/829533 — zzlalani, Nov 25 '13 at 06:48
i using mysqli prepared , here i used normal mysql for test its more easer — Raviv g, Nov 25 '13 at 06:53

score 2 · Accepted Answer · answered Nov 25 '13 at 06:46

2

try this

$string = $purifier->purify(@$_POST['string']); 

      $query = "SELECT * FROM users WHERE user_fname like '%".$string."%' OR user_lname like '%".$string."%' OR username like  '%".$string."%' OR useremail like '%".$string."%'";
      mysql_query("SET NAMES 'utf8'");
      $result2 = mysql_query($query);
      $num = mysql_num_rows($result2);

      if($num == 1)
      {
                //true
              }else{

                 //not found nothing
              }

answered Nov 25 '13 at 06:46

Anshul Parashar

3,096
4
30
56

i wil test it , 1 moment. – Raviv g Nov 25 '13 at 06:47
in case of `user123` this query will search for `user1234` and `Auser123` too – zzlalani Nov 25 '13 at 06:49
1

yea this what i need , thanks i just have problem with utf8 when i put text in Hebrew , i dont get any result and say wrong all time. i will flag your post as accepted soon as the system will give me,i need wait few minutes. – Raviv g Nov 25 '13 at 06:52
@zzlalani according to user need its work because its used `OR`operator here – Anshul Parashar Nov 25 '13 at 06:53

zzlalani · Answer 2 · 2013-11-25T06:53:55.893

Change your query with this

$query = "SELECT * FROM users WHERE user_fname LIKE '".$string."%' OR user_lname LIKE '".$string."%' OR username LIKE '".$string."%' OR useremail LIKE '".$string."%'";

NOTE:

If you have user123 keyword and you want to make a search for all the rows that have data user123* you can apply the wildcard $string%

And in case of *user123* you can use %$string%

And in case of *user123 you can use %$string

score 0 · Answer 3 · edited Nov 25 '13 at 06:47

0

try this

$query = "SELECT * FROM users WHERE user_fname like '%".$string."%' OR user_lname like '%".$string."%' OR username like '%".$string."%' OR useremail like '%".$string."'%";

edited Nov 25 '13 at 06:47

zzlalani

22,960
16
44
73

answered Nov 25 '13 at 06:46

Rakesh Shewale

497
6
22

Kalpit · Answer 4 · 2013-11-25T06:49:06.960

0

try this way

$query = "SELECT * FROM users WHERE user_fname LIKE '%".$string."%' OR user_lname = '%".$string."%' OR username = '%".$string."%' OR useremail= '%".$string."%'";

for info for Like keyword

Note

Please avoid mysql_... use mysqli_ or PDO

edited Nov 25 '13 at 06:49

answered Nov 25 '13 at 06:47

Kalpit

4,906
4
25
43

score 0 · Answer 5 · edited Nov 25 '13 at 07:27

0

try this:

$query = "SELECT * FROM users 
WHERE user_fname LIKE '%$string%' 
OR user_lname LIKE '%$string%' 
OR username LIKE '%$string%' 
OR useremail LIKE '%$string%'";

edited Nov 25 '13 at 07:27

Tobia Zambon

7,479
3
37
69

answered Nov 25 '13 at 07:08

mamun0024

71
5

How to find similar results from all rows in DB with MySQL query

5 Answers5