代码之家 › 专栏 › 技术社区 › M S

显示加入两个表的所有可用视频

join php

M S · 技术社区 · 2 年前

我有两张桌子 news 和 video new_id

桌子

身份证件
1.		视频
2.

桌子 视频

身份证件	视频_名称
1.	视频_名称
2.	视频名称2
3.		2.

我的代码每次只显示一个视频 新建_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 class="col-md-4">
            <!-- Card item START -->
            <div class="card">
                <!-- Video -->
                <div class="card-image">
                    <div class="overflow-hidden w-100">
                        <!-- HTML video START -->
                        <div class="player-wrapper overflow-hidden">
                            <video class="player-html" controls crossorigin="anonymous">
                                <source src="../uploads/<?= $video[$new['id']] ?>" type="video/mp4">
                            </video>
                        </div>
                        <!-- HTML video END -->
                    </div>
                </div>
                <div class="card-body px-0 pt-3">
                    <h5 class="card-title"><a href="post-single-3.html" class="btn-link text-reset fw-bold"><?= $new['title'] ?></a></h5>
                    <!-- Card info -->
                    <ul class="nav nav-divider align-items-center d-none d-sm-inline-block">
                        Mar 02, 2022
                    </ul>
                </div>
            </div>
            <!-- Card item END -->
        </div>
        <!-- HTML5 player video post END -->
<?php

    }
}
?>

1 回复 | 直到 2 年前

Barmar 2 年前

你应该 $video[$id] 所有视频的数组,然后循环播放。

对于索引为的视频,不应使用相同的数组 news.id 以及所有视频的列表,因为新闻ID将与数组索引冲突。我变了 $video[] $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 class="col-md-4">
            <!-- Card item START -->
            <div class="card">
                <?php foreach($video[$new['id']] as $v) { ?>
                    <!-- Video -->
                    <div class="card-image">
                        <div class="overflow-hidden w-100">
                            <!-- HTML video START -->
                            <div class="player-wrapper overflow-hidden">
                                <video class="player-html" controls crossorigin="anonymous">
                                    <source src="../uploads/<?= $v ?>" type="video/mp4">
                                </video>
                            </div>
                            <!-- HTML video END -->
                        </div>
                    </div>
                <?php }
                <div class="card-body px-0 pt-3">
                    <h5 class="card-title"><a href="post-single-3.html" class="btn-link text-reset fw-bold"><?= $new['title'] ?></a></h5>
                    <!-- Card info -->
                    <ul class="nav nav-divider align-items-center d-none d-sm-inline-block">
                        Mar 02, 2022
                    </ul>
                </div>
            </div>
            <!-- Card item END -->
        </div>
        <!-- HTML5 player video post END -->
<?php

    }
}
?>