MYSQL, how to search keyword in multiple columns?

Question

How can I search a keyword in multiple columns in mysql syntax?

Now I have a variable $phone, that store the form input, and use the syntax to search in a single column:

SELECT * FROM db.table WHERE 'phone1' = '".$phone."';

How I make that search through columns 'phone1', 'phone2', 'phone3' and 'phone4'?

Thanks

I trust that you have sanitized $phone accordingly, too?

BenM
– BenM

2013-01-22 18:16:06 +00:00
Commented Jan 22, 2013 at 18:16 — BenM
– BenM, Commented Jan 22, 2013 at 18:16

John Conde · Accepted Answer · 2013-01-22 18:15:10Z

5

Use OR in your WHERE clause

SELECT * 
FROM db.table 
WHERE 'phone1' = '".$phone."'
OR 'phone2' = '".$phone."'
OR 'phone3' = '".$phone."'
OR 'phone4' = '".$phone."'

answered Jan 22, 2013 at 18:15

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

8 Comments

lc. Over a year ago

But @WilliamdeCastro beware if $phone contains the string "' OR 1=1 --"

John Conde Over a year ago

Yes, they don't show enough code to say whether or not they were sanitizing and/or escaping their data so I didn't elaborate on it.

William de Castro Over a year ago

@lc $pohne will ever have a simple numeric string

BenM Over a year ago

@WilliamdeCastro How do you guarantee that?

BenM Over a year ago

I'm sorry, you simply cannot rely on JS alone to sanitize your DB strings. And, as for running on intranet, never underestimate your users...

|

bonCodigo · Accepted Answer · 2013-01-22 18:36:59Z

1

Why not try in: =)

SELECT * 
FROM db.table 
WHERE '".$phone."' in (`phone1`, `phone3`, `phone3`, `phone4`)
;

edited Jan 22, 2013 at 18:36

answered Jan 22, 2013 at 18:22

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

9 Comments

bonCodigo Over a year ago

Not sure how logical it is in terms of performance.. tested table:(sqlfiddle.com/#!2/615bf/1) Please leave a comment for the downvotes as or is same as in and explain plan shows the same

BenM Over a year ago

It is, but you should use backflicks instead of single quotes for field names, and you have a mismatch in your quotes anyway.

BenM Over a year ago

Your answer is incorrect and will not work in its current form, hence the downvote.

BenM Over a year ago

That's all you needed to do. There was no need to get childish about it now, was there? ;-)

bonCodigo Over a year ago

Why not then downvote the above answer for the same? if you are downvoting it has to be consistent on your reasoning for all. Isn't it?

|

Collectives™ on Stack Overflow

MYSQL, how to search keyword in multiple columns?

2 Answers 2

8 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related