I have two tables news and video. Need to show all available videos per new_id

Table news

Table video

My code show me only one video per new_id

<?php
$stmt = $con->prepare('SELECT `id`, `title`, `services` FROM `news` ORDER BY `id` DESC');
$stmt->execute();
$stmt->bind_result($id, $title, $services);
while ($stmt->fetch()) {
    $news[] = ['id' => $id, 'title' => $title, 'services' => $services];
}

$stmt = $con->prepare('SELECT n.id, v.video_name FROM news AS n INNER JOIN video AS v ON n.id = v.new_id');
$stmt->execute();
$stmt->bind_result($id, $video_name);
while ($stmt->fetch()) {
    $video[] = ['id' => $id, 'video_name' => $video_name];
    $video[$id] = $video_name;
}

foreach ($news as $new) {
    $service = explode(", ", $new['services']);
    if (in_array('Video', $service)) {
?>
        <!-- HTML5 player video post START -->
        <div >
            <!-- Card item START -->
            <div >
                <!-- Video -->
                <div >
                    <div >
                        <!-- HTML video START -->
                        <div >
                            <video  controls crossorigin="anonymous">
                                <source src="../uploads/<?= $video[$new['id']] ?>" type="video/mp4">
                            </video>
                        </div>
                        <!-- HTML video END -->
                    </div>
                </div>
                <div >
                    <h5 ><a href="post-single-3.html" ><?= $new['title'] ?></a></h5>
                    <!-- Card info -->
                    <ul >
                        Mar 02, 2022
                    </ul>
                </div>
            </div>
            <!-- Card item END -->
        </div>
        <!-- HTML5 player video post END -->
<?php

    }
}
?>

CodePudding user response：

You should make $video[$id] an array of all the videos, then loop over them.

You shouldn't use the same array for the videos that are indexed by news.id and also the list of all videos, since the news IDs will conflict with the array indexes. I've changed $video[] to $all_video[]

$stmt = $con->prepare('SELECT n.id, v.video_name FROM news AS n INNER JOIN video AS v ON n.id = v.new_id');
$stmt->execute();
$stmt->bind_result($id, $video_name);
$all_video = [];
$video = [];
while ($stmt->fetch()) {
    $all_video[] = ['id' => $id, 'video_name' => $video_name];
    $video[$id][] = $video_name;
}

foreach ($news as $new) {
    $service = explode(", ", $new['services']);
    if (in_array('Video', $service)) {
?>
        <!-- HTML5 player video post START -->
        <div >
            <!-- Card item START -->
            <div >
                <?php foreach($video[$new['id']] as $v) { ?>
                    <!-- Video -->
                    <div >
                        <div >
                            <!-- HTML video START -->
                            <div >
                                <video  controls crossorigin="anonymous">
                                    <source src="../uploads/<?= $v ?>" type="video/mp4">
                                </video>
                            </div>
                            <!-- HTML video END -->
                        </div>
                    </div>
                <?php }
                <div >
                    <h5 ><a href="post-single-3.html" ><?= $new['title'] ?></a></h5>
                    <!-- Card info -->
                    <ul >
                        Mar 02, 2022
                    </ul>
                </div>
            </div>
            <!-- Card item END -->
        </div>
        <!-- HTML5 player video post END -->
<?php

    }
}
?>