Help with MySQL query

[eluser]Boris Strahija[/eluser]
I'm trying to achieve something similar to Wordpress, where you can get multiple articles by category. The problem I ran into is if I want to get articles that are in more than one category.
Example:

Code:

$cms->get_articles_by_category(array(18,7,21))

It's actually pretty easy to get articles when I need only 1 category, but things get tricky with multiple categories. So in my example I need to get all articles that are assigned to all 3 categories(18, 7, 21). WHERE IN (18,7,21) returns articles that are just one of those categories so that's not an option. My Database setup is something like this:

Code:

bd_entries
    id
    title
    status
    ...

bd_categories
    id
    title
    ...

bd_entry_category
    entry_id
    category_id

I did find a partial sollution to the problem with the following query:

Code:

SELECT
     bd_entries.id AS entry_id
    ,bd_entries.title
    ,bd_entry_category.category_id
    ,GROUP_CONCAT(bd_entry_category.category_id) AS entry_categories
    ,COUNT(bd_entry_category.entry_id) AS category_count
FROM bd_entries
INNER JOIN bd_entry_category ON bd_entry_category.entry_id = bd_entries.id
WHERE bd_entry_category.category_id IN (7, 18, 21)
AND bd_entries.status = 'published'
GROUP BY bd_entry_category.entry_id
HAVING entry_categories = '7,18,21'

The problem is in the following part:

Code:

HAVING entry_categories = '7,18,21'

Because sometimes the GROUP_CONCAT function returns the value in a different order eg. '18,7,21'.

So is there any way I can use the HAVING function differently, maybe someway to create arrays in MySQL and then compare it?
I hope I asked the question clear enough

[eluser]danmontgomery[/eluser]
Did you try:

Code:

GROUP_CONCAT(bd_entry_category.category_id ORDER BY category_id ASC) AS entry_categories

?

[eluser]Boris Strahija[/eluser]
Awsome thanks.
For some reason ORDER BY ... DESC isn't working in GROUP_CONCAT. You know anything about that?

Code:

GROUP_CONCAT(bd_entry_category.category_id ORDER BY category_id DESC) AS entry_categories

It's not like I need it, I'm just curious why it isn't returning any records.


Here's my final query:

Code:

SELECT
     bd_entries.id AS entry_id
    ,bd_entries.title
    ,GROUP_CONCAT(bd_entry_category.category_id ORDER BY category_id ASC) AS entry_categories
    ,COUNT(bd_entry_category.entry_id) AS category_count
FROM bd_entries
INNER JOIN bd_entry_category ON bd_entry_category.entry_id = bd_entries.id
WHERE bd_entry_category.category_id IN (7,18,21)
AND bd_entries.status = 'published'
GROUP BY bd_entry_category.entry_id
HAVING entry_categories = '7,18,21'

Thanks again