Loop through associative array resulting from mysqli_fetch_assoc

I am new in the web development family and I really need help on that problem. The requirement is to get a list of video with their tags. I have 3 tables in the DB – one for videos, one for tags, and one intermediate table video_tags.

Here is my query to get datas from DB tables in order to list videos with their tags:

$query = "SELECT * FROM video_tags VT
        INNER JOIN videos V ON V.id_video = VT.id_video 
        INNER JOIN tags T ON T.id_tag = VT.id_tag
        ORDER BY VT.id_video";

Then I execute this query:

$prepare = mysqli_prepare( $connexion, $query );
mysqli_stmt_execute( $prepare );
$item = mysqli_stmt_get_result( $prepare );
$item_row = mysqli_fetch_assoc( $item );
$item_numrows = mysqli_num_rows( $item );

So I got an associative array $item_row. Finally, I organize all the data in html table with a do/while loop:

<tbody>
 <?php
   if( $item_numrows > 0 ){
     $cnt = 0;                        
        do{                        
 ?>
 <tr id="id_video_tag_<?= $item_row["id_video_tags"]?>">
    <th scope="row"><?= $item_row["id_video"]?></th>
    <td><?= $item_row["video_title"]?></td>
    <td><?= $item_row["tag"]?></td>
 </tr>
 <?php
    $cnt++;
    }
   while($item_row = mysqli_fetch_assoc( $item ))
  }
 ?>
</tbody>

However, the list has repetitive lines coz’ there are several tags for one video so you will get as many lines for one video as the tags that video has. I tried a foreach loop on the associative array.

do{
 foreach ($item_row as $key => $value){

But when I dumps information on the variable, I can see that the $key lists all the fields available from the DB in a row for all the 374 rows, and $value lists all the values in succession too. Exemple of the var_dump($item_row)

array(18){["id_video_tags"]=> int(435) ["id_video"]=> int(143) ["id_tag"]=> int(12) ["video_title"]=> string(16) "" ["video_description"]=> string(0) "" ["video_url"]=> string(45) ["tag"]=> string(0) "" } 
array(18){["id_video_tags"]=> int(503) ["id_video"]=> int(143) ["id_tag"]=> int(50) ["video_title"]=> string(16) "" ["video_description"]=> string(0) "" ["video_url"]=> string(45) ["tag"]=> string(0) "" }

Is it possible to get an associative array like the following one:

$item = [
 [
  "id_video_tags" => 435,
  "id_video => 143,
  ...
  "tag" => "",
 ],
 [
  "id_video_tags" => 503,
  "id_video => 143,
  ...
  "tag" => "",
 ],

Otherwise, I don’t see how I can do to avoid lines repetition for each tag of each video.

Thanks a lot for an answer.

Answer

To solve this you can manipulate your data to get something like a “parent => child” relationship (movies as parents and tags as children). Like so

$query = "
    SELECT * FROM video_tags VT
    INNER JOIN videos V ON V.id_video = VT.id_video 
    INNER JOIN tags T ON T.id_tag = VT.id_tag
    ORDER BY VT.id_video
";

$prepare      = mysqli_prepare($connexion, $query);
mysqli_stmt_execute($prepare);
$stmt         = mysqli_stmt_get_result($prepare);

$videos = [];
while($row=mysqli_fetch_assoc($stmt)){
    if( !isset($videos[$row['id_video']]) ){
        $videos[$row['id_video']] = [
            'id_video'          => $row['id_video'],
            'video_title'       => $row['video_title'],
            'video_description' => $row['video_description'],
            'video_url'         => $row['video_url'],
            'tags'              => []
        ];
    }

    $videos[$row['id_video']]['tags'][] = [
        "id_video_tags" => $row['id_video_tags'],
        "id_tag"        => $row['id_tag'],
        "tag"           => $row['tag']
    ];
}

// print_r($videos);

And use it like so

<tbody>
    <?php foreach($videos as $video): ?>
        <tr id="id_video_<?= $video["id_video"]; ?>">
           <th scope="row"><?= $video["id_video"]; ?></th>
           <td><?= $video["video_title"]; ?></td>
           <td><?= implode(', ', array_column($video["tags"], 'tag')); ?></td>
        </tr>
    <?php endforeach; ?>
</tbody>

Advertisement

Answer