Grab All Rows in ONE Column from One SQL Statement

Question

I'm looking for a way, either via SQL or PHP, to return many columns from an SQL statement but be able to iterate through them one column at a time.

I have a query which gives me for example:

ID | N1 | N2
1  | 15 | 20
2  | 25 | 30
3  | 35 | 40

I know I can go through with a foreach $result as $row and use $row['T1'] in the loop, but I'm looking for a way to put ALL $row['T1'] into an array without having to iterate over them. I havn't been able to find how to do this.

I would need an array (or set of arrays) which would be organized as:

myArray = array( 'N1' => array('15', '25', '35'),
                 'N2' => array('20', '30', '40')
          );

so that I can access them using standard array functions like array_sum() and array_slice().

Is there a way, using MySQL, PHP(PDO), to do this simply and programmatically for a large number of fields?

My SQL uses a complicated join to get many fields, these fields need to be averaged and printed to a text file. They would like to group the fields together, not have all fields in a standard table.

Hence needing the array properly set up.

So: Which method can I use to do this? I know I can iterate over the whole $result and push the $row['index'] into several of my own arrays, but surely there's a better way to do this? With a complicated join, is it quicker to do a seperate query for each field individually?

Marc B · Answer 1 · 2013-04-10T17:15:55.273

0

SELECT n1 FROM yourtable
UNION ALL
SELECT n2 FROM yourtable

This will naturally become very inefficient as you add more columns. Even though you're only issuing one "query" call, internally the DB still has to execute each member query in the union separately.

re-reading your stuff, perhaps this'd be more along what you want, in pseudo-code:

select N1, N2
from yourtable

$data = array()
while($row = fetch($result)) {
   foreach($row as $col => $val) {
      $data[$col][] = $val;
   }
}

edited Apr 10 '13 at 17:15

answered Apr 10 '13 at 17:01

Marc B

356,200
43
426
500

Wouldn't this produce: `$myArray = array(15,25,35,20,30,40)` or similar, simply appending the results instead of seperating? – StuckAtWork Apr 10 '13 at 17:08
well, you did say "one column at a time". so this'd be one way. But re-reading the rest, I see what you're getting at. – Marc B Apr 10 '13 at 17:14
That is what I've ended up with so far, basically transposing the array. Looks like there isn't anything built-in from PHP or SQL that allows that output from the query. The performance seems pretty good. I can probably get better performance still with `array_map` but I lose my `$col` index (as it is associative, not numerically indexed) – StuckAtWork Apr 11 '13 at 12:12

score 0 · Answer 2 · answered Apr 11 '13 at 18:25

What I came up with. Doesn't allow for anything except a 2-D array, but I'm sure another for statement or something could fix that. This allowed me to keep my keys.

// Trasposes, while keeping the keys, a 2-dimensional array.
function transpose($array) {
  $tArray = array();
  foreach ($array as $iKey => $i) {
    foreach ($i as $jKey => $j) {
      $tArray[$jKey][$iKey] = $j;
    }
  }
  return $tArray;
}

Result:

$myArr:
Array
(
    [0] => Array
        (
            [N1] => 15
            [N2] => 20
        )

    [1] => Array
        (
            [N1] => 25
            [N2] => 30
        )

    [2] => Array
        (
            [N1] => 35
            [N2] => 40
        )

    [3] => Array
        (
            [N1] => 45
            [N2] => 50
        )

)

transpose($myArr)
Array
(
    [N1] => Array
        (
            [0] => 15
            [1] => 25
            [2] => 35
            [3] => 45
        )

    [N2] => Array
        (
            [0] => 20
            [1] => 30
            [2] => 40
            [3] => 50
        )

)

Grab All Rows in ONE Column from One SQL Statement

2 Answers2