group by and make new array-CodePudding

I know how to work with arrays but i am stuck on this one. I can't see how to build. I tried a lot of things but nothing worked as aspected.

My mysql table looks like this:

uid   pid   stock
 1     1     0
 1     2     0
 2     3     0

The output array i need is like this:

Array
(
    [0] => Array
        (
            [uid] => 1
            [items] => Array
            (
              [0] => 1
              [1] => 2
            )
        )

    [1] => Array
        (
            [uid] => 2
            [items] => Array
            (
              [0] => 3
            )
        )

)

I used a mysqli class but that is not important here. What i did so far:

$db->join("users u", "w.uid=u.id", "LEFT");
$db->join("products p", "w.pid=p.id", "LEFT");
$db->where('w.uid IS NOT NULL');
$db->groupBy('uid');
$wishlist = $db->get("wishlist w", null, "w.uid");

foreach($wishlist as $w2) {
  
  $array[] = array(
      'uid' => $w2['uid'],
      'items' => $w2['pid'],
  );  
 
}
 
echo '<pre>';
print_r($array);
echo '</pre>';

Output is:

Array
(
    [0] => Array
        (
            [uid] => 1
            [items] => 2
        )

    [1] => Array
        (
            [uid] => 2
            [items] => 3
        )

)

CodePudding user response：

Assuming items is the same as pid and you load the datat from the database into a $rows array.

$array = [];
foreach ($rows as $row)
{
    $uid = $row['uid'];
    $array[$uid]['uid'] = $uid;
    
    if (!isset($array[$uid]['items'])
        $array[$uid]['items'] = [];

    $array[$uid]['items'][] = $row['pid'];
}

// reindex keys
$array = array_values($array);

CodePudding user response：

@Torsetha, thanks for your answer, it is working now.

Full code:

$db->join("users u", "w.uid=u.id", "LEFT");
$db->join("products p", "w.pid=p.id", "LEFT");
$db->where('w.uid IS NOT NULL');
$wishlist = $db->get("wishlist w", null, "w.uid, w.pid");

foreach($wishlist as $w2) {
  
    $uid = $w2['uid'];
    $array[$uid]['uid'] = $uid;
    
    if (!isset($array[$uid]['items'])) {
        $array[$uid]['items'] = [];
    }

    $array[$uid]['items'][] = $w2['pid'];
  
}

echo '<pre>';
print_r(array_values($array));
echo '</pre>';