multi-array result in PHP object / Sql query

Question

I have an object called Index which has a property called _fletter (in this case a private property).

I have an sql query that fetches all distinct values from a table using the following query:

SELECT DISTINCT fletter FROM books ORDER BY fletter ASC

The way I call this query is through a private function that does all the database related stuff and returns a variable:

function getFirstLetter() {
    // Database stuff omitted
    $stmt->execute();
    $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $res;
}

The way I give the result to the _fletter property is as follows:

$this->_fletter = $this->getFirstLetter();

What ends up happening is that the property of $this->_fletter is an associative array that when print_r gives this:

[0] => Array
    (
        [fletter] => A
    )
    // etc.

I then want to use a foreach statement such that it iterates through every _fletter[x] where x corresponds to the letter in question and run the following sql statement

SELECT booktitle, isbn, pages FROM books WHERE booktitle = ':booktitle'

I then want to return the result like in the previous example:

$this->_fletter = $this->getBookTitles();

And, the idea is that I want the results of the new associate array to be added to the _fletter array where the First Letter corresponds to the new result set, something along the lines of:

[0] => Array
    (
        [fletter] => [A]
            (
                [1] => 'A time to kill'
                [2] => 'Almost Famous'
            )
    ) //etc - note I may not have written the syntax correct

So, the new query has to take the value of '_fletter', run the query and return the result in the same place or under the same array entry as that of the first letter. So I want the fletter to be the new key or whatnot of the new result array

If I simply add the new result to the _fletter array it will just append to the existing array and not use the value of [fletter] rather it will add new entries to the array:

[15] => Array
        (
            [fletter] => W
        )

    [A] => Array
        (
            [0] => Array
                (
                    [booktitle] => A Time to kill
                    [isbn] => 1234567890
                )

Any ideas how this can be achieved? I want to avoid placing the php in the html portion of the page, however, if there is no alternative, I might have to do that.

Answer 1

It's really hard you understand what you want. I think that you need only one query with one foreach

SELECT fletter, booktitle, isbn, pages FROM books

<?php
$fletters = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $index = $row['fletter'];
    unset($row['fletter']);
    $fletters[$index][] = $row;
}