NYCPHP Meetup

[Cliff Hirsch]

I am trying to properly paginate a table spread over several pages. The
problem is, I want the # of rows displayed to be based on a collapsed
summary column. Using Javascript, the default display would be to
collapse everything based on one field (in this case category). As an
example, I may want to show 10 collapsed category rows per page that can
expand into a larger # of rows based on the # of rows in each category,
when a Javascript "shows rows in category" button is clicked. This makes
the start # a bit tricky and the individual rowsperpage # variable while
though the "category rowsperpage # is fixed.
 
I think this would give me the proper results if it worked:
 
SELECT fields FROM table WHERE table.userid = $userid
AND table.catid IN (SELECT DISTINCT catid FROM table
WHERE table.userid = $userid AND LIMIT $startrow, $rowsperpage)
 
>>But LIMIT is not allowed in a sub-query, so I tried this:
  
SELECT GROUP_CONCAT(DISTINCT catid) FROM table
WHERE table.userid = $userid AND LIMIT $startrow, $rowsperpage
 
SELECT fields FROM table
WHERE table.userid = $userid AND table.catid IN ($result from above
query)
 
>> But LIMIT does not work for GROUP_CONCAT, so I'm down to this:
 
SELECT DISTINCT catid FROM table
WHERE table.userid = $userID AND LIMIT $startrow, $rowsperpage)
 
PHP code to turn array result into a string, then:
 
SELECT fields FROM table
WHERE table.userid = $userid AND table.cid IN ($result from above query)
 
Is there a simpler way? Perhaps this is a case for AJAX -- only get row
info when an "expand" button is clicked. Thoughts?
 
Cliff Hirsch
_______________________________
Pinestream Communications, Inc.
Publisher of Semiconductor Times & Telecom Trends
http://www.pinestream.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20050930/520da6af/attachment.html>