CODEWITHSUNDEEP
mysql
John Tangale
John Tangale

Reputation: 435

Most efficient mysql query to retrieve multiple categories for each result in a search listing

I have search page that returns 10 articles per page. Each article can be assigned to multiple categories. As part of the search listing I want to be able to display something like:

Title of Article
Added on: 6/11/02
Categories: Basic, Cars, Mustang
Summmary of article

The only way I can think of doing it is for each search result do a query that retrieves its categories. If I do this, though, each search in the app would generate 10+ queries. I also give people the option of listing more than 10 search results, which would generate even more queries.

Is there a more efficient way of doing this?

Upvotes: 0

Views: 147

Answers (1)

bfavaretto
bfavaretto

Reputation: 71908

One possible solution: JOIN the category table(s) on your main query. This will give you a resultset with multiple rows per article (one row per article/category combination). Then you can (a) filter duplicates on your php/asp/ruby/whatever code, or GROUP BY category ID and GROUP_CONCAT the categories on your SELECT clause.

Upvotes: 1

Related Questions