I’ve spoken about pagination before. Use the search to see where. I’ve started adding pagination to the Search page. I’ll be using the Pager module from PEAR again.

In short, here is what Pager gives you, in the form I’m using it:

Gives you a list of hyperlinks to the various pages

Tells you the parameters to use in your SQL query to get the first and last item on the page

You can also give it your entire data set, but that’s pretty heavy duty. I use Pager as a counter, for lack of a better term. It tells me what page I’m on, gives me the hyperlinks to the other pages, and leaves me alone to paint the page. It’s a tool.

How have you been paging your output?

I’ll probably give you a better introduction to Pager in a future post. For now, I want to share with you what I found in the search page and how I recently improved the SQL.

Better code

While I was adding the new starting with and ending with search conditions, I wanted to avoid duplicating some code. So I create this little function to help build the SQL. Query building is something I like doing. Here is part of the code that builds the user supplied condition based upon a field containing a supplied value. In short, you are searching for the supplied value somewhere within a given field. The condition is sometimes case-insensitive. Here is how I cater for that.

Now consider how to do that if you have two more very similar conditions: starts with, or ends with. I could duplicate the above case statement two more times, and remove the leading and trailing wild card symbols (% is a wild card in SQL). Or, I could do this instead:

Better query

I did that yesterday. Today I was completing the work to count the number of rows. Yes, I can get the row count after issuing the query. But the goal with pagination is to not pull back all the data. Pull back exactly what is required for this page. To do that, you need to know how many rows you have, and thus, how many pages. With that information, Pager can tell you the parameters for your SQL query (i.e. to be used in the LIMIT and OFFSET clauses of your SELECT statement.

I did not want to build the query twice. The I wanted one code stream and at the end of it, I would have both the query for the row count, and for the result set. Here is an example of how I did it:

Oh, and I wound up making the query much slimmer while doing this. For some reason, I’d been joining on commit_log_ports and commit_log when searching. That is why I needed that DISTINCT clause you see in the old code. Perhaps there was a reason for it in the past, but if so, it is lost.