TJohns.co

<?php
// Connect to the mysql server
$mysqli = new mysqli('localhost', $env['username'], $env['password'], $env['database']);
if ($mysqli->connect_errno) {
    echo 'Failed to connect';
}

echo $mysqli->host_info . "\n";

// SQL query to join 3 tables based on item ID.
// This will return one row for each permutation of data.
// Note that the column 'desc' in the OPs question has been replaced with 'description'
// to avoid a naming conflict with a MYSQL keyword.
$res = $mysqli->query("select distinct a.id, a.item, a.description, b.metakey, b.metavalue, c.name from a join b on a.id = b.item_id join c on a.id = c.item_id order by a.item"); 
print_r($res);

// Transform the mysql output which contains duplicate information
// into the desired data structure as specified in the OPs question.
$output = [];
while($row = $res->fetch_assoc()) {
        // We need to use the row ID (Item ID) to process the mysql rows.

       // Only add the full row if the Item ID has not previously been added.                                                   
        if (!isset($output[$row['id']])) {
            // Form the desired data structure
            $output[$row['id']] = [
                "DATA" => [
                    // The data array is an indexed array.
                    $row['item'],
                    $row['description'],
                ],
                "META" => [
                    // The meta array is an associative array and uses key value pairs.
                    $row['metakey'] => $row['metavalue'],
                ],
                // The extra array is an indexed array.
                "EXTRA" => [  
                    $row['name'],
                ],
            ]; 
        } 
        // Here we fill in the missing data from the partially duplicated mysql rows.
        // We drill down into the output array to check which keys have been added already,
        // and if it hasn't been added we add it.
        if (!isset($output[$row['id']]['META'][$row['metakey']])){
            $output[$row['id']]['META'][$row['metakey']] = $row['metavalue'];
        }
        // Same again, but a slightly different check. This time we are dealing with
        // an indexed array so we need to see if the value has been added.
        if (!in_array($row['name'], $output[$row['id']]['EXTRA'])) {
            $output[$row['id']]['EXTRA'][] = $row['name'];
        }
}

print_r($output);

Add comment