Pagination Category Issue

[Rich]

I am currently working on a reciprocals list for one of my sites. I am wishing to display the results in a series of pages so the page isn't umpteen miles long.

I have the pagination itself working, except it isn't sorting by category. instead, everything is being shown in each category, just broken up by pages. lol

I am speculating that it has to do with this query, and I likely have it written wrong:

  $get_recip = $vbulletin->db->query_read("
	    SELECT recipid, categoryid, sitetitle, username, userid, sitelink, sitelinkimg, aboutme, views, votes, rating, joindate, active
        FROM " . TABLE_PREFIX . "recip_links
        WHERE active=1 ORDER BY joindate DESC
	    LIMIT " . ($limitlower - 1) . ", $perpage
		");

I tried to set "order by" to category, but that did nothing. I have tried several different things over the past couple hours but i am about ready to pull my hair out.

Could someone lend this schmuck a hand sorting out why it isn't displaying by category.

[MoT3rror]

Try GROUP BY categoryid

PHP Code:

$get_recip = $vbulletin->db->query_read("
        SELECT recipid, categoryid, sitetitle, username, userid, sitelink, sitelinkimg, aboutme, views, votes, rating, joindate, active
        FROM " . TABLE_PREFIX . "recip_links
        WHERE active=1 ORDER BY joindate DESC, categoryid ASC GROUP BY categoryid
        LIMIT " . ($limitlower - 1) . ", $perpage
        "); 


[Rich]

I just gave what you wrote up a whirl, but it spits out a database error asking me to check the syntax near "group by". I then dropped the initial category id and tried that, no avail. Also dropped the comma, no avail. lol

I have never used the group by command, so I am not certain of the proper syntax. I tried running a search on google for the syntax, but I found more people asking the same question than I did anything else.

I appreciate the assistance.

[MoT3rror]

Sorry the GROUP BY goes before the ORDER BY.

PHP Code:

$get_recip = $vbulletin->db->query_read("
        SELECT recipid, categoryid, sitetitle, username, userid, sitelink, sitelinkimg, aboutme, views, votes, rating, joindate, active
        FROM " . TABLE_PREFIX . "recip_links
        WHERE active=1 GROUP BY categoryid ORDER BY joindate DESC, categoryid ASC 
        LIMIT " . ($limitlower - 1) . ", $perpage
        "); 


[Rich]

I have been playing with this more, and this has worked...partially.

  $get_recip = $vbulletin->db->query_read("
        SELECT recipid, categoryid, sitetitle, username, userid, sitelink, sitelinkimg, aboutme, views, votes, rating, joindate, active
        FROM " . TABLE_PREFIX . "recip_links
        WHERE categoryid='$categoryid' AND active=1 ORDER BY recipid DESC
	    LIMIT " . ($limitlower - 1) . ", $perpage
		");

It is now displaying the individual categories. One category, which I have one test link in, is not displaying the navigation and is showing the correct link. (Which means it is working as expected.)

The other test category, which I have 9 test links in is showing the nav, and is showing the first 5 links on the first page (which is how I have it limited), but the second page is blank and isn't showing the remaining links.

What would cause the second page to appear blank?

--------------- Added [DATE]1205678392[/DATE] at [TIME]1205678392[/TIME] ---------------

I got it. I wasn't passing the categoryid within the page navigation itself.

All works now. Thanks for the help MoT3rrer