Same Id Different product from SQL output in one line using PHP-CodePudding

Same Id but multiple different product in SQL. Data should be retrieved from SQL and Output should be shortened to one line for each ID using PHP.

EXAMPLE:

SQL

ID	Product
001	Laptop
001	Monitor
001	Speaker
002	Phone
003	Other Services

Expected Output

PHP

ID	Product
001	Laptop, Monitor, Speaker
002	Phone, Case
003	Other Services

MY CODE

$sql = "SELECT id, product From Stock";
$result = mysqli_query($conn, $sql);

 while ($row = $result->fetch_array()){
        $id[] = $row["id"];
        $product[] = $row["product"];
}

$max_id = count($id);
$duplicate_id = array();

for($i=0; $i<$max_id;$i  ){

    $duplicate_m[$id[$i]] = $id[$i] = $product[$i];
}
print_r($duplicate_m);

CURRENT OUTPUT

[001] => Laptop
[002] => Phone
[003] => Other Services

CodePudding user response：

change your code to this

for($i=0; $i<$max_id;$i  ){
  if(!isset($duplicate_m[$id[$i]])){
    $duplicate_m[$id[$i]] = $id[$i] = $product[$i];
  }else{
    $duplicate_m[$id[$i]] .= ", {$product[$i]}";
  }
}

CodePudding user response：

Depending on your version of sql server, you can use string_agg function

$sql = "select id, string_agg(product, ',') from Stock group by id"

Should return your data in the way you want, without transformations in PHP code.

For other versions you can check this other question in SO with the needed code => ListAGG in SQLSERVER

CodePudding user response：

You use associative array, and override the values of the $duplicate_m array. You should check if key exists, and then save the element. Also, you should use multidimensional array. So

for($i=0; $i<$max_id;$i  ){
   if(!isset($duplicate_m[$id[$i]])){
       duplicate_m[$id[$i]] = [];
       duplicate_m[$id[$i]][] = $product[$i];
   } else {
       duplicate_m[$id[$i]][] = $product[$i];
   }
}