mysql select in multiple fields

Question

In my search I used autosuggest. The problem now is I have to search the value in multiple fields such as firstname,middlename,lastname,caption etc. How to identify that the match string will belong on specific field name.

let say i have table

 firstname     middlename     lastname      caption
 james         kelly          tow           handsome
 timy          john           fung          fearless
 hanes         bing           ken           great

in my input field once I typed "j" I should select and ouput

james
john
great

Currently I just output the firstname so instead of the above result I came out like below which is not good.

james
timy
hanes

It is possible? Any help would greatly appreciated.

Max Shawabkeh · Accepted Answer · 2010-02-05 08:34:20Z

1

You could do something like:

SELECT IF(first LIKE 'j%',
          first,
          IF(middle LIKE 'j%',
             middle,
             IF(last LIKE 'j%',
                last,
                ''))) AS name
FROM mytable
HAVING name <> '';

answered Feb 5, 2010 at 8:34

Max Shawabkeh

38.7k10 gold badges85 silver badges92 bronze badges

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

1 Comment

tirso Over a year ago

hi max, your solution is so nice and works for me. I tried before the fulltext search and using OR but the problem is I don't know in which fieldname belong. Thanks

Håvard S · Accepted Answer · 2010-02-05 08:02:58Z

0

Just OR them together like so:

SELECT * FROM yourTable WHERE firstname LIKE '%j%' OR lastname LIKE '%j%' -- ...

answered Feb 5, 2010 at 8:02

Håvard S

24k9 gold badges64 silver badges73 bronze badges

Comments

Bjoern · Accepted Answer · 2010-02-05 08:03:21Z

-1

This shouts for a fulltext search.

Check out the mysql entries about that!

answered Feb 5, 2010 at 8:03

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

Collectives™ on Stack Overflow

mysql select in multiple fields

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