How to create a multidimensional array when using prepared statements?

Question

i have a query that returns data like so:

releaseid | name | format |
---------------------------
1         | erbr | CD
1         | erbr | DVD
2         | name | CD
3         | test | CD
4         | yo   | CD
4         | yo   | CASETE
5         | hey  | 8 TRACK

Notice that some releaseid have more then 1 format.

In PHP, i'm using a prepared statement to initialize an array like so:

$conn = fn_connect(); // my function to connect to db
$q = 'SELECT ....';
$stmt = $conn->prepare($q);
$stmt->bind_param('i', $recordingid);
$stmt->execute();
$stmt->bind_result($id, $name, $format);
$stmt->store_result();
$num_rows = $stmt->num_rows;

if ( $num_rows > 0 ) {
    $releases = array();

    while ( $stmt->fetch() ) {
        $releases[$id] = array('releaseid'=>$id, 'releasename'=>$name, 'releaseformat'=>$format);
    }
}

$stmt->free_result();
$stmt->close();

As you can see, when i'm looping, i'm grouping the data by the releaseid and when a releaseid has more then 1 format, it only keeps the last format:

Array
(
    [1] => Array
        (
            [releaseid] => 1
            [releasename] => erbr
            [releaseformat] => DVD
        )

    [2] => Array
        (
            [releaseid] => 2
            [releasename] => name
            [releaseformat] => CD
        )
...
)

I'm trying to create a multidimensional array so i can keep all the formats of a releaseid

i've tried using the following, but again, it keeps the last format

while ( $stmt->fetch() ) {
        $releases[$id] = array('releaseid'=>$id, 'releasename'=>$name, array('format'=>$format));
    }

Answer 1

Try this one:

        $conn = new mysqli("host", "user", "pwd", "db");

        $q = 'SELECT ....';
        $stmt = $conn->prepare($q);
        $stmt->execute();
        $stmt_result = $stmt->get_result();
        $result = [];
        if ($stmt_result->num_rows > 0) {

            # Save in $row_data[] all columns of query
            while ($row_data = $stmt_result->fetch_assoc()) {

                if (isset($result[$row_data['id']])) {
                    $result[$row_data['id']]['releaseformat'][] = $row_data['format'];
                } else {
                    # Action to do
                    $result[$row_data['id']] = array('releaseid' => $row_data['id'], 'releasename' => $row_data['name'], 'releaseformat' => array($row_data['format']));
                }
            }

        } else {
            # No data actions
            echo 'No data here :(';
        }
        echo "<pre>";
        print_r($result);
        $stmt->close();

It's working for me, I hope it will work for you.

Answer 2

You need to check if the $id entry in $releases already exists, and if it does, merge the new format value with its existing releaseformat values. Something like this:

while ( $stmt->fetch() ) {
    if (isset($releases[$id])) {
        $releases[$id]['releaseformat'][] = $format;
    }
    else {
        $releases[$id] = array('releaseid'=>$id, 'releasename'=>$name, 'releaseformat'=>array($format));
    }
}

This will give you values that look like this:

[1] => Array
    (
        [releaseid] => 1
        [releasename] => erbr
        [releaseformat] => Array 
            (
                 [0] => CD
                 [1] => DVD
            )
    )