sort mysql rows by column?

Question

I have following sql query:

SELECT job_id, job_type FROM jobs

I'm getting the following result (set of rows) from mysql query:

RESULT (print_r):

Array
(
    [0] => stdClass Object
        (
            [job_id] => 239
            [job_type] => 'type1';
        }
    [1] => stdClass Object
        {
            [job_id] => 53
            [job_type] => 'type2';
        }
    [2] => stdClass Object
        {
            [job_id] => 76
            [job_type] => 'type3';
        }
    [3] => stdClass Object
        {
            [job_id] => 78
            [job_type] => 'type1';
        }       
)

As you can see I've got three types of job: type1, type2, type3

Is there any way to map/regroup these results by job_type? Basically I'd like to have something similar to this:

Array 
(
    ['type1'] => Array (
        [0] => stdClass Object 
            {
                [job_id] => 239
                [job_type] => 'type1';          
            }
        [1] => stdClass Object 
            {
                [job_id] => 76
                [job_type] => 'type1';          
            }           
    )

    ['type2'] => Array (
        [0] => stdClass Object 
            {
                [job_id] => 53
                [job_type] => 'type2';          
            }
    )

    ['type3'] => Array (
        [0] => stdClass Object 
            {
                [job_id] => 78
                [job_type] => 'type3';          
            }
    )
)

Or maybe I should use different query? I've tried to use array_map() with no luck, but I was getting only one array with elements from only one job_type.

Thanks in advance.

Answer 1

Unfortunately for this task does not have a native solution in pure PHP/MySQL. So You need to sort it on PHP side manualy. I think You should write function for this an use it when You need

function sortBy($array, $key, $sortKeys=false){
   // I don't test this function, just write it for answer, so it may contain errors
   $tmp = array();
      foreach($array as $k=>$v){
          $tmp[$array[$k][$key]][] = $v;
      }
   if($sortKeys)
       ksort($tmp);
   return $tmp;
}

And use it like

print_r(sortBy(mySQLSelect(),'job_type'));

Answer 2

You cannot do this with predefined PHP functions. But you can do it yourself pretty easily.

For example: Assuming you have your MySQL result as written in your question in a variable called $rows, you can do the following to get the desired map.

$map = array();
foreach($rows as $row) {
    if (!isset($map[$row->job_type])) {
        $map[$row->job_type] = array($row);
    } else {
        $map[$row->job_type][] = $row;
    }
}

Now $map contains your desired array.