CODEWITHSUNDEEP
phpmysql
Storm Spirit
Storm Spirit

Reputation: 1520

How to search on lastname and firstname with one variable given

I have first_name and last_name

so this is my current query on PHP

SELECT * FROM users WHERE CONCAT_WS(first_name, " ", last_name)') LIKE $name.'%'

my sample DB:

firstname   |   lastname
-------------------------
Joe         |   Smith
Bien        |   Tibor
Brocs       |   Mar
Joe         |   Gold

this is my Test Result

Test 1: name = Joe

firstname   |   lastname
-------------------------
Joe         |   Smith
Joe         |   Gold

Test 2: name = Smith

firstname   |   lastname
-------------------------
"No Results Found"

Upvotes: 1

Views: 100

Answers (5)

CD001
CD001

Reputation: 8472

First create a FULLTEXT index on your table:

ALTER TABLE `tbl`
ADD FULLTEXT INDEX `full_name` (`firstname`, `lastname`)

Then perform a natural language search.

SELECT users.*, 
MATCH(users.firstname, users.lastname)
AGAINST (? IN NATURAL LANGUAGE MODE) AS relevance
FROM users

Use a bound parameter to replace that ? with your name string.

Since this search will be performed against an index it will be significantly faster, on large datasets, than wildcard string matching.

Upvotes: 0

Andrew
Andrew

Reputation: 1989

If you want to exclude results from searching "oe" then you could just use an OR instead:

SELECT * FROM users WHERE firstname = ' . $name .' OR lastname = ' . $name

Upvotes: 0

Jamie Bicknell
Jamie Bicknell

Reputation: 2326

Use the wildcard % on either side of the $name variable.

$sql = "SELECT * FROM `users` WHERE CONCAT(`first_name`, ' ', `last_name`) LIKE '%".$name."%'";

Upvotes: 1

Passionate Coder
Passionate Coder

Reputation: 7294

Hi Your CONCAT_WS syntax is wrong

It should be

CONCAT_WS (separator, string1, string2,…)

Upvotes: 0

apriede
apriede

Reputation: 106

Add the % widlcard on both ends of $name, so your query looks like '%Smith%'

Needless to say you need to consider proper sql sanitization so consider using something like PDO

Upvotes: 0

Related Questions