Why is my code making PDO return nested arrays?

Question

I'm sure I must be doing something wrong, but I can't seem to find an answer anywhere (at least neither in the documentation nor on SO as of yet). I am a beginner in PHP and SQL.

I have this simple table called "mObject_has_media" with only two columns "media_idMedia" and "mObject_idmObject"

When I try to query the table with this code,

public function selectMediaIds (MObject $object) {
    $medias = array ();
    $req = $this -> _db -> query ('SELECT media_idMedia FROM mObject_has_media WHERE mObject_idmObject = ' . $object -> id ());
    while ($data = $req -> fetch (PDO::FETCH_ASSOC)) {
        $medias[] = $data;
    }
    $req -> closeCursor ();
    return $medias;
}

the problem is this: when I var_dump the contents of the method's result, I get:

array (size=2)
  0 => 
    array (size=1)
      'media_idMedia' => string '52' (length=2)
  1 => 
    array (size=1)
      'media_idMedia' => string '53' (length=2)

I'd like to get rid of those nested arrays and just get something like:

array (size=2)
  0 => 'media_idMedia' => string '52' (length=2)
  1 => 'media_idMedia' => string '53' (length=2)

so I can simply run this array into a foreach loop right afterwards... I'm sure that this problem has everything to do with the fetch() method, but in spite of experimenting with different options, I haven't been able to get the desired behavior.

A similar question (I think) was asked here: PDO::FETCH_ASSOC Does not return the correct table columns but the answer mentioned the Zend framework, and I am NOT using any framework at all, this is just vanilla PHP.

0 => 'media_idMedia' => string '52' (length=2) makes no sense. That isn't a real data structure, but the closest thing it resembles is a nested array, which you claim to be trying to avoid. You can have a string key, or a numeric key, but you can't have a numeric key pointing at a string key pointing at a value. That doesn't make sense. — user229044
– user229044 ♦, Commented Mar 8, 2014 at 22:07
Oops sorry, I just made that up to show what I was aiming for, but I forgot to delete part of the output I was getting. What I meant was that I just want an array with 0 => firstID, 1 => 2ndID and so on... — ReinaDelSur
– ReinaDelSur, Commented Mar 8, 2014 at 22:53

Amal · Accepted Answer · 2014-03-08 22:26:30Z

1

Let's create a multi-dimensional array just for testing purposes:

$array = array(
    array('foo' => 'value1'),
    array('foo' => 'value2')
);

Now consider the following code:

$result = array();

while ($row = array_shift($array)) {
    // $row will contain one nested array at a time
    $result[] = $row;
}

print_r($result);

$row is an array, and with the statement $result[] = $row;, you're pushing the entire array to $result thereby making it multi-dimensional.

This would output:

Array
(
    [0] => Array
        (
            [foo] => value1
        )

    [1] => Array
        (
            [foo] => value2
        )

)

Now, consider the following code:

$result = array();

while ($row = array_shift($array)) {
    // $row will contain one nested array at a time
    $result[] = $row['foo'];
}

print_r($result);

With the statement $result[] = $row['foo'];, we are pushing the value $row['foo'] to the $result array. At the end of loop iteration, $result will hold an array of foo row values.

This would output:

Array
(
    [0] => value1
    [1] => value2
)

Demo

To fix your original code, you can modify it like so:

$medias[] = $data['media_idMedia'];

edited Mar 8, 2014 at 22:26

answered Mar 8, 2014 at 22:04

Amal

76.8k18 gold badges133 silver badges154 bronze badges

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

1 Comment

ReinaDelSur Over a year ago

Thank you for such a detailed explanation! Works like a charm :D

Mubo · Accepted Answer · 2014-03-08 22:21:50Z

1

The parameter PDO::FETCH_ASSOC tells PDO to return the result as an associative array.

PDO::FETCH_ASSOC will return an array as shown in your code., except that if there are multiple columns with the same name, the value referred to by that key will be an array of all the values in the row that had that column name

I think this line of code

while ($data = $req -> fetch (PDO::FETCH_ASSOC)){ }
$data = $req -> fetch (PDO::FETCH_ASSOC)
 `$data`, itself is returned as an array.

This means you don't need to define another array just use $data

Something like

 $req = $this -> _db -> query ('SELECT media_idMedia FROM mObject_has_media WHERE mObject_idmObject = ' . $object -> id ());
    while ($data = $req -> fetch (PDO::FETCH_ASSOC)) {
        $data['media_idMedia']; //for media id.
    }

answered Mar 8, 2014 at 22:21

Mubo

1,0808 silver badges16 bronze badges

2 Comments

ReinaDelSur Over a year ago

Mmmmmmmm maybe I'm doing something wrong, but when I var_dump the contents of $data['media_idMedia'] after the while loop, I get a null / a boolean at false (I don't remember which, I've been var_dumping stuff all evening to check whether my script was behaving correctly...)

Edward Over a year ago

For anyone wondering, ->fetchAll(PDO::FETCH_COLUMN); will fetch all the rows and put all the rows as strings in one array, instead of a multidimensional array

Collectives™ on Stack Overflow

Why is my code making PDO return nested arrays?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related