Jump to content

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


cparekh

Recommended Posts

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

  • 1 month later...

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.