mysql result conversion to pivot table with php

Question

   products(prod_id, prod_name,...)
   attribute_key(attr_key_id, attr_name)
   attributes_values(attr_val_id, attr_key_id, value)
   product_attr_values(id,product_id,attr_val_id)

I have defined MYSQL result as an array definition. The problem is i have to concatenate same attribute with different values for the same product with comma(;). But i can get result without concatenating it. Help appreciated.

$shop = Array
(
"0" => Array
    (
        "id" => 9,
        "prodname" => 'Ramkinkar Baij',
        "attribute_name" => 'Author',
        "value" => 'sabsyasachi das'
    ),

"1" => Array
    (
        "id" => 10,
        "prodname" => 'Ramkinkar Baij',
        "attribute_name" => 'Author',
        "value" => 'Ela Datta'
    ),

"2" => Array
    (
        "id" => 11,
        "prodname" => 'Ramkinkar Baij',
        "attribute_name" => 'Editor',
        "value" => 'Sathi Basu'
    ),

"3" => Array
    (
        "id" => 12,
        "prodname" => 'Ramkinkar Baij',
        "attribute_name" => 'Editor',
        "value" => 'Radha Prasad Gupta'
    ),

"4" => Array
    (
        "id" => 13,
        "prodname" => 'Ramkinkar Baij',
        "attribute_name" => 'Place_of_Publication',
        "value" => 'Panagarh, Burdwan, West Bengal'
    ),

"5" => Array
    (
        "id" => 14,
        "prodname" => 'Rendava Prapancha Telugu Mahasabhalu, 1981',
        "attribute_name" => 'Author',
        "value" => 'sabsyasachi das'
    ),

"6" => Array
    (
        "id" => 15,
        "prodname" => 'Rendava Prapancha Telugu Mahasabhalu, 1981',
        "attribute_name" => 'Author',
        "value" => 'Ela Datta'
    ),

"7" => Array
    (
        "id" => 16,
        "prodname" => 'Rendava Prapancha Telugu Mahasabhalu, 1981',
        "attribute_name" => 'Translated_by',
        "value" => 'partha dasgupta'
    ),

"8" => Array
    (
        "id" => 17,
        "prodname" => 'Rendava Prapancha Telugu Mahasabhalu, 1981',
        "attribute_name" => 'Editor',
        "value" => 'Bandana Mukhopadhay'
    ),

"9" => Array
    (
        "id" => 18,
        "prodname" => 'Rendava Prapancha Telugu Mahasabhalu, 1981',
        "attribute_name" => 'Editor',
        "value" => 'Sathi Basu'
    ),

"10" => Array
    (
        "id" => 19,
        "prodname" => 'Rendava Prapancha Telugu Mahasabhalu, 1981',
        "attribute_name" => 'Translated_Title',
        "value" => '(Second World Telugu Conference, 1981)'
    ),

"11" => Array
    (
        "id" => 20,
        "prodname" => 'Rendava Prapancha Telugu Mahasabhalu, 1981',
        "attribute_name" => 'Place_of_Publication',
        "value" => 'Hyderabad'
    ),

"12" => Array
    (
        "id" => 21,
        "prodname" => 'Rendava Prapancha Telugu Mahasabhalu, 1981',
        "attribute_name" => 'Foreword',
        "value" => 'Information on Government organisations of Art and Culture in Andhra Pradesh'
    ),

"13" => Array
    (
        "id" => 22,
        "prodname" => 'Rendava Prapancha Telugu Mahasabhalu, 1981',
        "attribute_name" => 'Descriptive_Tags',
        "value" => 'Conference, Language'
    )

);


$groups = array();

foreach($shop as $key => $array){
//$type is not necessary, it's just for clarity below
$product = $array['prodname'];



if( !isset($groups[$product]) ){
    $groups[$product] = array();
    $groups[$product]['prod'] = $array['prodname'];
}


$groups[$product][$array["attribute_name"]] = $array['value'];

}
//then combine the groups into a master array
$out = array();
foreach($groups as $g){
$out[] = $g;
}
echo '<pre>'. print_r($out, true).'</pre>';
print "<pre>";
//print_r($groups);
print "</pre>";

That's a semicolon. But why do you have to concatenate anything? — Strawberry, Aug 16 '17 at 13:05
Nope. Statement B is not a necessary corollary of statement A. — Strawberry, Aug 16 '17 at 13:49
Likewise. See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Aug 16 '17 at 13:54
@Strawberry, Please find this [SQL fiddle](http://www.sqlfiddle.com/#!9/cc65ed/1) — Shuvadeep Chakraborty, Aug 16 '17 at 14:06
I suggest you re-read the accepted answer at the link provided. It recommends 3 things, of which you've provided 1. — Strawberry, Aug 17 '17 at 07:36
@Strawberry, provided all three points according to your link. viz, SQL fiddle, mysql resultset as an array `$shop` and also my effort. — Shuvadeep Chakraborty, Aug 17 '17 at 08:00
I guess I'd find it easier to follow if the result set was provided as a standard mysql result with columns and rows. — Strawberry, Aug 17 '17 at 11:03

score 2 · Answer 1 · answered Aug 16 '17 at 13:00

2

Use MySQLs GROUP BY and GROUP_CONCAT functions.

SELECT prod_name, GROUP_CONCAT(CONCAT(attr_name, ': ', value) SEPARATOR ', ') AS attributes FROM [your tables and joins] GROUP BY prod_id

answered Aug 16 '17 at 13:00

Björn Tantau

1,564
14
13

@tantau you are right but it's very difficult to fetch result as per my need from mysql. So i tried to convert with php. I need something like this `array("0"=> array('productName'=> 'x', 'author' => 'del Karnegie,William fulckner', 'editor'=> 'x,y,z', ))` Or May be like that `array("0"=>array('ProductName'=> 'X', 'author1'=> 'x', 'author2'=>'y', 'author3'=>'z'))` – Shuvadeep Chakraborty Aug 16 '17 at 13:22
@ShuvadeepChakraborty You could also return the attributes as JSON to better handle them in php. See https://stackoverflow.com/questions/12511933/how-create-json-format-with-group-concat-mysql. Newer MySQL versions even have native functions to create JSON. – Björn Tantau Aug 16 '17 at 14:05
Please do me a favor. can you review the efficacy of this query [SQLfiddle] (http://www.sqlfiddle.com/#!9/cc65ed/4) – Shuvadeep Chakraborty Aug 17 '17 at 05:59

mysql result conversion to pivot table with php

1 Answers1