如何使用循环编写动态sql查询

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

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