0

I have a database table as below.

Promotion_Table

id(INT), promotion_name(VARCHAR),......, bungalow_ids(VARCHAR)

We can add a promotion for a bungalow(23). So a row is added with the bungalow id as below.

1, My Promotion, ........, 23

But if I single a promotion is added for a multiple bungalows(23,42) all ids are saved in the bungalow_ids column as below.

2, My Promotion 2, ........, 23 | 42

If a user search for promotion which are for specific bungalow(23) All promotions for the bungalow should be shown in the result.

I have a query as below.

SELECT * FROM Promotion_Table WHERE bungalow_ids = '23'

It only gets 1 rows. But actually 2nd row should be shown too since there is a offer. I can nt use LIKE since it gets wrong records.

Given that I have already referred below links but I have no idea how to use them in the query.

Can you split/explode a field in a MySQL query?

Equivalent of explode() to work with strings in MySQL

How can I fix this? How can I explode the column data and use it in the query ?

Community
  • 1
  • 1
Techie
  • 44,706
  • 42
  • 157
  • 243

3 Answers3

0

you need to reformat your DB schema. you need to construct 2 tables one for promotions and one for bangalows. like below:

promotions: Promotion_id(int), Promotion_desc

bangalows: Bangalow_id(int), Promotion_id(int)

tables example:

promotion :

1        myPromotion     
2        secondPromotion

bangalows:

1     1    
2     2   
3     1   
4     1

once you create above two tables, the following query will work and returns 1,3,4:

SELECT Bangalow_id FROM Promotion_Table WHERE bungalow_id = '1'
Community
  • 1
  • 1
uriee
  • 31
  • 5
0

The previous Answer is the right decision but if you insist in your model. Probably what you want to do is:

SELECT *
  FROM Promotion_Table
 WHERE bungalow_ids = '23'
    OR bungalow_ids LIKE '23,*'
    OR bungalow_ids LIKE '*,23'
    OR bungalow_ids LIKE '*,23,*'

this assuming the numbers are separated by ",". But this is the wrong way, make the changes to the DB as stated in the previous answer.

Pedro.The.Kid
  • 1,968
  • 1
  • 14
  • 18
0

Use , to separate the string and try this query

select * from promotion_table where FIND_IN_SET("23",bungalow_ids)

http://sqlfiddle.com/#!2/7bbcb/1

VeNoMiS
  • 330
  • 5
  • 15