Optimizing SELECT * FROM with mysql_field_table()

Creating your own data access layer, can be quite some work. After some time, you will tackle the basics of querying the database and parsing the results into objects. Each query only retrieves a set of data from a single table, so you push the code to production like I did.

Then, after using the code for some time you want to be able to execute slightly more complex queries (like for example JOIN or sub queries). Then that piece of neat code, once running smoothly, started to degrade very fast.

Database structure

Our sample database contains two message entries written by two different authors.

Table: user

id name
1 Bouke
2 Tim

Table: post

id userId name
1 1 This is Bouke's message
2 2 This is Tim's message

Objects

So, let's talk some code. The system contains posts written by some user. The objective is to list all the messages and the user who posted the message. The following objects are given.

class Post
{
    public $id;
    public $userId;
    public $message;

    protected $_user;

    public function setUser(User $user)
    {
        $this->_user = $user;
    }

    public function getUser()
    {
        if(!isset($this->_user)) {
            $result = mysql_query("SELECT * FROM `user`
               WHERE `id` = {$this->userId}");
            $this->setUser(mysql_fetch_object($result, "User"));
        }

        return $this->_user;
    }
}

class User
{
    public $id;
    public $name;
}

Initially: 3 queries

This one is simple and straight forward. First, you fetch all the posts. Then for each post, you fetch the person who posted it. Note, there is initially a single query for requesting all the posts, but each post results in a query for the user.

$result = mysql_query("SELECT * FROM `post`");

while($post = mysql_fetch_object($result, "Post")) {
   echo "{$post->getUser()->name}: {$post->message}", "<br/>\n";
}

Improvement with a JOIN

The solution seems simple, using a JOIN only performs a single query:

SELECT * FROM `post` JOIN `user` ON (`post`.`userId` = `user`.`id`)

But this has also its affects on the code issuing this query. The code needs to build two objects from each row it receives. Using a simple associative array will not help you here, as both tables have a column called `id`.

Aha, you might think, use an alias and case solved! But what if you are a bit lazy, like me. You would not want to specify all the columns with their aliasses, but still use SELECT *. This is where mysql_field_table() appears on the stage:

string mysql_field_table ( resource $result , int $field_offset )
Returns the name of the table that the specified field is in.

From: PHP Manual

So, first we query the database using a join:

$result = mysql_query("SELECT * FROM `post`
   JOIN `user` ON (`post`.`userId` = `user`.`id`)");

Then, we build the mapping of column index => {tablename, fieldname}

$columns = array();
for($i = 0; $i < mysql_num_fields($result); $i++) {
    $columns[] = array(
        mysql_field_table($result, $i),
        mysql_field_name($result, $i));
}

// Result:
// [["post","id"],
//  ["post","userId"],
//  ["post","message"],
//  ["user","id"],
//  ["user","name"]]

Now we know which column relates to which tablename and fieldname, we iterate over all rows and create a two-dimensional array:

$rows = array();
while($row = mysql_fetch_row($result)) {
    foreach($row as $i => $value) {
        $row[$columns[$i][0]][$columns[$i][1]] = $value;
        unset($row[$i]);
    }
    $rows[] = $row;
}

// Result:
// [{   "post":{"id":"1","userId":"1","message":"This is Bouke's message"},
//  "user":{"id":"1","name":"Bouke"}},
//  {   "post":{"id":"2","userId":"2","message":"This is Tim's message"},
//  "user":{"id":"2","name":"Tim"}}]

Then the last step concerns transforming the two-dimensional array into object:

class Factory
{
    public static function build($object, array $data)
    {
        foreach($data as $property => $value) {
            $object->$property = $value;
        }
        return $object;
    }
}

foreach($rows as $row) {
    $post = Factory::build(new Post(), $row["post"]);
    $post->setUser(Factory::build(new User(), $row["user"]));

    echo "{$post->getUser()->name}: {$post->message}", "<br/>\n";
}

And you're done; all down to a single query! You might wander, why would you rewrite four lines of code to a whoppin' 32 lines? Only for the benefit of using lazy SELECT * querying?

Well, those 32 lines decompose into 25 lines for the framework and only a stunning 7 lines for the fetching and object building. Is the method worth rewriting your code?

The method explained has not yet been taken into production, as I first want to perform an speed impact vs ease of code analysis. For now, the code provided is only a proof of concept.

Except where otherwise noted, this work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.