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.

JSON and Browser Security on the YUI Blog

Douglas Crockford posts a set of guidelines on writing secure web applications using remote scripting and JSON on the YUI Blog.

JSON is a data interchange format. It is used in the transmission of data between machines. Since it carries only data, it is security-neutral. The security of systems that use JSON is determined by the quality of the design of those systems. JSON itself introduces no vulnerabilities.

JSON and Browser Security on the YUI Blog

We Should Always Try to Reinvent the Wheel

All the things we currently use will eventually become obsolete. They should, and reinvention helps make sure of that. Reinvention allows the shedding of the unnecessary and retention of what is optimal. It gives way for improvement and sometimes leads to discovery of new and better ways.

This also applies to software development. Having hand-me-down APIs and open source solutions is good, but this should not stop us from creating things from scratch on our own. With doing things from the ground up, you do not just learn how to extend, but you also figure out how things work, where you can improve, and what you don’t need. In some cases, reinvention is much more convenient than trying to understand something that’s not of your own. Dictate, instead of trying to figure out, how things work.

I like reinventing the wheel. I think most developers do. We love solving problems, and we love challenging solutions.

Nope, You Don’t Need to Work Abroad to Achieve Growth

After reading “Growth means going abroad?” and Migs Paraz’s related post “Exodus”, I couldn’t help but ask myself similar questions. Was my move to Singapore worth it? Do the pay I’m currently getting, and the affiliation that I have justify leaving my home country, being away from my family and fiancee, and giving up a community and company I founded? Am I learning new things that I couldn’t possibly learn if I chose to stay in the Philippines? Is this really a step forward career-wise or just a big jump in salary?

I’m not really clear with my answers. I’m not really learning new things that I couldn’t learn on my own, I’ve always been keen on exploring new things related to my field. I’m working for a big name in the web/internet industry, but almost as a nobody. It doesn’t beat running your own company and steering things yourself, but the beef it adds to my resume is a pretty big plus. About the pay, I can’t complain about the pay.

So is it worth all the sacrifices made? I’ll have to say yes. The increase in pay allows me to sort of fast track things - help support my family, save up for properties, prepare for my wedding, etc. As for the step-down in the job title, I like to think of it as a small step back in preparation for a big leap forward. Save up now, then go back and fund yourself later. Getting in touch with family is no longer a problem. The world is too connected now, and we have the internet to thank for that. With regards to learning, I can keep learning new things on my own. I can keep growing professionally and skill-wise, with or without my current affiliation, in or outside my home country.

So for now, I’ll stay here, do what I can do, learn what I can learn, and earn as much as I can. I might just be trying to justify my greed, but It’s how I like to think of things. Sort of looking at the positive side of things to boost my morale.