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
– Merv, Commented Dec 6, 2016 at 14:08

Jibin Balachandran · Accepted Answer · 2016-12-06 14:32:26Z

2

Try this:

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

edited Dec 6, 2016 at 14:32

answered Dec 6, 2016 at 9:35

Jibin Balachandran

3,4491 gold badge27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Jibin Balachandran Over a year ago

@Ashish, You got any error?? If not can you share the result and expected result.

Jibin Balachandran Over a year ago

@Ashish Second select?? You mean for ds ??

Ashish Over a year ago

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

Jibin Balachandran Over a year ago

@Ashish So you want to use the result of first query to filter the data from second query, right??

Jibin Balachandran Over a year ago

Let us continue this discussion in chat.

|

Collectives™ on Stack Overflow

Compare Value with Comma Separated String SQL PHP

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related