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

Question

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

what format column is the ID column? – Professor Abronsius Dec 01 '22 at 08:36 — Professor Abronsius, Dec 01 '22 at 08:36

score 0 · Answer 1 · answered Dec 01 '22 at 08:23

0

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]}";
  }
}

answered Dec 01 '22 at 08:23

Danz

252
1
8

score 0 · Answer 2 · edited Dec 01 '22 at 09:19

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];
   }
}

score 0 · Answer 3 · answered Dec 01 '22 at 08:29

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

score 0 · Answer 4 · answered Dec 01 '22 at 12:30

0

You can add the product to an array with id index in this way:

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

answered Dec 01 '22 at 12:30

user2272143

469
5
22

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

4 Answers4