Drew C King

I finally upgraded my Wordpress installation to 2.7.1, and while I was at it I decided to change the theme to something that makes reading code snippets a little bit easier. The previous theme I was using limited each code block to about 450px in width, plus it felt a little bit too dark. Now, running this Coogee theme, I’ve got a total of 660px worth of content width.

I’m also trying out the WP Super Cache plugin to speed things up. I don’t get very much traffic here, but Wordpress takes much longer to load than I’m accustomed to, so I thought I’d try the cache route. It makes a huge difference. My main blog index was taking over 3 seconds to fully load, as timed by Firefox’s YSlow plugin. With the cache enabled, it’ll load in about 0.3 seconds. Ahh.

Hopefully all my old posts and comments are displaying properly. There’s some odd character encoding issues that I need to sort out, but if you notice any glaring problems, like broken links or errors, please let me know.

Lately I’ve been forcing myself to learn PostgreSQL as a replacement for MySQL. In my experimentation, one of the first things I had to figure out how to do was simulate auto-incrementing. In MySQL it’s as simple as sticking the word AUTO_INCREMENT in the column definition, but PostgreSQL doesn’t have that. What it has instead is a totally separate database object called a SEQUENCE, which is essentially just a single-row table used especially to provide for this type of functionality.

For example, in order to create a simple table to store user records, you’d first create the sequence that will be used to generate the unique user IDs:

CREATE SEQUENCE user_seq

Then create the user table and tell its user_id column to use the sequence as the default value:

CREATE TABLE user
(
  user_id INTEGER DEFAULT NEXTVAL('user_seq'),
  email CHARACTER VARYING(50),
  password CHARACTER(40)
)

After this, the first user record that is inserted will get a value of 1, and successive insertions will be incremented appropriately, just like AUTO_INCREMENT.

Because this is such a common practice, PostgreSQL lets you take a little shortcut, via the SERIAL column type (thought technically speaking, it’s not actually a true data type). Using this, you can simply skip the sequence creation, and just specify your table like so:

CREATE TABLE user
(
  user_id SERIAL,
  email CHARACTER VARYING(50),
  password CHARACTER(40)
)

This will create a sequence called user_user_id_seq behind the scenes (i.e., table_column_seq)

I’ve been working on a project lately where I’m rolling my own basic role-based access control (RBAC) system. In experimenting with different ways to handle the role definitions, I realized that it’d be pretty nice to have a basic enum() function or construct, as a quick, convenient way to define a set of constants. There’s this option:

1
2
3
4
5

< ?php
    define('ADMIN', 0);
    define('MODERATOR', 1);
    define('EDITOR', 2);
?>

but that isn’t exactly succinct. I’d rather have something like this:

1
2
3
4
5

< ?php
    enum('ADMIN', 'MODERATOR', 'EDITOR');
    // ADMIN == 0 (true)
    // MODERATOR == 1 (true), etc.
?>

You can kind of mimic the enumerated values with arrays like so:

Read the rest of this entry

I’ve been using Comb with a few more projects recently. The main point of Comb is to structure the code in such a way that the request handlers get accessed directly via Apache, as opposed to rewriting the entire URL, and making PHP dynamically include or instantiate a request handling class (i.e. Action, Command, etc).

For example, you might have a registration form that is accessed like this (pseudo-HTTP):

1
2
3
4
5

# display the registration page...
GET  /register.php
 
# registration form submits to...
POST /proc_register.php

I personally hate that naming convention because it reminds me of how I wrote code 5 years ago, and it’s just plain ugly.

Here’s a cleaner, friendlier approach that I’d rather use:

1
2
3
4
5

# display the registration page...
GET  /register
 
# registration form submits to...
POST /register

Read the rest of this entry

Jody clued me into a convenient method of paginating a result set from MySQL. Since he hasn’t blogged about it yet, I will >:-)

In the past, I’d issue two queries back to back, similar to this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

<?php
    $page = isset($_GET['page']) ? (int) $_GET['page']: 1;
    $size = 10;
    $offset = ($page - 1) * $size;
 
    $db = new mysqli("localhost", "drew", "mypass", "db");
 
    $sql = "SELECT username FROM user LIMIT $offset, $size;"
         . "SELECT COUNT(*) FROM user";
 
    $users = array();
    $total = 0;
 
    if ($db->multi_query($sql) && $res = $db->store_result())
    {
        // fetch this page of user records
        while (list($name) = $res->fetch_row())
        {
            $users[] = $name;
        }
 
        // fetch the TOTAL number of users
        $db->next_result();
        list($total) = $db->store_result()->fetch_row();
    }
 
    $pages = ceil($total/$size);
 
    echo "Total Users: $total<br>";
    echo "Total Pages: $pages<br>";
    echo "Current Page: $page<br><br>";
 
    foreach ($users as $u) echo "$u<br>";
 
    if ($page > 1) 
        echo '<a href="?page=', $page-1, '">&laquo; Prev</a>';
 
    if ($page > 1 && $page < $pages) 
        echo " | ";
 
    if ($page < $pages) 
        echo '<a href="?page=', $page+1, '">Next &raquo;</a>';
?>

Take note of Line #8. The first query grabs the specified page of results, the second simply does a full count of all rows so I could calculate the total number of pages.

What sucks, however, is if the query is more complex, especially if the various parts of it have to be dynamically generated, e.g., table joins, conditions in the where clause, etc. In such a case you’d have to change the second query to get rid of the column list SELECT column1, column 2... and make it SELECT COUNT(*). Also, you’d have to toss out any ORDER BY and LIMIT clauses.

MySQL allows you to simplify this extremely common scenario by using the FOUND_ROWS() function. In the above code, you can simply modify the query itself:

1
2
3
4
5
6
7
8
9

<?php
    // ... change this:
    $sql = "SELECT username FROM user LIMIT $offset, $size;"
         . "SELECT COUNT(*) FROM user";
 
    // ... to this:
    $sql = "SELECT SQL_CALC_FOUND_ROWS username FROM user LIMIT $offset, $size;"
         . "SELECT FOUND_ROWS()";
?>

The rest of the code remains the same and will give you the exact same results.

The speed of this approach can vary depending on how you have your table(s) indexed, and the complexity of the query itself. In other words, in some circumstances it may be faster to actually run the LIMITed query followed by the appropriate SELECT COUNT(*) FROM ... query as in the first example, but the primary benefit is in the simplification of the query itself.