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..

also you must know that mysql_* functions are depreciated stackoverflow.com/q/12859942/829533 — zzlalani
– zzlalani, Commented Nov 25, 2013 at 6:48
i using mysqli prepared , here i used normal mysql for test its more easer — Raviv g
– Raviv g, Commented Nov 25, 2013 at 6:53

Anshul Parashar · Accepted Answer · 2013-11-25 06:46:05Z

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, 2013 at 6:46

Anshul Parashar

3,1264 gold badges34 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Raviv g Over a year ago

i wil test it , 1 moment.

zzlalani Over a year ago

in case of user123 this query will search for user1234 and Auser123 too

Raviv g Over a year ago

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.

Anshul Parashar Over a year ago

@zzlalani according to user need its work because its used ORoperator here

zzlalani · Accepted Answer · 2013-11-25 06:47:11Z

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, 2013 at 6:47

zzlalani

24.6k16 gold badges48 silver badges73 bronze badges

answered Nov 25, 2013 at 6:46

Rakesh Shewale

5079 silver badges23 bronze badges

Comments

Kalpit · Accepted Answer · 2013-11-25 06:49:06Z

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, 2013 at 6:49

answered Nov 25, 2013 at 6:47

Kalpit

4,9365 gold badges27 silver badges45 bronze badges

Comments

zzlalani · Accepted Answer · 2013-11-25 06:53:55Z

0

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

edited Nov 25, 2013 at 6:53

answered Nov 25, 2013 at 6:46

zzlalani

24.6k16 gold badges48 silver badges73 bronze badges

Comments

Tobia Zambon · Accepted Answer · 2013-11-25 07:27:39Z

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, 2013 at 7:27

Tobia Zambon

7,6493 gold badges42 silver badges70 bronze badges

answered Nov 25, 2013 at 7:08

mamun0024

715 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related