Anyone who has worked with database date/time fields probably recognizes the number from the title of this blog post. If not, it’s simple: there are 86400 seconds in a day. Why do I care about this? Because there are all sorts of fun things that I can do with that number. More…

One of the reasons I wasn’t around much earlier this year was I was in the process of moving a bunch of sites over to a new server (including this one). In most cases the move went without a hitch. In one particular case there was an interesting bug that didn’t show up right away. It was related to the banner system I wrote for my largest board. Fortunately it was an error on the “good” side, so I didn’t make any sponsors angry. More…

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.

More…

I recently rewrote my banner management system for one of my boards. The board is fairly active (in fact we’re averaging over 100,000 page views daily now) so with multiple page views per second taking place during the busiest times of the day it would make sense to be concerned about performance. And I was. But I also had to be concerned about auditing my banner and page statistics, and ensuring that if I said a banner was going to be displayed every 10 page views that it was. So the system had to be 100% efficient and just as accurate. That presented some challenges.

More…

The IP address information for a poster is stored in the phpbb_posts table in phpBB2. In my Checkbox Challenge MOD it’s also stored during registration attempts. The IP address stored after being converted to hex and is then stored as an 8 character string. This is then decoded on the fly when requested. Sometimes you might want to decode the IP from the character string by using MySQL directly. It turns out there is a very simple formula to do that.

More…

In the first post in this series on working with recursive data I talked about several different ways to store the information in a database. Some of them were promising, but they all had complications of some kind or another. I was using the phpBB Doctor Project Manager database design as an example, but there are quite a few different scenarios where recursive data will be found. Since SQL is not a recursive language, I am trying to find the best way to model the data so that I can access it with minimal fuss.

As an example, in my project management system I need to be able to quickly and easily identify the parent task, if the task has any sub-tasks (child records), and which tasks are at the same level (siblings). I would like to be able to traverse the tree in either direction (up to the parent or down to the child) without using recursion. In order to do that, I need a model that is different from anything presented in the prior post.

More…

There are all sorts of scenarios that require recursive data. If you don’t know what “recursive” means, it’s a relationship from an entity back to the same entity. In English, it’s data that points back to itself. Some typical examples of recursive data are company org charts, inventory build instructions, or even forums for phpBB3. Yes, I’m talking about phpBB3, are you surprised? I hope not, because I’m going to reference phpBB3 only in passing. The article is actually more about storing recursive data in any form. It’s also how I store information in my phpBBDoctor Project Management system, among other things.

SQL is not a recursive language. When I write a query it’s all about relationships between rows, not about looping back through the same table. Oracle has a special construct used to traverse recursive data and it works very well, but it’s the only database that I am currently aware of to support this. Since most phpBB MOD authors will not be writing for Oracle, I will skip that concept for now.

As mentioned in the first paragraph I have recursive data in my project tracking system that I use here on the phpBB Doctor site. The design for this system is simple, but complex. The first table is the project table and it includes summary attributes for the project. These attributes include values such as when the project started, who is the project manager, a description of the project, and the status. The next table is the tasks table. A task is assigned to a project, but a task can be broken up into sub-tasks as well. There is no expected limit to the depth of the tasks. Here is a screen shot of my test project so you get an idea of what I’m talking about.

More…

A Check-Up From the Doctor

I will not install code for clients that was written by someone else without reviewing it first. There is a lot of bad code out there. There is also some very good code, don’t get me wrong about that. But many excellent MOD authors just don’t know databases as well as they would like. So the queries that they write may be functional, but not optimal. At this point in my involvement with phpBB I write most of my own MODs, so most of the time this review is for a private client who wants me to install something that I don’t already have or have no interest in writing.

At one point after I essentially rewrote a MOD for one of my clients they suggested that I offer this service via the Doctor Board. Hmm. Well. It’s an interesting idea. It wasn’t in my plans for this board when I started (and still isn’t) and I would much rather get my desired services completed and released first, but yeah, I could do that.

Probably in about 2012.

Maybe.

More…

In prior articles in this series I have talked about associative tables and primary / foreign keys. In this post I am going to talk more about keys but more specifically about table joins. If you’ve been working with databases for a while this article will probably seem fairly basic. But like many basic things, it’s important.

More…

Over the years that I have been involved with phpbb.com I have seen a number of posts – not that frequent, mind you, but more often than I would expect – asking how to “renumber my users” or something like that. It seems that folks are bothered by the fact that the first user_id is 2, or sometimes by the fact that there can be gaps in the sequence of user_id values. Why worry? Your database doesn’t.

More…