cparekh Posted March 25, 2009 Share Posted March 25, 2009 Hi, I have a table where I'm storing events and each event can belong to multiple categories so I'm storing the category ids in a field called 'catid' as a comma-separated string e.g. '1,3' where each number corresponds to a category id. Now, I know what I'm about to ask can be achieved in a different, better way however I'm working with someone else's code and a change would require re-coding parts of the whole application. I'm hoping I can do this without having to make those changes. So, what I'd like to do is get all the events for a particular category and limit the rows returned to, let's say, 10. What's happening currently is that the following query is run and then using explode and a foreach on the result-set I'm pulling out the correct events for the category I want. However using this method I can't limit the number of events to show as on the query I have to get all the events in the database. I'd like to know if there is a way to write the query and apply the LIMIT on the query so that only 10 rows are ever pulled out, all correct for a particular category id. The current query: SELECT * created FROM events ORDER BY date DESC Pseudo query of what I hope is possible: SELECT * created FROM events WHERE (subquery or conditioning) ORDER BY date DESC LIMIT 10 where the subquery would play the role of explode, split the string on a comma and check for a particular category id (e.g. 3) and only return the 10 rows for any records that have a '3' in the catid column. I really hope that makes sense!! Any help or guidance is greatly appreciated. Thanks, C. MySQL version: 4.1.22 Link to comment https://forums.phpfreaks.com/topic/151050-solved-query-containing-a-sub-query-to-split-a-string-on-commas-and-run-a-comparison/ Share on other sites More sharing options...
fenway Posted April 1, 2009 Share Posted April 1, 2009 You can use FIND_IN_SET() to find the "3"... but I'm not sure what else you mean. Link to comment https://forums.phpfreaks.com/topic/151050-solved-query-containing-a-sub-query-to-split-a-string-on-commas-and-run-a-comparison/#findComment-798545 Share on other sites More sharing options...
cparekh Posted May 5, 2009 Author Share Posted May 5, 2009 Hi fenway, yes, this is exactly what I needed. Many thanks, C. Link to comment https://forums.phpfreaks.com/topic/151050-solved-query-containing-a-sub-query-to-split-a-string-on-commas-and-run-a-comparison/#findComment-826589 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.