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.
Advertisement
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>