[SOLVED] Query containing a sub-query to split a string on commas and run a comparison

[cparekh]

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

[fenway]

You can use FIND_IN_SET() to find the "3"... but I'm not sure what else you mean.

[cparekh]

Hi fenway,

 

yes, this is exactly what I needed.

 

Many thanks,

C.