How to create multidimensional array with name of columns in PDO

Question

I have this query and I would like to create multidimensional array. I tried this:

$columns = array("col1", "col2", "col3", "col4");
$query = "SELECT " . implode(",", $columns) . " FROM my_table";
$sql = $db->prepare($query);
$sql->execute();

$data = array();
while ($row = $stm->fetch()) {
    $nestedData = array();

    for ($i = 0, $m = count($columns); $i < $m; $i++) {
        $value = $row[$columns[$i]];
        $nestedData[] = empty($value) === false ? $value : "";
    }

    $data[] = $nestedData;
}

I get something like this:

[["value11","value12","value13","value14"],
["value21","value22","value23","value24"], etc]

I would like to have there also names of columns (like this):

[["col1":"value11","col2":"value12","col3":"value13","col4":"value14"],
["col1":"value21","col2":"value22","col3":"value23","col4":"value24"], etc]

Could anybody help me how to achieve this?

Possible duplicate of How to read fetch(PDO::FETCH_ASSOC);

Calimero
– Calimero

2015-11-26 09:21:20 +00:00
Commented Nov 26, 2015 at 9:21 — Calimero
– Calimero, Commented Nov 26, 2015 at 9:21

Ostin · Accepted Answer · 2015-11-26 09:25:24Z

1

Have a look at "fetch_style" properties: http://php.net/manual/en/pdostatement.fetch.php

And use http://php.net/manual/en/pdostatement.fetchall.php

$columns = array("col1", "col2", "col3", "col4");
$query = "SELECT " . implode(",", $columns) . " FROM my_table";
$sql = $db->prepare($query);
$sql->execute();
$data = $sql->fetchAll(PDO::FETCH_ASSOC);

answered Nov 26, 2015 at 9:25

Ostin

1,5511 gold badge12 silver badges25 bronze badges

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

Comments

Pavlin · Accepted Answer · 2015-11-26 09:23:54Z

0

I believe what you're looking for is that PDO returns an associative array. This can easily be achieved by doing this:

$results = $stmt->fetch(PDO::FETCH_ASSOC);

or if you want this to be default for all your queries, you can create your connection that way:

$conn = new PDO($connStr, $usr, $pass, [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);

For more information or options, read the php manual: http://php.net/manual/en/pdostatement.fetch.php

answered Nov 26, 2015 at 9:23

Pavlin

5,5587 gold badges44 silver badges57 bronze badges

Comments

user2959229 · Accepted Answer · 2015-11-26 09:25:19Z

0

Firstly, $results = $stmt->fetch(PDO::FETCH_ASSOC);

Then you need:

$nestedData[] = empty($value) === false ? $value : "";

needs to look like

$nestedData[$column_name] = empty($value) === false ? $value : "";

where $column_name is the name of the field. Look at the manual to work out how to get the field names that you need.

Something like this might work:

foreach ($row AS $key => $value)
{
    $nestedData[$key] = empty($value) === false ? $value : "";
}

answered Nov 26, 2015 at 9:25

user2959229

1,3552 gold badges11 silver badges21 bronze badges

Comments

Standej · Accepted Answer · 2015-11-26 09:27:30Z

0

Instead for loop you can do foreach loop

foreach($array as $key=>$element){
    $nesteddata[$key] = $element;
}

answered Nov 26, 2015 at 9:27

Standej

7531 gold badge4 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to create multidimensional array with name of columns in PDO

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related