displaying mysql column names in an array along with its data

Question

I am trying to fetch records from MySql and display them in an array along with column headers using PDO, reason I am doing this is because i want to export them in an excel and I am using php-excel

right now the following code displays the records perfectly fine in an array and I am able to export them just fine

    while ($row[] = $stmt->fetch(PDO::FETCH_ASSOC)) :
        $data = $row;
    endwhile;

The array i get as a result of above code is as following

Array
(
    [0] => Array
        (
            [user_id] => 1
            [fname] => First Name
            [lname] => Last Name
        )

    [1] => Array
        (
            [user_id] => 91
            [fname] => First Name
            [lname] => Last Name
        )
)

But the problem is I am missing column headers in excel, so for that I need to display the MySql column headers in an array as well.

I am using the following code to create an array for column headers

    for ($i = 0; $i < $stmt->columnCount(); $i++) {
        $col = $stmt->getColumnMeta($i);
        $columns[] = $col['name'];
    }

but this gives me an array in following format

Array
(
    [0] => user_id
    [1] => fname
    [2] => lname
)

whereas I need the column header array in following format

Array
(
    [0] => Array
        (
            [user_id] => user_id
            [fname] => fname
            [lname] => lname
        )
)

I will appreciate any help.

andrewtweber · Accepted Answer · 2014-03-10 16:45:18Z

1

I assume you want the headers to be in the same array. In that case you would do this before the while loop

$data[0] = array();
for ($i = 0; $i < $stmt->columnCount(); $i++) {
    $col = $stmt->getColumnMeta($i);
    $data[0][$col['name']] = $col['name'];
}

Also, the while loop is a little inefficient. You're pushing each row into the $row array and then copying it into $data every single time. It should be done like this instead:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) :
    $data[] = $row;
endwhile;

This should get you:

$data = Array
(
    [0] => Array
    (
        [user_id] => user_id
        [fname] => fname
        [lname] => lname
    )
    [1] => Array
    (
        [user_id] => value...
        ...
    )
    ...
)

answered Mar 10, 2014 at 16:45

andrewtweber

25.8k22 gold badges93 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Digital Chris · Accepted Answer · 2014-03-10 16:44:53Z

1

Instead of letting it auto-number, just provide the name again as the key:

for ($i = 0; $i < $stmt->columnCount(); $i++) {
    $col = $stmt->getColumnMeta($i);
    $columns[$col['name']] = $col['name'];
}

Example: https://eval.in/117424

answered Mar 10, 2014 at 16:44

Digital Chris

6,1771 gold badge22 silver badges29 bronze badges

Comments

AbraCadaver · Accepted Answer · 2014-03-10 17:00:24Z

1

Might be easier:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if(!isset($data)) {
        $data[] = array_combine(array_keys($row), array_keys($row));
    }
    $data[] = $row;
}

Or you could add them after the fact using array_unshift().

Or in a separate array:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if(!isset($col)) {
        $col[] = array_combine(array_keys($row), array_keys($row));
    }
    $data[] = $row;
}

edited Mar 10, 2014 at 17:00

answered Mar 10, 2014 at 16:54

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

1 Comment

Shairyar Over a year ago

Indeed another way of doing it, that's why I love stackoverflow, brilliant solutions by brilliant minds

Collectives™ on Stack Overflow

displaying mysql column names in an array along with its data

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related