PHP MySQL Search Combined Columns

Question

I had just finished my search functionality for a users system when I found out that it didn't search the way I wanted it to.

If I have a datebase table with 2 columns called 'fname' and 'lname'.

In one row, 'fname' has a value of 'Ashley' and 'lname' has a value of 'Staggs'.

I can search for either 'Ashley' or 'Staggs' and I will get the results correctly, but if I search for 'Ashley Staggs', no results are displayed.

How would I do this properly?

My SELECT query is as follows:

SELECT * FROM `users` WHERE fname LIKE '%" . protect($_GET['s']) . "%' OR lname LIKE '%" . protect($_GET['s']) . "%'

I knew something like this would happen, but this time I can't figure it out.

Thanks, Ashley

@Wiseguy Woops. Thanks for pointing that out. I wrote a function especially for the occasion and forgot to use it. — Ashley Staggs
– Ashley Staggs, Commented Apr 16, 2012 at 21:38
Have you tried using explode() to separate the search crtieria by a space, and then trim() the resulting array? That way, you could divide the criteria into first name and last name. — Pateman
– Pateman, Commented Apr 16, 2012 at 21:40

sp00m · Accepted Answer · 2012-04-17 07:17:11Z

3

'Ashley Staggs' is neither in fname, nor in lname, so your request doesn't return anything. You could try to concatenate your MySQL fields:

SELECT * FROM `users` WHERE fname LIKE '%" . $_GET['s'] . "%' OR lname LIKE '%" . $_GET['s'] . "%' OR CONCAT(fname, ' ', lname) LIKE '%" . $_GET['s'] . "%'

[EDIT] Even better:

SELECT * FROM `users`
WHERE REPLACE(CONCAT(fname, lname, fname), ' ', '')
LIKE '%" . str_replace(' ', '', protect($_GET['s'])) . "%'

edited Apr 17, 2012 at 7:17

answered Apr 16, 2012 at 21:42

sp00m

49k31 gold badges152 silver badges263 bronze badges

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

5 Comments

Ashley Staggs Over a year ago

thanks. It worked. Funny thing is I tried CONCAT on another part of the system and just didn't think to use it.

Ashley Staggs Over a year ago

@Pateman haha. Nope it does not. Then again, i could just do OR CONCAT(lname, ' ', fname) too.

sp00m Over a year ago

Nope, this solution isn't very generic.

sp00m Over a year ago

Hey, I have another idea, try something like SELECT * FROM users WHERE CONCAT(fname, lname, fname) LIKE '%" . str_replace(' ', '', $_GET['s']) . "%'

sp00m Over a year ago

Nope, 'cause the str_replace will remove spaces from your GET var. A bit more generic :)

Donovant · Accepted Answer · 2012-04-16 21:48:04Z

0

SELECT fname_lname FROM ( SELECT CONCAT(fname, ' ', lname) fname_lname FROM users ) users
WHERE fname_lname LIKE '%" . $_GET['s'] . "%'

answered Apr 16, 2012 at 21:48

Donovant

3,1618 gold badges45 silver badges76 bronze badges

Comments

rawb · Accepted Answer · 2012-04-16 21:48:42Z

0

You might try something like this - it'll just split the search string by spaces and search for each word:

$search = explode(' ', $_GET['s']);
$query = 'SELECT * FROM `users` WHERE 0';
foreach ($search as $v)
{
  $v = mysql_real_escape_string($v);
  $query .= " OR (`fname` LIKE '%{$v}%' OR `lname` LIKE '%{$v}%')";
}
// echo $query;

answered Apr 16, 2012 at 21:48

rawb

2,44619 silver badges10 bronze badges

Comments

Pateman · Accepted Answer · 2012-04-16 22:01:10Z

0

Regarding sp00m answer, I have a slightly different approach, but built on the same concept.

$search = preg_replace ( "/\s\s+/" , " " , $_GET['s']);

And then use this query:

"SELECT * FROM `users` WHERE CONCAT(fname, ' ', lname) LIKE '%" . $search . "%' OR CONCAT(lname, ' ', fname) LIKE '%" . $search . "%'"

EDIT

Just had an idea you could use. Basically, you could create two additional fields in the table - fname_lname and lname_fname , use the regex I mentioned before to get rid of unnecessary spaces, use explode() to check the word count. If you have two words, then you can use these two new fields, giving you only two conditions in the query. When you have only one word, you still have two conditions in the query.

edited Apr 16, 2012 at 22:01

answered Apr 16, 2012 at 21:50

Pateman

2,7403 gold badges32 silver badges47 bronze badges

Comments

Ravinder Payal · Accepted Answer · 2013-11-19 14:04:33Z

0

hey i want to sugest a stronger more strong search but it required MyISAM table code for this is

$q="you search string";
$searchArray = explode(" ", $q);
$query="SELECT * FROM cmusers WHERE  MATCH (`firstname`, `lastname`, `email`) AGAINST ('";
$i=0;
foreach ($searchArray as $word) {
$query .= "+".$word."* ";
}
$query .= "' IN BOOLEAN MODE)";
$result=mysql_query($query) or die("Error founded:".mysql_error()."there is problem existing we feels a very great sorry");
$finded=mysql_num_rows($result);

working of this can be seen at http://www.funnenjoy.com

answered Nov 19, 2013 at 14:04

Ravinder Payal

3,0291 gold badge34 silver badges43 bronze badges

Comments

TheCrazyProfessor · Accepted Answer · 2016-09-22 05:39:33Z

0

I will just do

SELECT * FROM users WHERE CONCAT(firstname, ' ', lastname) LIKE '%{$search}%'

answered Sep 22, 2016 at 5:39

TheCrazyProfessor

9691 gold badge16 silver badges31 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Search Combined Columns

6 Answers 6

5 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related