MySQL: What to do in place of arrays

What would you say is the most efficient way to do this query in MySQL? Say you have two tables:

blogs: id, title, post (E.g. 1, 'Why I like MySQL', 'MySQL is…')
categories: id, blog, name (E.g. 1, 1, 'Databases', 2, 1, 'Open Source')

How could I query a “page” of blogs with an offset and get a fixed number of blogs (e.g. 10 per page) but also all their categories? I understand MySQL doesn’t support arrays so was wondering if this could be done with one query rather than selecting the categories with a query per blog.


I don’t know what you mean with query a page of blogs with an offset

Blogs will need to have a foreign key if you want to know the category. That is, you need to add the field “category_Id” and your table would be:

Blogs: id, title, post, category_id (e.g. 1, ‘Why I like MySQL’, ‘MySQL is…’, 1)
That last 1 indicates that this blog belongs to the category 1, with the name of ‘Databases’

Select * from blogs inner join categories on = limit 10;

something like this

[quote=“nvrmnd1, post:3, topic:216526, full:true”]something like this
no, because if each blog has 2 categories, you’ll end up listing only 5 blogs

he wants 10 blogs, regardless of number of categories each blog has

try this, @DrQuincy

SELECT , b.title , , GROUP_CONCAT( AS categories FROM blogs AS b LEFT OUTER JOIN categories AS c ON = GROUP BY ORDER BY DESC LIMIT 10

@r937, a-ha, just what I wanted, thanks!

Just a further question on this. Is there a suggested way to separate chunks of data if you’re returning strings? For ints, for example, a comma is good enough but that’d likely break in text (e.g. if I had a comma in one of my blog categories). Creating a small random string to go between them would do it but just wondered if there was a built-in way.

You could also break this into two queries. One to fetch the paginated blog posts and another to fetch all categories for the retrieved blog posts. One more query isn’t going to make any significant performance impact and might even be faster. This will also make it easier to model the domain with data other than just the category name.

SELECT x,y,z FROM blogs LIMIT 10;

-- Create array of blog ids in result set to use in where clause for categories.

SELECT x,y,z FROM categories WHERE blog_id IN ({blog_id_list})

-- Map categories to each blog at the application level.

Than for instance when using PHP it would a simple of matter of just populating each blog with its categories. Something like.

foreach($categories as $category) {
$blogs[$category['blog_id']]['categories'][] = $category;

[quote=“DrQuincy, post:6, topic:216526, full:true”]
Just a further question on this. Is there a suggested way to separate chunks of data if you’re returning strings? [/quote]
yes, use a special delimiter of your choosing

see da manual for the DELIMITER syntax of the GROUP_CONCAT function

Yes, I thought of this way too but was interested in how it could be done in a single query. In my case I think the performance difference will be negligible and will probably use this method.

Do you mean SEPARATOR? For my usage above I have realised that actually a new line will do the job nicely.


Just note as well the string returned by GROUP_CONCAT is limited:

[quote=“DrQuincy, post:10, topic:216526, full:true”]
Do you mean SEPARATOR? [/quote]
yeah, SEPARATOR… sorry, mind not always working at 100% efficiency

Thought so! Thanks for all your help.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.