SELECT vs select

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.
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 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 :)


Erich Beyrent's picture

#1 Erich Beyrent : Thank you

Thank you so much for posting this - I was struggling to figure out why a query that produced 3 results would end up with 9 pagination links. Not only did you post a solution, but also a good method for tracking down where issues are.

Nice work!

Anonymous's picture

#2 Anonymous : Well, since SQL standard

Well, since SQL standard doesn't mention case sensitivity as a requirement for sql statements your preg_replace should be case insensitive too!

Christian Nally's picture

#3 Christian Nally : Thanks for posting that!

That's definitely something I could have been trapped by. Thanks so much for posting this!