array structure of database results

Question

This might be quite a trivial question, but which of these methods is best practice for structuring an array for return db results? say a list of blog posts... Sorting and grouping posts? or sorting and grouping elements?

Array
(
    [title] => Array
        (
            [0] => Untitled
            [1] => Untitled
        )

    [id] => Array
        (
            [0] => 8
            [1] => 11
        )

)

or

Array
(
    [0] => Array
        (
            ['id']=> 8
            ['title'] => Untitled
        )

    [1] => Array
        (
            ['id']=> 11
            ['title'] => Untitled
        )

)

The first way seems the easiest and the way I have been doing it. I can simply:

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $post['title'][] = $row['title'];
    $post['id'][] = $row['id'];
}

and then

$count = count($post['id']);

but the second way seems to make better sense and structure the information more logically. It is just a little more complicated to set up, filter and work with in the template.

$c = 0;
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $post[$c]['title'] = $row['title'];
    $post[$c]['id'] = $row['id'];
    $c++;
}

Answer 1

The second is better AND simpler to set up:

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $posts[] = $row;
}

// total number of posts
$nrPosts = count($post);

This will auto-index the array numerically, and you don't need to copy anything from $row into $post, it'll simply copy the whole row.

The second structure is better:

• It logically groups the data of a post into a structure
• It requires no handling of the data, more than the initial filling of the $post-array
• You can easily handle any number of blog posts with a single index / range of indexes

Answer 2

No use making a data structure that doesn't look right when you output it. The second data set it also way more usable which is why it is used in bigger projects like Wordpress.