How to write Dynamic sql query using loops

Question

.

I'm trying to write a query to fetch the data from the database, but am not getting. I want to fetch the title of ConstituencyId 3. For 3rd constituency, I have to display 4 different titles. if a user selects constituency 2 and 3 I have to display titles of 2nd and 3rd Constituency.

SELECT N1.Id, N1.Title From NewsContent N1
Where N1.Id > 0 and  
(N1.ConstituencyId like ('%2%') OR N1.ConstituencyId Like ('%3') OR N1.ConstituencyId like ('%4%'));

This is the equery I have to write dynamically because I don't know what the user selects. am not getting in dynamically. Please help me out on this.

Thanks

Tim Biegeleisen · Accepted Answer · 2018-11-15 10:37:22Z

1

You may use FIND_IN_SET here, e.g.

SELECT N1.Id, N1.Title
FROM NewsContent N1
WHERE
    N1.Id > 0 AND
    FIND_IN_SET('2', N1.ConstituencyId) > 0 AND
    FIND_IN_SET('3', N1.ConstituencyId) > 0;

But note that storing CSV in the ConstituencyId column means that your table is not completely normalized. This is not ideal, for the above reason that it makes querying difficult, and updating even more difficult. Fortunately, MySQL has a FIND_IN_SET function which can help, but we should avoid relying on it.

answered Nov 15, 2018 at 10:37

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

8 Comments

Sasi Kumar Over a year ago

if user selects more than 5 options how can i write

Tim Biegeleisen Over a year ago

You would need five separate calls to FIND_IN_SET, unless you can tell me that you store your IDs in ascending/descending order in the ConstituencyId column. In that case, I could give you another option.

Sasi Kumar Over a year ago

if user selects more than 100 options, How would i know, that how many options are selected, So am expecting the loop. if we write the loop , it will run automatically..

Tim Biegeleisen Over a year ago

If you really have this requirement, then you should take my advice and stop storing CSV data in your tables.

Sasi Kumar Over a year ago

what is your advice?

|

Collectives™ on Stack Overflow

How to write Dynamic sql query using loops

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related