Return the name of the MySQL column where a select match was

user_15181449 0
Viewed 209

I have this MySQL query:

SELECT *
  FROM profiles 
 WHERE phone = :query 
    OR email = :query 
    OR facebook = :query 
    OR instagram = :query

I need to know what columns the match occurred in. So if the match occurred in.

How do I get that data in the MySQL query?

Edit: The optimal result would be to get all names of matched columns in the returned row.

Edit 2: If I have this table:

+----+---------+----------------+----------------+----------+-----------+
| ID |  Name   |     email      |     phone      | facebook | instagram |
+----+---------+----------------+----------------+----------+-----------+
|  0 | Jill    | jill@aol.com   | (123)123-1245  | fbjill   | igjill    |
|  1 | Jack    | jack@aol.com   | (123)111-1111  | fbjack   | igjack    |
|  2 | Bob     | bob@aol.com    | (123)222-2222  | fbbob    | igbob     |
|  3 | jMarie  | jill@aol.com   | (123)123-1245  | none     | none      |
+----+---------+----------------+----------------+----------+-----------+

and I query this:

SELECT *
      FROM profiles 
     WHERE phone = fbjill 
        OR email = fbjill 
        OR facebook = fbjill
        OR instagram = fbjill

I need it to return the all the data of the row but with an additional columns stating the name of the columns it came from.

mysql
3 Answers

You could use CONCAT_WS here:

SELECT *,
    CONCAT_WS(',', IF(phone = :query, 'phone', NULL),
                   IF(email = :query, 'email', NULL),
                   IF(facebook = :query, 'facebook', NULL),
                   IF(instagram = :query, 'instagram', NULL)) AS matching_cols
FROM profiles
WHERE -- your conditions here

This would return a CSV string, consisting of up to 4 columns, which matched the incoming :query string parameter.

user_18632290

You can SELECT your conditions.

SELECT 
    *,
    phone = :query as phone_match,
    email = :query as email_match,,
    facebook = :query as facebook_match,
    instagram = :query as instagram_match
FROM profiles 
WHERE 
    phone = :query 
    OR email = :query 
    OR facebook = :query 
    OR instagram = :query

That will return 1 for each match, 0 for non-matches.

user_21312120

Use

SELECT *,
       MAKE_SET (       (phone = :query) 
                  + 2 * (email = :query) 
                  + 4 * (facebook = :query) 
                  + 8 * (instagram = :query), 'phone', 
                                              'email', 
                                              'facebook', 
                                              'instagram') from_where
FROM profiles 
HAVING from_where != ''

or the same with LIKE operator (see fiddle).

user_101387340
Related