PDO Query fetchAll remove keys but keep values including duplicates

Question

I am trying to add custom fields to my presentation platform and have the following MySQL/PDO query:

SELECT presenters.presenter_name,
       presenters.presenter_email,
       presenters.presenter_contact,
       presentations.presentation_uid,
       presentations.presentation_presenter_notes,
       presentations.presentation_date,
       presentations.presentation_customer_reference,
       presentations.presentation_customer_name,
       presentations.presentation_customer_email,
       customfields.customfield_name,
       customfields_data.customfield_data_value
    FROM presentations 
    INNER JOIN presenters ON presentations.presentation_presented_by = presenters.presenter_id 
    LEFT JOIN customfields ON customfields.customfield_presentation_uid = presentations.presentation_uid 
    LEFT JOIN customfields_data ON customfields_data.customfield_data_id = customfields.customfield_id
    WHERE presentations.presentation_uid = :presentation_id

I execute the query with $presentation = $query->fetchAll(PDO::FETCH_ASSOC); and then print_r the results and get the following:

Array (
     [0] => Array
         (
        [presenter_name] => John Doe
        [presenter_email] => [email protected]
        [presenter_contact] => 0123456789
        [presentation_uid] => esljqpmdh
        [presentation_presenter_notes] => Great presentation
        [presentation_date] => 2015-06-05 14:17:15
        [presentation_customer_reference] => How to make a great presentation.
        [presentation_customer_name] => Doe John
        [presentation_customer_email] => [email protected]
        [customfield_name] => Favourite Colour
        [customfield_data_value] => Blue
    )

     [1] => Array
         (
        [presenter_name] => John Doe
        [presenter_email] => [email protected]
        [presenter_contact] => 0123456789
        [presentation_uid] => esljqpmdh
        [presentation_presenter_notes] => Great presentation
        [presentation_date] => 2015-06-05 14:17:15
        [presentation_customer_reference] => How to make a great presentation.
        [presentation_customer_name] => Doe John
        [presentation_customer_email] => [email protected]
        [customfield_name] => Age
        [customfield_data_value] => 26
         )
)

What I am trying to achieve is this so that I can iterate through the custom fields and add them in to my view template:

Array (
        [presenter_name] => John Doe
        [presenter_email] => [email protected]
        [presenter_contact] => 0123456789
        [presentation_uid] => esljqpmdh
        [presentation_presenter_notes] => Great presentation
        [presentation_date] => 2015-06-05 14:17:15
        [presentation_customer_reference] => How to make a great presentation.
        [presentation_customer_name] => Doe John
        [presentation_customer_email] => [email protected]
        [customfield_name] => Favourite Colour
        [customfield_data_value] => Blue
        [customfield_name] => Age
        [customfield_data_value] => 26
    )

or better still:

Array (
        [presenter_name] => John Doe
        [presenter_email] => [email protected]
        [presenter_contact] => 0123456789
        [presentation_uid] => esljqpmdh
        [presentation_presenter_notes] => Great presentation
        [presentation_date] => 2015-06-05 14:17:15
        [presentation_customer_reference] => How to make a great presentation.
        [presentation_customer_name] => Doe John
        [presentation_customer_email] => [email protected]
        [customfields] => Array (
                  [customfield_name] => Favourite Colour
                  [customfield_data_value] => Blue
                  [customfield_name] => Age
                  [customfield_data_value] => 26
         )
    )

But I'm not sure what to do next, I'm completely stuck and not sure which part of my code is incorrect. The query or the fetchAll.

Any help would be greatly appreciated.

Answer 1

Are you ok with associative arrays? Then create a new array (say $def_presentation or something ) and go for something like

foreach($presentation as $row) {
    if(!isset($def_presentation[$row['presentation_uid']])) {
        $def_presentation[$row['presentation_uid']] = $row;
        unset($def_presentation[$row['presentation_uid']]['customfield_name'],$def_presentation[$row['presentation_uid']]['customfield_data_value']);
        $def_presentation[$row['presentation_uid']]['customfields'] = array();
    } 
    $def_presentation[$row['presentation_uid']]['customfields'][$row['customfield_name']] = $row['customfield_data_value'];
}

Answer 2

Et voila ! See Demo

CODE :

$res_non_associative = Array (
 0 => Array
     (
    "presenter_name" => "John Doe",
    "presenter_email" => "[email protected]",
    "presenter_contact" => "0123456789",
    "presentation_uid" => "esljqpmdh",
    "presentation_presenter_notes" => "Great presentation",
    "presentation_date" => "2015-06-05 14:17:15",
    "presentation_customer_reference" => "How to make a great presentation.",
    "presentation_customer_name" => "Doe John",
    "presentation_customer_email" => "[email protected]",
    "customfield_name" => "Favourite Colour",
    "customfield_data_value" => "Blue"
),

 1 => Array
     (
    "presenter_name" => "John Doe",
    "presenter_email" => "[email protected]",
    "presenter_contact" => "0123456789",
    "presentation_uid" => "esljqpmdh",
    "presentation_presenter_notes" => "Great presentation",
    "presentation_date" => "2015-06-05 14:17:15",
    "presentation_customer_reference" => "How to make a great presentation.",
    "presentation_customer_name" => "Doe John",
    "presentation_customer_email" => "[email protected]",
    "customfield_name" => "Age",
    "customfield_data_value" => 26
     )
);

// You won't have to do that - Proccess only coz i couldn't get the     
// associative array from fetchAll()
$res = array();
array_push($res, array_values($res_non_associative[0]));
array_push($res, array_values($res_non_associative[1]));


// Array that will be returned
$final_array = array();


// Depending how the result will be put in array
// Regarding your query it seems you catched 2 by 2
// Therefore we need a cursor to add into the final_array
$cursor = 0;

for($i = 0; $i < count($res); $i += 2)
{
    // We will push into main array all infos except "customfields"
    $main_array = array();
    $max_main_insert = count($res[$i]) - 2;

    for($j = 0; $j < $max_main_insert; ++$j)
    {
        array_push($main_array, $res[$i][$j]);
    }
    array_push($final_array, $main_array);

    // We will push into custom array all the "customfield"
    $custom_array = array();

    array_push($custom_array, $res[$cursor][$max_main_insert]);
    array_push($custom_array, $res[$cursor][$max_main_insert +1 ]);

    array_push($custom_array, $res[$cursor + 1][$max_main_insert]);
    array_push($custom_array, $res[$cursor + 1][$max_main_insert +1 ]);

    array_push($final_array[$i], $custom_array);

    ++$cursor;
}


print_r($final_array);

OUTPUT :

Array
(
[0] => Array
    (
        [0] => John Doe
        [1] => [email protected]
        [2] => 0123456789
        [3] => esljqpmdh
        [4] => Great presentation
        [5] => 2015-06-05 14:17:15
        [6] => How to make a great presentation.
        [7] => Doe John
        [8] => [email protected]
        [9] => Array
            (
                [0] => Favourite Colour
                [1] => Blue
                [2] => Age
                [3] => 26
            )

    )
)