1
\$\begingroup\$

I have a website that keeps track of 'changes' that users make. These changes come in all shapes and sizes and each shape and size has a different MySQL table to store them in. In total there are 8 different tables. Each table has completely different columns, so I don't think a simple JOIN would be possible, however they all have a time column.

What I would like to do is show the last x number of changes made across all tables (i.e. sorted by time). How I am doing it now is querying all tables like this:

foreach ($tables as $table) {
    $stmt = $this->_dbh->prepare("SELECT $table.*,
            realname,
            time,
            reservations.conf_num,
            reservations.conf_letters
        FROM
            $table
        LEFT JOIN users ON $table.user_id = users.id
        LEFT JOIN reservations
            ON $table.conf_num = reservations.conf_num
        ORDER BY
            time DESC
        LIMIT ".$this->_num_values);
    $stmt->execute();

    $changes[$table] = $stmt->fetchAll();
}

Then I sort and slice the resultant array:

ksort($changes);
$changes = array_reverse($changes);

// Make sure to limit it if we have a maximum number of values
if ($this->_num_values > 0) {
    $changes = array_slice($changes, 0, $this->_num_values);
}

This works fine, however it just seems extremely inefficient to me since we are retrieving the latest x values from all tables instead of just returning the exact number so that the sum of the number of rows from the individual queries equals x. Is there a better way to do this, or am I just trying to micro-optimize too much here?

\$\endgroup\$
1
\$\begingroup\$

Yes, there is: create a stored procedure and UNION the result sets then sort the records and return them to PHP.

\$\endgroup\$
  • \$\begingroup\$ Edit comment. I'm working on it. I'll keep you posted if I have any trouble working this out. I've never done UNION before. \$\endgroup\$ – Mike Mar 19 '13 at 19:37
  • \$\begingroup\$ It was complicated and took me 2-3 hours, but I finally have it working. Thanks for the suggestion. \$\endgroup\$ – Mike Mar 19 '13 at 22:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.