pager_query() peculiarities

Submitted by Gold on Wed, 07/30/2008 - 20:11

I'd been banging my head against this for a while now and finally dug around in includes/pager.inc to figure out what was going on...

The situation;
I had a query and while pager_query() returned the correct results theme('pager') always returned way too many pages.

The SQL fed into this function is a little picky. I had the SQL laid out in the source for user readability. The preg_replace() didn't like it and couldn't spot the SELECT...FROM chunk. In order to allow this to work nicely you need to ensure that the very first thing in the string is the SELECT. Not a new line so that you can have your query line up nice in your editor. Also, the FROM statement needs to be on the same line.

I tend to layout my SQL for visually easy reading. e.g.

$sql = "
  SELECT my, fields
  FROM myTable
  WHERE thiscondition
  AND thisconditiontwo
  ORDER BY whatever";

pager_query() fails on this. Modifying it to what we have below works however;

$sql = "SELECT my, fields FROM myTable
  WHERE thiscondition
  AND thisconditiontwo
  ORDER BY whatever";

I'm thinking I may write a hook_db_rewrite_sql() to sanitize this so I don't need to think about it in the future...