Paginating multiple results in a single query

[Pandemikk]

Forgive me for the title, it's a bit lacking in descriptiveness. What I currently have:

 

$results = $this->registry->db->query_read("
SELECT title, threadid, forumid, dateline
FROM " . TABLE_PREFIX . "thread
WHERE postuserid = " . intval($this->profile->userinfo['userid']) . "
	AND sticky != 1
	AND forumid IN (13, 19, 39)
ORDER BY dateline DESC
LIMIT 0,50
");

 

This is fine for returning of the latest results to be readied for pagination by PHP. But I want to be able to have, say, 30 results for EACH forum ID (13, 19, 39, etc) without having to do 3 separate queries. Is this possible?

[Nodral]

If you have the id's in an array called $forumid[] , you can iterate through it and run the same query over and over

 

<?php
foreach($forumid as $value){
$results = $this->registry->db->query_read("
SELECT title, threadid, $value, dateline	
FROM " . TABLE_PREFIX . "thread
WHERE postuserid = " . intval($this->profile->userinfo['userid']) . "
AND sticky != 1
AND forumid IN (13, 19, 39)
ORDER BY dateline DESC
LIMIT 0,50
");

mysql($sql);



//Do what you want with the results of query



}

?>

[Pandemikk]

That'd be easy enough. But, if possible, I wanted to do this in one query (or else I'd simply make my life easier and make three queries).

[Nodral]

But surely if you're running the same query against 3 different fuorum id's this would be the logical way to do it?

 

I'm not sure I fully understand what you are trying to acheive.

[Muddy_Funster]

It could be done in a single query - in theory.  You would need to subquery each and every form table selecting the records and limiting to 30 resutls per query, then cross refference the results returned against the overall schema and produce your "master" result set, this would then need to be manipulated for pagination, and would only be relevent for a limited time.  It is, in my oppinion, over complicating a simple issue.

[fenway]

You're asking to retrieve N records per group -- yes, that's possible, and efficiently, too.

 

See here.

[Pandemikk]

I'm sorry but that's a bit advanced for me. I'm having trouble finding out exactly how to do it.

 

[fenway]

That link is precisely how to do it.  Of course it's advanced -- you're asking an for advanced query.  You had the option of just running it N times, but you didn't like that.

[Pandemikk]

I didn't mean to imply that it was too advanced for me to bother with. Simply stating that I would love to learn how to do it that way but I just can't understand it, so if you had any pointers or could lead me in the right direction that'd be much appreciated.

 

And the reason I don't want to run it x amount of times is for performance reasons. The page I'm running it on already has lots of queries and PHP processing so anything I can do to lessen the load is ideal. Plus it's good for me to start learning more advanced queries. So please don't take it as "I don't like it". It'd be much easier to do so doing it that way, but coding isn't always about doing what's easiest - though at times (perhaps this one included) that may be best.