CODEWITHSUNDEEP
phpmysql
Xitcod13
Xitcod13

Reputation: 6089

search multiple columns as if it was one column in mysql

I have a table that stores email in 3 diffrent columns name host and domain. I want to join those columns to create one column so i can search by a full email adress. just like 

SELECT * FROM email_eml WHERE (all the columns combined) = $email;

the email is stored without a . and a @ in neither of the columns i would like the final result to contain those.

Upvotes: 1

Views: 133

Answers (4)

RandomSeed
RandomSeed

Reputation: 29809

In order for MySQL to use indexes (you did index these columns, didn't you?), I'd rather split your input e-mail address, and then search on each field separately, that is:

SELECT CONCAT(name, '@', host, '.', domain) AS email
WHERE name = 'user' AND host = 'mysite' AND domain = 'com'

Do you really need to keep the three e-mail address components separated in your database? Otherwise, just store complete e-mails.

Upvotes: 2

jbrtrnd
jbrtrnd

Reputation: 3843

You can use the CONCAT() function.
Then your request will be : 

SELECT * FROM email_eml WHERE CONCAT(name,'@',host,'.',domain) = $email

Upvotes: 0

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

SELECT * FROM email_eml WHERE name+'@'+host+'.'+domain = '$email';

Upvotes: 0

Florian Margaine
Florian Margaine

Reputation: 60835

SELECT * FROM email_eml WHERE CONCAT( name, '@', host, '.', domain ) = $email

See the doc (assuming you're using MySQL) for the CONCAT function: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_concat

However:

  1. Don't use *. It's terrible for performance.
  2. I sure hope your $email variable is escaped and free of SQL injection risks. If it's not, use prepared statements.

Upvotes: 4

Related Questions