PHP/MySQL search for result in string using variable

Question

I have a query that just isn't providing results:

$name = "John Smith";

$query = "SELECT * FROM my_table WHERE lname LIKE '%$name%' AND fname LIKE '%$name%'";

lname in the database would be listed as Smith fname in the database would POSSIBLY be listed as John S.

Some entries have a middle initial attached to their name, some don't.

Also, names are pulled in as one string on the page where I am querying this information, not first name and last name strings. Limitation of the existing code.

So I'm trying to search for the last name in the string $name as well as the first name. I assume I'm doing it wrong. Is this possible? Or is there another way to do this?

You're searching for the string John Smith in both the lname and fname fields - that's not going to find anything, as the contents are split over both fields. — andrewsi
– andrewsi, Commented Apr 1, 2013 at 19:08
$query = "SELECT * FROM my_table WHERE lname LIKE '%$name%' OR fname LIKE '%$name%'"; — dikirill
– dikirill, Commented Apr 1, 2013 at 19:09
What if I have Dan Smith and John Walters though? I need to make sure that the string John Smith, either first or last name appears in both lname and fname. — Dave Rottino
– Dave Rottino, Commented Apr 1, 2013 at 19:14

sgeddes · Accepted Answer · 2013-04-01 19:32:12Z

1

How about this (if I'm understanding you correctly):

SELECT *
FROM my_table
WHERE '$name' like concat(fname,' %')
  AND '$name' like concat('% ',lname)

SQL Fiddle Demo

To search the fname field, it concats a ' %' after the fullname being searched. And to search the lname field, it concats a '% ' before the fullname.

EDIT: A slight variation of Mario's good comment that uses REGEXP:

SELECT *, CONCAT_WS(' ', fname, lname)
FROM my_table
WHERE  CONCAT_WS(' ', fname, lname) REGEXP REPLACE('$name',' ', '.*')

More Fiddle

Since you mention you cannot break up the parameter (why?), this uses replace to replace the space accordingly.

edited Apr 1, 2013 at 19:32

answered Apr 1, 2013 at 19:22

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

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

6 Comments

Mario Mueller Over a year ago

I would modify this a bit: select * from foo2 where "john smith" in (fname, lname) or CONCAT_WS(" ", fname, lname) REGEXP "john.*smith";

sgeddes Over a year ago

@MarioMueller -- I like the REGEXP option -- I'll edit the post to show. Thanks!

sgeddes Over a year ago

@MarioMueller -- actually, that would require altering the input of $name and I didn't think OP could do that. If OP can alter input, then I like this option too.

Mario Mueller Over a year ago

Yes, you're right. This might be no acceptable due the presented immutability of the original value, but I might be worth a try ;)

sgeddes Over a year ago

@MarioMueller -- I updated with a version that should work using SQL to replace the input. Up to OP at this point :) Thanks!

|

Ankit Sharma · Accepted Answer · 2013-04-01 19:25:56Z

0

i have the solution for it.. you can try like this

$name = "John Smith";

$query = "SELECT * FROM my_table WHERE fname LIKE substring_index(".$name.",' ',1) AND fname LIKE substring_index(".$name.",' ',-1)";

answered Apr 1, 2013 at 19:25

Ankit Sharma

1431 silver badge10 bronze badges

Comments

Santi · Accepted Answer · 2013-04-01 19:36:19Z

0

I don't understand what is exactly the problem but the next query could help you:

SELECT * FROM my_table WHERE '$name' LIKE CONCAT('%', lname ,'%') and '$name' LIKE         CONCAT('%',fname ,'%')

If you need to check if lname is contained in $name the query you show is incorrectly and you are checking the opposite.

edited Apr 1, 2013 at 19:36

answered Apr 1, 2013 at 19:21

Santi

162 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL search for result in string using variable

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related