php - 如何关联 php mysql 中不同表的值并显示这些值？

Question

下面是将我的 php 代码连接到 mysql 的代码。我想显示 Actor 的所有电影，例如 actor_name 将显示与其相关的所有电影..你们能帮我解决这个问题吗？这个输出是当我搜索 Actor 未找到时..您可以检查一下是否我在变量 q4 中的代码是正确的，谢谢。

actor table

电影表

电影类型转换

我想要的是显示 actor_no 1 和 2 主演的所有电影..谢谢

<?php
$dbc = @mysqli_connect('localhost', 'root', 'black98765', 'activity_7b')
OR die("Could not connect to MySQL: " . mysqli_connect_error());
if(isset($_POST['submit'])){
$actor_query = "SELECT * FROM actor where name='$actor'";

$actor_result = mysqli_query($dbc, $actor_query);

$actor_data = mysqli_fetch_array($actor_result);

$actor_no = null;

// Check if actor already exists
if (isset($actor_data['actor_no'])) {
    $actor_no = (int)$actor_data['actor_no'];
} else {
    // Actor does not exist in the table, so we will create a new entry
    //insert into actor table
    $q = "INSERT INTO actor (name) VALUES ('$actor')";
    //execute the query to get primary key value
    $r = mysqli_query($dbc, $q);

    if ($r) {
        echo "Inserted actor successfully!";
        //assign to variable below
        $actor_no = mysqli_insert_id($dbc);
    } else {
        echo "Failed to insert actor data!";
        mysqli_error($dbc);
    }
}
//insert into movie table
$movie_query = "SELECT * FROM movie where movie_name='$movie'";

$movie_result = mysqli_query($dbc, $movie_query);

$movie_data = mysqli_fetch_array($movie_result);

$movie_no = null;
// Check if movie already exists
if (isset($movie_data['movie_no'])) {
    $movie_no = $movie_data['movie_no'];
} else {
    //movie does not exist in the table, so we will create a new entry
    //insert into actor table
    $q2 = "INSERT INTO movie (movie_name, release_year) VALUES ('$movie',DATE_FORMAT('$year','%Y-%m-%d'))";
    //execute the query to get primary key value
    $r2 = mysqli_query($dbc, $q2);

    if ($r2) {
        echo "Inserted move successfully!";
        //assign to variable below
        $movie_no = mysqli_insert_id($dbc);
    } else {
        echo "Failed to Insert Data!";
        mysqli_error($dbc);
    }


}
if (null !== $movie_no && null !== $actor_no) {
    $q3 = "INSERT INTO movie_actor (movie_no, actor_no, rate) VALUES ($movie_no, $actor_no, '$rate')";

//connect and insert $q
    $r3 = mysqli_query($dbc, $q3);
    if ($r3) {
        echo "Inserted Successfully!";
    } else {
        echo "Failed to Insert Data!";
        mysqli_error($dbc);
    }

} else {
    echo "Failed to Insert Data!";
}
}
if(isset($_POST['search'])){
     $q4 = "SELECT movie.movie_name,actor.name
            FROM movie 
            INNER JOIN actor

            WHERE movie_name = '$search_actor'";
     $r4 = mysqli_query($dbc,$q4);
     if($r4){
           while($row = mysqli_fetch_assoc($r4){
              echo "<p>Movies of Actor which he Starred In:</p>"    
              echo $row['movie_name']."<b/>";
           }
     }else{
          echo "actor not found!";
     }
}
mysqli_close($dbc);
?>

Answer 1

您需要使用联接，因为数据来自多个表

$q4 = "从 Actor 中选择电影.电影名称, Actor .名称
将 movie_actor 加入 actor.actor_no = movie_actor.actor_no
在 movie.movi​​e_no = movie_actor.movi​​e_no 上加入电影，其中 actor.name like '%$search_actor%';