-1

What I have with me?

A string with comma separated : $str_states = "1,2";

I have a table with the following: 

id event_name  states
1  ABC         1,4,5
2  PQR         1,2,3
3  XYZ         3,4,5

What I want:

id  event_name states
1   ABC        1
2   PQR        1,2

What I tried with a query:

SELECT id,event_name FROM events_table WHERE
FIND_IN_SET('1,2', states);

SELECT id,event_name FROM events_table WHERE
states IN ('51,58');
Vinil Lakkavatri
  • 11
  • 2
  • inLocationId IN ('51,58')??? – saravanatn Jan 09 '19 at 11:05
  • 2
    This is an [X-Y problem](http://xyproblem.info/). The real issue is that your database is de-normalised. If you ever find yourself storing more than one value inside a single field, stop and re-consider your database design. SQL is not designed to be used like this. Here you should have an extra table which contains Event ID and State ID (as a compound primary key), and where each column is a foreign key back to events and states tables respectively. This structure accurately describes a many-to-many relationship such as this. Once you do that, your query will become much simpler. – ADyson Jan 09 '19 at 11:05
  • P.S. If you didn't understand this, you need to study entity-relationship design and database normalisation in more detail before continuing to work with relational databases. – ADyson Jan 09 '19 at 11:06

1 Answers1

0

You can't really use FIND_IN_SET the way you want here. FIND_IN_SET searches for a single value against a CSV string. So, you would have to use OR here:

SELECT id, event_name
FROM events_table
WHERE FIND_IN_SET('1', inLocationId) > 0 OR FIND_IN_SET('2', inLocationId) > 0;

If your starting point for the input is 1,2, then you will have to tease apart the individual values in your app layer.

By the way, storing CSV unnormalized data in your SQL tables is bad practice. You would be better off to fix your design than to use my answer.

As a bonus, here is a more terse way to write your query using REGEXP:

SELECT id, event_name
FROM events_table
WHERE inLocationid REGEXP '[[:<:]](1|2)[[:>:]]';

But again, please fix your data model.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360