使用逗号分隔的字符串SQL PHP比较值 [英] Compare Value with Comma Separated String SQL PHP

问题描述

我在网页上使用两层链式选择框来过滤数据...我在查询第二个选择值时遇到问题...

I am using two-tier chained select boxes on my webpage to filter data...I am having problem with my query for the second select values...

表:

id     name      cat                        loc

1      ABC       resort                     mall road
2      BCD       banquet hall               mall road
3      CDE       farm house, banquet hall   pakhowal road
4      DEF       hotel                      ferozpur road
5      FEZ       hotel                      fountain chowk
6      ZEX       resort                     mall road

我有两个选择框，其中第一个用于显示DISTINCT猫值...对我来说效果很好...

I have two select boxes in which first one is for DISTINCT cat values...which is working perfectly for me...

我正在使用的查询是:

select distinct cat from 
(select  trim(substring_index(substring_index (concat(cat,',,'),',',n),',',-1)) 
as cat from table t 
cross join (select 1 as n union all select 2 union all select 3) r) t 
where  cat <> '' ORDER BY cat ASC

输出:

cat

banquet hall
farm house
hotel
resort

现在，我要进行查询，以选择所有与我们在第一个框中选择的猫值相同的区域，例如，如果我从第一个框中选择度假村"，则在第二个框中选择商城路" ...以防万一我选择了宴会厅"，然后应该选择购物中心路"和百老汇路"等等.

Now I want to make an query which selects all those areas that have same cat value we select in first box like if i select "resort" from first select it chose "mall road" for second box...and in case I chose "banquet hall" then it should chose "mall road" and "pakhowal road" and so on.

我也为此使用了查询，但是它对我来说无法正常工作...

I have used a query for this also but it is not working properly for me...

第二个选择框查询:

SELECT DISTINCT loc 
from table 
WHERE cat = '$_POST[cat]' AND loc IS NOT NULL

它只为度假胜地"和旅馆"猫提供正确的输出....如果我选​​择农舍"或宴会厅"，它不会获取百老汇路" ...

It gives right output for "resort" and "hotel" cat only....if I chose "farm house" or "banquet hall" it doesn't fetch "pakhowal road"...

如何更改查询以实现此目标...?

How I can change my query to achieve this... ?

推荐答案

尝试一下:

SELECT DISTINCT loc
FROM [YourTable]
WHERE INSTR(CONCAT(', ',cat,' ,'),CONCAT(', ','$_POST[cat]',' ,'))>0 AND loc IS NOT NULL 

这篇关于使用逗号分隔的字符串SQL PHP比较值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！