Compare Value with Comma Separated String SQL PHP

Question

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...

table:

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

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

Query i am using is:

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

Output:

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...

Second select box query:

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... ?

On a side note, please do use PDO or at least sanitize your input before using it within a query. Straight inserting $_POST variables has long been bad practice. — Merv, Dec 06 '16 at 14:08

Jibin Balachandran · Accepted Answer · 2016-12-06T14:32:26.400

2

Try this:

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

edited Dec 06 '16 at 14:32

answered Dec 06 '16 at 09:35

Jibin Balachandran

3,381
1
24
38

@Ashish, You got any error?? If not can you share the result and expected result. – Jibin Balachandran Dec 06 '16 at 09:41
@Ashish Second select?? You mean for `ds` ?? – Jibin Balachandran Dec 06 '16 at 09:44
nops sir...First select fetches cat and based on that selection second select fetches loc...on using this query after selecting a cat my second select don't show any loc....means whatever i select from cat....loc remains empty... – Ashish Dec 06 '16 at 09:47
@Ashish So you want to use the result of first query to filter the data from second query, right?? – Jibin Balachandran Dec 06 '16 at 09:53
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/129888/discussion-between-jibin-balachandran-and-ashish). – Jibin Balachandran Dec 06 '16 at 10:06
@Ashish, This code looks fine. Please check if you have extra spaces in your data – David דודו Markovitz Dec 06 '16 at 10:26
@DuduMarkovitz yeah i have spaces between cat value and loc value...for example cat value like "s t, ds" and loc value like "no w" – Ashish Dec 06 '16 at 13:54
@Ashish, O.k. so you should probably fix the data. `replace (cat,' ','')` – David דודו Markovitz Dec 06 '16 at 13:56
@DuduMarkovitz plz check edited question i have edited content with my original content can you plz now create a query for me...Thanks – Ashish Dec 06 '16 at 14:06
@JibinBalachandran plz check edited question i have edited content with my original content can you plz now create a query for me...Thanks – Ashish Dec 06 '16 at 14:08
@Ashish I have updated my answer, do check and let me know if you are facing any issue. – Jibin Balachandran Dec 06 '16 at 14:33

Compare Value with Comma Separated String SQL PHP

1 Answers1

Linked