Home

Your premium source for custom modification services for phpBB

  logo

HomeForumsBlogMOD ManagerFAQSearchRegisterLogin

Comments August 29, 2008

Building a Better Board Banner System Part II: Using LAST_INSERT_ID()

Filed under: Database Tips, MOD Writing — Dave Rathbun @ 7:19 am CommentsComments (5) 

In the first post in this series I talked about the design process for my new banner system. I wanted it to be 100% accurate so I eliminated any sort of random number generation process. I also eliminated a SELECT … FOR UPDATE because I was concerned about deadlocks affecting the efficiency of my code. At the end of the post I introduced the MySQL LAST_INSERT_ID() function. Today I will cover it in much more detail.

MySQL offers an interesting function called LAST_INSERT_ID() that – once I figured out the proper syntax – provided exactly what I needed for my accurate and efficient banner system. What does it do? Simply put, it provides a shortcut to return the result of a previous SQL statement without having to worry about intervening updates. Before I explain the function, I want to share a bit of the database design and how the process will eventually work.


Page Count Math

I have a table called phpbb_views. The structure of this table is very simple: it only contains a single column named view_ctr. The field is currently defined as int(11) unsigned which means it can go up to a really large number. What can I say… I’m optimistic. :) I have had code in place for years that increments this value on every page view. Previously I had the update statement running inside the includes/page_tail.php code. For the banner system I moved that logic into the includes/page_header.php code instead.

Let me assume that I have 10 banners that are all equally weighted. I want to ensure that these banners are displayed in sequential order, from one to ten and then starting over again. There is a perfect function for this, and you might have seen it if you’ve looked at how phpBB2 generates alternating row colors for tables. The syntax looks like this:

($var1 % $var2)

The result is the remainder of $var1 divided by $var2. Note that both $var1 and $var2 are converted to integer values (if needed) prior to the operation. How can this help me?

A sequential number (like page view counter values) will increment by one, starting at some number, and extending to infinity. What I need is a rotating sequence of numbers that starts at 1 and goes to 10 and then starts over again. That’s exactly what the % operator will do for me. All I have to do is use my page view counter as $var1 and 10 (the number of banners) as $var2 and here’s what the numbers look like:

+-----+--------------+
| 100 |            0 |
| 101 |            1 |
| 102 |            2 |
| 103 |            3 |
| 104 |            4 |
| 105 |            5 |
| 106 |            6 |
| 107 |            7 |
| 108 |            8 |
| 109 |            9 |
| 110 |            0 |
+-----+--------------+

If you notice the column on the left has an increasing sequence, while the column on the right starts with zero and increments up to nine and then starts over again. This is because 100 / 10 has no remainder, while 101 / 10 has a remainder of one, and so on. This is perfect! Now all I have to do is ensure that I will never skip a number and I have a guaranteed way to pick the next (and correct) banner to display. If I have a zero-based array index these numbers are already perfect. If my array index starts at one then all I have to do is change the formula to ($var1 % $var2) + 1 and I am all set.

How the MySQL LAST_INSERT_ID() Function Works

I will jump right to the syntax of my query and then explain it next:

UPDATE phpbb_views
SET view_ctr = LAST_INSERT_ID(view_ctr+1) ;

This statement accomplishes two things at the same time. First, it updates the view_ctr column using the provided expression (view_ctr + 1). Second, the LAST_INSERT_ID() function makes a note of the resulting value and stores it as a part of my session. In a sense, it is doing the update and the select all at the same time! This eliminates any potential issue with statements appearing out of order. It also eliminates any need to perform any locking on the rows or the table.

To retrieve the session value I issue this command:

SELECT LAST_INSERT_ID() ;

That is simple, isn’t it? As per the MySQL documentation this query does not even reference a table. It pulls the last affected value from the session variable and provides it to me. If the view_ctr value was 110 and I run the first command the new value for view_ctr is 111 and that value is also cached for my session. When I retrieve the value using the second query I get 111.

All of my problems are solved. 8-) I can use this technique without worrying about a deadlock and without worrying about missing values. It didn’t take long to set up my code with this function. But is it fast?

On average that query takes about 0.00015 seconds. That’s not too bad. What’s even better is that the query to set the new value as well as the one required to update the banner counters both are equally as efficient.

My final banner system provides me with the following features:

  • Banners can have start dates or expiration dates
  • Advertisers can have multiple banners on file
  • Advertisers can “weight” banners so they appear more (or less) frequently
  • Page views are evenly distributed across all active banners according to weight

There are many more features in place, but the last bullet point is the result of the work done to get this far. I will talk more about the table design and how I completed my banner system in my next post.

5 Comments »

  1. I think this will prove useful for a project I’m working on.

    Can I query SELECT LAST_INSERT_ID() ; without having run an INSERT?

    Ie, can I just do SELECT LAST_INSERT_ID() FROM phpbb_tablename to get the next auto-index?

    Does that work?

    Comment by Dog Cow — August 29, 2008 @ 3:37 pm

  2. Dog Cow, if you look at the code you’ll see that I’m using the LAST_INSERT_ID() to track an update. :) From what I remember reading, it captures the expression result, it’s not really tied to the auto-increment value despite what it sounds like.

    An auto-increment is already handled separately; there is no danger of getting someone else’s increment value in your code even if you sleep for a few minutes before going back to get it. The power of the LAST_INSERT_ID() in my case was I can run an update and capture the result of the update all in one statement. The code was:

    UPDATE phpbb_views
    SET view_ctr = LAST_INSERT_ID(view_ctr+1) ;

    So what that does is generate a numeric value by incrementing the current value of view_ctr by adding one, capture that number in a register or something somewhere, then perform the update. Then when I request the LAST_INSERT_ID() value I retrieve that same value, even if 20 more updates have happened in between the update and my request. And I didn’t have to lock the row or table in order to manage that.

    Comment by Dave Rathbun — August 29, 2008 @ 4:10 pm

  3. I should have explained myself better. You didn’t answer my question (my fault).

    Ok, what I want to know is if there is an easy way to find out what the next auto_increment is going to be before the row is inserted/updated.

    I need to use that next auto_increment number to influence another decision in the code. For example, if the index will be greater than 100, then the number will be 2, else if it is less than 100, the number will be 1.

    I know there’s a not-so-elegant way where you can query the table info (this is how phpMyAdmin does it, because it will show the next auto_increment value).

    Can this be done in one query? If not, my plan 2 is to use two queries to do the same thing.

    Comment by Dog Cow — August 30, 2008 @ 4:28 pm

  4. The brute-force way would be to run a query like they do when adding a new user to phpBB2. You get the max value and increment by one. However, that’s not suitable for high-volume tables. I don’t think the LAST_INSERT_ID() function is going to help because it preserves a value that was just processed, rather than anticipating something that has yet to happen.

    Comment by Dave Rathbun — August 30, 2008 @ 4:48 pm

  5. Thanks for the info; I think it’s back to plan 2 for me. :(

    Comment by Dog Cow — September 2, 2008 @ 9:51 am

RSS feed for comments on this post.

Leave a comment

Tags allowed in comments:
<a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <strong> <em> <u> <sup> <sub> <strike>

Confirm submission by clicking only the marked checkbox:

     **         

Powered by WordPress