php - How to turn flat MySQL data into a multidimensional array -

i got data mysql select query...

+--------+----------+---------+--------------+----------------+ | vid_id | vid_name | file_id | file_url     | tag_name       | +--------+----------+---------+--------------+----------------+ |      1 | video 1  |       1 | video_1.mp4  | lorem ipsum    | |      1 | video 1  |       2 | video_1.webm | lorem ipsum    | |      2 | video 2  |       3 | video_2.mp4  | dolor sit amet | |      2 | video 2  |       4 | video_2.webm | dolor sit amet | +--------+----------+---------+--------------+----------------+ 

... i'm trying multidimension php array so:

array (     [0] => array         (             [name] => video 1             [tag] => lorem ipsum             [files] => array                 (                     [0] => video_1.mp4                     [1] => video_1.webm                 )         )      [1] => array         (             [name] => video 2             [tag] => dolor sit amet             [0] => array                 (                     [0] => video_2.mp4                     [1] => video_2.webm                 )         ) ) 

i close when use use variable check if video name has been added array. however, can't figure out how add video file nested array on next iteration. best attempt far:

<?php     // define variable keep track of iterations:     $title = '';      while ($row = mysqli_fetch_assoc($r)) {          if ($title != $row['vid_name']) {             // grab data:             $video_data[] = array(                 'name' => $row['vid_name'],                 'tag' => $row['tag_name'],                 $files = array(                     'file' => $row['file_url']                 )             );         } else {             // grab file url , add files array:             $files[] = $row['file_url'];         }          // update tracking variable:         $title = $row['vid_name'];     } ?> 

this doesn't work:

array (     [0] => array         (             [name] => video 1             [tag] => lorem ipsum             [0] => array                 (                     [file] => video_1.mp4                 )         )      [1] => array         (             [name] => video 2             [tag] => dolor sit amet              [0] => array                 (                     [file] => video_2.mp4                 )         ) ) 

i'm sure i'm trying achieve possible don't have enough knowledge of php figure 1 out (and suspect there better ways array want). suggestions , pointers appreciated!

why not use vid_id or vid_name (if unique) first array key?

check if vid_id exists in array, if not, add details, , add every file corresponding vid_id under key.

$video_data = array(); while ($row = mysqli_fetch_assoc($r)) {      $key = $row['vid_id'];      // add details if don't exist yet     if (!isset($video_data[$key])) {         $video_data[$key] = [             'name' => $row['vid_name'],              'tag' => $row['tag_name']         ];     }     // add file     $video_data[$key]['files'][] = $row['file_url']; }