How can I optimize this database to array structure code? I need to take data from a MySQL database and message it into a format expected by the front end of an application - I can not change the front end as other services provide it data in this same format.

The database is structured as follows:

id   type     value     label         optgroup
1    car      ix5       Ford Taurus   Ford
2    car      ix6       Ford Focus    Ford
3    car      ix9       Cobalt        Chevy
4    planet   ix8       Earth         Default


The output from this code must do the following: for types with optgroups, records must be categorized by optgroup; if there is only one optgroup, then it should be ignored. The real data has hundreds to thousands of rows per type. The finally array output from this data would be:

$data = [
    'car' =&gt; [
        'chevy' =&gt;  [ 'ix9' =&gt; 'Cobalt' ],
        'ford'  =&gt;  [ 'ix5' =&gt; 'Ford Taurus', 'ix6' =&gt; 'Ford Focus' ]
    ],
    'planet' =&gt; [ 'ix8' =&gt; 'earth' ]
];


The code I have doing this currently works, but is a bit slow, and I am looking for a possible improvement.  Here's the functioning code, where $STH-&gt;result() is the database result as an array of rows:

protected function _format($STH)
{
    $data = [];

    foreach ($STH-&gt;result() as $row)
    {
        if ( ! $row-&gt;optgroup)
            $data[ $row-&gt;type ][ $row-&gt;value ] = $row-&gt;label;
        else
            $data[ $row-&gt;type ][ $row-&gt;optgroup ][ $row-&gt;value ] = $row-&gt;label;
    }

    // selects with a single optgroup can have that optgroup removed
    foreach ($data as $menutype =&gt; $optorkey)
    {  
        if (is_array($optorkey) &amp;&amp; count($optorkey) == 1)
            $data[$menutype] = current($optorkey);
    }

    return $data;
}


EDIT

The original query generating the data is very simple, as follows:

SELECT type, value, label, optgroup FROM ####.options ORDER BY type, optgroup, label ASC


The data in this table is updated frequently by automated processes.
Non-optimizing suggestions

Your SQL is not very clear. What kind of object is acceptable for the type column, and how do you manage to put items into that column? How specific or general should the terms be in that column? What is the value column? label, and even optgroup? To me, these are extremely confusing. Is optgroup a group of options, or a group of outputs? This isn't official, but it's a good read and it might help you.

If each value of value (naming confusion right there!) starts with "ix", then why is it there? I can't even tell what that column means because the Earth comes between two cars! Anyways, if each entry starts with that prefix, consider adding it in your PHP.

Optimizing...

I don't see to much you can do. Your query is already quite minimal, but I'm not an expert there.