I spent way too much time this weekend trying to get the pager stuff in Drupal working for a module I`m playing with.
I had learned a lot from the watchdog module on how paging was supposed to be working and I was trying to do the same with another database. As I got a page limited by the number of records I wanted but I couldn't find any of the fancy next, previous and page number thingies that I wanted.
The watchdog module worked for me and I started stripping the watchdog module till I could actually replace my function with watchdog_overview function. Even replaced the watchdog query with my qeury.. but not such luck.. I couldn't get $output .=
theme('pager', NULL, 50, 0); to work.
So as every open source geek does.. I started looking into the code. pager.inc
The header told me the author.. I could mail him and wait or even call him , , but I read on.
$pager_total_items[$element] = db_result(db_query($count_query, $args));
Never returned anything useful.., my mysql log learned me there never was any query with a count ..
Till I modified the pager.inc file to actually perform the query I wanted it to do.. and it started working.
Then I took a closer look at the regexp that was being used to create the $count_qeury
$count_query = preg_replace(array('/SELECT.*?FROM /As', '/ORDER BY .*/'), array('SELECT COUNT(*) FROM ', ''),$query);
However my query was all in lowercase so the count was never inserted.
The only difference between the working watchdog module and my own code was in the use of uppercase SQL statements.
So call me lazy :)