PHP-MySQL search Query to find names starts with letter entered in the text box

phpservices
phpservices used Ask the Experts™
on
Hello.

I need to write search Query that returns the names that matches with the typed characters.. I have stored both first and last name in same column.  I need to search the typed characters with both First and Last Name (" All stored in Name field, separated by space )

Example:

name

Robert Mathew
Albert Mathew
Albert
Andrew Simon
Akhil Peter
Philp John

SELECT name FROM table WHERE name LIKE '$search%' OR name LIKE  '% $search%'

Can I write this ?  But this doesn't work.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rajkumar GsSoftware Engineer

Commented:
Try this query

SELECT name FROM table WHERE name LIKE '%search%'

--Raj
Rajkumar GsSoftware Engineer

Commented:
If you want to search for 'Andrew Simon'.

FirstName as 'Andrew' OR LastName as 'Simon'

SELECT name FROM table WHERE name LIKE '%Andrew%' OR  name LIKE '%Simon%'

--Raj

Most Valuable Expert 2011
Top Expert 2016
Commented:
I think you may need something like this - note the spaces in the query to allow checking both first and last names.

SELECT name FROM table WHERE name LIKE '$c%' // CATCHES FIRST NAME MATCHES
OR name LIKE ' $c% // CATCHES LAST NAME MATCHES
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

@phpservices, @Ray
B-) Remember that LIKE is not a Regexp, therefore the $ just means the $ character.
Therefore Ray's suggestion is
SELECT name FROM table WHERE name LIKE 'c%' // CATCHES FIRST NAME MATCHES
OR name LIKE '% c%' // CATCHES LAST NAME MATCHES

And the initial suggestion should be rewritten as
SELECT name FROM table WHERE name LIKE 'search%' OR name LIKE  '% search%'

(which indeed happend to be tyhe same)

I would personally test the performance impact of
SELECT name FROM table WHERE concat(' ', name) LIKE  '% $search%'

Note that none of the queries above returns really the "name", eg searching for 'John' would find OK in 'John Albert' as well as 'Bob Johnny', 'Albert DeJohn' and 'Albert de John'
If you are searching
Most Valuable Expert 2011
Top Expert 2016

Commented:
In my example, I probably should have added some more PHP stuff - my intention of using the $c was to illustrate using a PHP variable in the construction of the MySQL query string.  More like this:
$c   = 'J'; // PROBABLY FROM A QUERY STRING LIKE /GET?c=J
$sql = "SELECT name FROM table WHERE name LIKE '$c%' OR name LIKE ' $c%';

Open in new window

B-( Stupid me, this $ was a good hint!
B-)
Most Valuable Expert 2011
Top Expert 2016

Commented:
Yes this $ but not $this

;-)

~Ray
Try doing it by
         SELECT column FROM table WHERE column RLIKE '^(search.*)|(.*[ ]+search.*)$'
Keep that space in square brackets as it is. You may find this one more compact and precise but I would be curious to know the performance on large data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial