搜索任何表字段中是否存在变量并获取表字段名称

Question

Using DOM on a webpage, I create a variable like this:

$adres = "Bruinbergstraat 2,Bruinberg,8730 Oedelem";

In a MySql table I have a lot of records; each record has at least 1 address field, other records have more address fields (max. 7).

When I use this code to find if the variable exists in any of the address fields:

$command = "Select * from alleclubs where (adres1 like '%" . $adres ."%' or adres2 LIKE '%" . $adres ."%' or adres3 LIKE '%" . $adres ."%' or adres4 LIKE '%" . $adres ."%' or adres5 LIKE '%" . $adres ."%' or adres6 LIKE '%" . $adres ."%' or adres7 LIKE '%" . $adres ."%')";

the result is 0 records found.

How can I solve this so I can get the table field name of the column the variable is in?

Answer 1

To get the table field name of the column the variable is matched with, you can do something like below-

"select *
       from (select 
                concat_ws(',',
                    if( adres1 LIKE '%$adres%','adres1',NULL),
                    if( adres2 LIKE '%$adres%','adres2',NULL),
                    if( adres3 LIKE '%$adres%','adres3',NULL),
                    if( adres4 LIKE '%$adres%','adres4',NULL),
                    if( adres5 LIKE '%$adres%','adres5',NULL),
                    if( adres6 LIKE '%$adres%','adres6',NULL),
                    if( adres7 LIKE '%$adres%','adres7',NULL)
                ) as 'matched_column_names',stamnummer
            from alleclubs ) derived_table
where matched_column_names != '' "

Then, when iterating each row through PHP, you can just use explode() over , to get each column name.

Note: You can also use CASE in MySQL but CASE stops when there is a match. The above query will give you all column names that matched with $adres for each single row.

UPDATE:

You can directly check this in your phpMyAdmin with your sample Bruinbergstraat 2,Bruinberg,8730 Oedelem as below-

select *
       from (select 
                concat_ws(',',
                    if( adres1 LIKE '%Bruinbergstraat 2,Bruinberg,8730 Oedelem%','adres1',NULL),
                    if( adres2 LIKE '%Bruinbergstraat 2,Bruinberg,8730 Oedelem%','adres2',NULL),
                    if( adres3 LIKE '%Bruinbergstraat 2,Bruinberg,8730 Oedelem%','adres3',NULL),
                    if( adres4 LIKE '%Bruinbergstraat 2,Bruinberg,8730 Oedelem%','adres4',NULL),
                    if( adres5 LIKE '%Bruinbergstraat 2,Bruinberg,8730 Oedelem%','adres5',NULL),
                    if( adres6 LIKE '%Bruinbergstraat 2,Bruinberg,8730 Oedelem%','adres6',NULL),
                    if( adres7 LIKE '%Bruinbergstraat 2,Bruinberg,8730 Oedelem%','adres7',NULL)
                ) as 'matched_column_names',stamnummer
            from alleclubs ) derived_table
where matched_column_names != '';