CODEWITHSUNDEEP
phpmysqljsonpdo
EpicWally
EpicWally

Reputation: 297

Return a 2-dimensional array with column names from PDO query

What I am trying to do, is pass a database query's result set directly into a Google DataTable. (Google visualization api here).

My goal (at least this is the way I can think of to use it, is to use Google's ArrayToDataTable() function. For this, I need my query to return a two dimensional array that I can pass to this function. I was originally trying to do this using PDO::FETCH_ASSOC, but when I encoded the result into JSON, I got an array of row objects that google's API couldn't handle. I then found this question, and using PDO::FETCH_NUM, I get a 2 dimensional array, which is a lot closer to what I want, but it omits column headers.

Is there a simple way to get a 2 dimensional array whose first entry is the column names, and each subsequent entry is a row from a database query? I would think this would be trivial, but I can't find a solution, without writing a function to do it.

Thank you, -Eric

EDIT:

In response to Michael's answer, I wrote this:

function assocTo2dArray($array){
    $return = array();
    $return[] = array_keys($array[0]);
    foreach($array as $row){
        $return[] = array_values($row);
    }
    return $return;
}

which achieves the same result, but looks a little cleaner.

Upvotes: 1

Views: 1012

Answers (1)

Michael Wheeler
Michael Wheeler

Reputation: 660

This assumes you've already built and executed a prepared statement (named $stmt).

$results = array();

$n = 0;
$fetchmode = PDO::FETCH_ASSOC;
while (true) {
    $row = $stmt->fetch($fetchmode);

    if ($row == null)
        break;

    if ($n == 0) {
        $results[0] = array(); //headers
        $results[1] = array(); //first row
        foreach ($row as $k => $v) {
            $results[0][] = $k;
            $results[1][] = $v;
        }

        $fetchmode = PDO::FETCH_NUM;
    } else {
        $results[] = $row;
    }

    $n++;
}

This SHOULD do what you are looking for.

Upvotes: 1

Related Questions