Using MySQL’s SQL_CALC_FOUND_ROWS to display paged data starting from the last page

MySQL’s FOUND_ROWS() and SQL_CALC_FOUND_ROWS let’s you issue a query that uses the LIMIT clause and at the same time, get the total number of rows matched by your query if the LIMIT clause weren’t there. This means that if you want to display pages of data and also want to show to the user the total number of pages you have, you don’t have to use two separate queries (first is for getting the total matching records, second is for fetching the records for the active page using a LIMIT clause).

Wanting to use this on my project (a messageboard), I was faced with the problem of displaying the last page of data first using this approach. Displaying the first page is trivial, but getting out the last page of data first requires some trickery. The main problem I had was I don’t know how many records I have in total so I can’t specify the correct offset in my LIMIT clause.

To better illustrate this, let’s create a simple example using the following MySQL table schema and sample data:

CREATE TABLE records (
  id int unsigned primary key auto_increment,
  content text
);

INSERT INTO records VALUES (1, 'Record 1'), (2, 'Record 2'), (3, 'Record 3'), (4, 'Record 4'), (5, 'Record 5'), (6, 'Record 6'), (7, 'Record 7'), (8, 'Record 8'), (9, 'Record 9'), (10, 'Record 10'),...(94, 'Record 94');

We have 94 records, and let’s say we want to show 10 records per page, starting from the last page. We should have 10 pages total, with the last page containing only 4 records. We want the last page with 4 records to show up first.

Let’s use the following query to fetch our last page of records using SQL_CALC_FOUND_ROWS:

$res1 = mysql_query( "SELECT SQL_CALC_FOUND_ROWS * FROM records ORDER BY id DESC LIMIT 10" );

Notice that even though we only want to show the 4 records on our last page, we still specified 10 in our LIMIT clause. This is because when issuing this query, we still don’t know the total number of records that match our query. We just assume for now that we have evenly distributed data. Remember that we have our last page results on $res1.

To get how many rows we have in total, we use the following code:

$res2 = mysql_query( "SELECT FOUND_ROWS() value" );
$temp = mysql_fetch_assoc( $res2 );
$total_records = $temp['value'];
mysql_free_result( $res2 );

Now we know how many records we have in total. To find out how many pages we should have, and how many records we should be showing in the last page:

$total_pages = ceil( $total / 10 );
$last_page_records = ( $total % 10 );

We now have our total page count ($total_pages) and we now know how many records we should be showing on our last page ($last_page_records). Now let’s get the records we need to display from $res1.

$data_array = array();
for ( $i = $last_page_records - 1; $i >= 0; --$i ) {
  mysql_data_seek( $res1, $i );
  $data_array[] = mysql_fetch_assoc( $res1 );
}

Since in our SELECT query earlier, we specified that data be sorted in reverse order (so we can get from the end of the matching records), we had to get our records in reverse order to get it back to normal sorting order.

A thing to note here is that we’re fetching data in reverse order. This means using ORDER BY attribute DESC. As of this writing, MySQL only stores index values in ascending order. This means you’ll be getting a Using filesort in your EXPLAIN results. To get around this, you can create pseudo reverse indexes, as suggested by Igvita.com.

Suggestions, corrections, criticisms and findings are welcome.

Using $_SESSION to Persist Form Input

In my previous projects, I’ve always used the query string for setting initial or previously submitted values to the forms. Something like:

http://some.domain.com/ourformpage.php?error=1&somefield=bob

This is fine and works well, but doesn’t work so well if you want to return long lines of text or huge data. For this, I’ve decided to use the $_SESSION variable.

When the user submits the form, the form action or handler checks the data, and in cases of errors, the form values are stored in the $_SESSION variable which are then fetched by the form after the redirection.

This works fine, as long as you keep in mind that the values you store in $_SESSION are accessible from all your active connections. Meaning if you open the same form several times and work on all of them at the same time, then you
might encounter overlapping values or previous values from one form showing up in another.

To address this issue, we create a token unique to that form instance which is included in the requests between the form and form handler. This token is then used to index an array in my $_SESSION var containing all the previous inputs for that form.

Can’t explain it too well but here’s a basic implementation. In our form page, we have:

<?php
$token = isset( $_GET['token'] ) ? $_GET['token'] : md5( time().’SOME_SECRET_KEY’ ) ;
$previous_input = isset( $_SESSION[$token] ) ? $_SESSION[$token] : array() ;
?>
<form method=”post” action=”myscript.handler.php”>
<input type=”hidden” name=”token” value=”<?php echo htmlentities( $token ); ?>” />
<input type=”text” name=”somefield” value=”<?php echo isset( $previous_input['somefield'] ) ? htmlentities( $previous_input['somefield'] ) : ” ; ?>” />
<input type=”submit” value=”Submit” />
</form>

And in our handler script myscript.handler.php, we have something like:

// get our token and input data
$token = isset( $_POST['token'] ) ? $_POST['token'] : md5( time().’SOME_SECRET_KEY’ ) ;
$somefield = isset( $_POST['somefield'] ) ? $_POST['somefield'] : ” ;
…
// persist data using our unique token
$arr = array();
$arr['somefield'] = $somefield;
$_SESSION[$token] = $arr;

Don’t forget to pass the token value to our form during redirection, so the form can fetch the previous values.

header( 'Location: ourformpage.php?token='.$token );

Note that the code shown is simplified for demo purposes. When doing this in your project, remember to filter all input and escape your output.

Another thing to note is in our code sample, we used time() for creating unique form tokens. This means our form values won’t overlap unless the user opens several forms within a second. Just improve on this for better resolution.