'Mysql Search value used to find row

Is it possible for Mysql to return the search value that it used to find the row?

For example I have a database like this:

ID | FLIPPERID | PHONENUMBERS                      | POSTERIDS  
1   10001        7003158974,8769873453,9085699812   6477741332,34234324234,5734345,34234

I do a query like this. But with hundreds of values:

SELECT * FROM `flipperaccounts` 
WHERE `posterids` = 3126764 
   OR `posterids` = 65139757 
   OR `phonenumbers` = 6477741332 
   OR `posterids` = 72345341;

Now I'm wondering if there's a way to know which value triggered the row to showup?

phpmysql


Solution 1:[1]

You can do a direct comparison using a case/when/then statement. I've used CONCAT_WS here in the event that there are multiple columns matched, so you can explode(',', $row['matched_column']) for your convenience.

SELECT 
    ID,FLIPPERID,PHONENUMBERS,POSTERIDS, CONCAT_WS(', ',
        CASE WHEN posterids = 3126764 THEN 'posterids_1',
        CASE WHEN posterids = 65139757 THEN 'posterids_2',
        CASE WHEN phonenumbers = 6477741332 THEN 'phonenumbers',
        CASE WHEN posterids = 72345341 THEN 'posterids_3' ) AS matched_column
    FROM flipperaccounts

Then when you get the result back your table will be like:

| ID | FLIPPERID | PHONENUMBERS | POSTERIDS | MATCHED_COLUMN |
  "1"   "10001"    "700315894.." "647774..",  "posterids_2"

Then you can access it within your loop statement as an index on the row.

if(!empty($row['matched_column'])) {
    echo $row['matched_column'];
}

Hopefully this helps.

Solution 2:[2]

I do not know of any way to tell what part of a complex WHERE clause caused a SELECTion.

Note that OR is associative (rather than commutative), so more than one of your clauses may cause a hit, and so the order is important to your answer — problem is, the query optimizer knows that OR is associative, and so it may arbitrarily change the order of the OR clauses! For example, if one column has a shorter index, the query optimizer might choose to do that first, or it may choose to do PRIMARY or UNIQUE columns first.

So it seems to me that your question in non-deterministic, without returning an array of OR clauses that "hit."

You could do this by using "SELECT COUNT(*)" for each of the OR clauses, which is fairly non-intensive, if the columns are indexed.

Solution 3:[3]

I went with RiggsFolly idea of making a child table. Thanks for the help.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ohgodwhy
Solution 2 Jan Steinman
Solution 3 Brandon

Related Questions