Creating an easy search script with MySQL

Question

I want an easy way to search 2 fields in my database. Here is the scenario: form has text box called name which can submit a value to search script.

Search script checks name against first and last name fields in database.

Problem I have come across is if people have enter middle names in the database under the first or last name field.

Example of the data i want to search

fname        lname
--------------------
stephen      jones
john james   doe
john         james doe

I thought could do a search like this:

SELECT
_leads.Firstname,
_leads.Lastname
FROM
_leads
WHERE
_leads.Firstname LIKE '%stephen jones%' OR
_leads.Lastname LIKE  '%stephen jones%'

Well I was hoping it would work.

My other attempt was going to be to explode the string then compare that against the database.

Am I looking too much into this? Is there any easy short bit of code that would help me out?

I think you should count the number of words in the input, then based on number of words (2 or 3) you can write a proper query (include middle name or not) — hungneox
– hungneox, Commented Dec 6, 2011 at 12:02
select * from table WHERE firstname LIKE "john%" AND lastname LIKE "%doe" — try2fly.b4ucry
– try2fly.b4ucry, Commented Dec 6, 2011 at 12:37

Tomáš Fejfar · Accepted Answer · 2011-12-06 12:01:48Z

1

You can search like this:

WHERE CONCAT(first, ' ', last) LIKE '%John%Doe%'

That would find it even when there are middle names :)

answered Dec 6, 2011 at 12:01

Tomáš Fejfar

11.2k8 gold badges58 silver badges85 bronze badges

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

1 Comment

Tomáš Fejfar Over a year ago

obviously t owuld be better to separate first and last name from query to look into first and last name in database :)

CAFxX · Accepted Answer · 2011-12-06 13:19:43Z

1

You should probably set up FULLTEXT indexes on those two columns and use it to retrieve the rows. In MySQL you would then write:

SELECT * FROM _leads WHERE MATCH(Firstname, Lastname) AGAINST ('stephen jones')

answered Dec 6, 2011 at 13:19

CAFxX

30.6k6 gold badges44 silver badges69 bronze badges

Comments

Lee · Accepted Answer · 2011-12-06 12:04:12Z

0

SELECT fields FROM table WHERE CONCAT(field1, ' ', field2) LIKE '%string%'

answered Dec 6, 2011 at 12:04

Lee

10.6k4 gold badges39 silver badges45 bronze badges

Collectives™ on Stack Overflow

Creating an easy search script with MySQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related