获取电影类型的输出

Question

我正在编写一个程序来输出具有该类型的电影。

sample database

Movie        movie_id , title (1, snitch)

genre        genre_id , genre (1, Action)(2, Animation)(3, Drama)(4, Crime )

movie_genre  movie_id, genre_id (1,1)(1,3)(1,4)

这是我的代码

  <?php 

  $stmt = $pdo->query("

  SELECT * 

   FROM movie, genre, movie_genre WHERE movie.movie_id = 

   movie_genre.movie_id AND genre.genre_id = movie_genre.genre_id And 

   movie.name='snitch'

  ");

   while ($row = $stmt->fetch()){

   $title= $row['title'];

   $genre= $row['genre'];

   ?>

        <h1> <?php echo $title ; ?> </h1>

            <h1> <?php echo $genre ; ?> </h1>

    <?php } ?>

我得到的输出是“Snitch Action Snitch Drama Snitch Crime”

但我想要“告密行动戏剧犯罪”

Answer 1

每个 $row 包含标题（“Snitch”）和一种类型。你循环这个，所以它与每一行的标题相呼应。试试这个替换：&nbsp; &nbsp;$oldtitle = $title = '';&nbsp; &nbsp;while ($row = $stmt->fetch()){&nbsp; &nbsp;$title= $row['title'];&nbsp; &nbsp;if ( $oldtitle == $title ) $title = '';&nbsp; &nbsp;else&nbsp; $oldtitle = $title;&nbsp; &nbsp;$genre= $row['genre'];&nbsp; &nbsp;?>&nbsp; &nbsp; &nbsp; &nbsp; <h1> <?php echo $title ; ?> </h1>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <h1> <?php echo $genre ; ?> </h1>&nbsp; &nbsp;<?php } ?>

Answer 2

您可以使用PDO 的常量和方法fetchAll()。试试PDO::FETCH_GROUP。它将按第一列对查询中的所有结果进行分组。在这种情况下，我只获取 2 列title和genre. 结果是：Array (&nbsp;&nbsp; &nbsp; [snitch] => Array (&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; [0] => Action&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; [1] => Drama&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; [2] => Crime&nbsp;&nbsp; &nbsp; )&nbsp;)我将它与它混合PDO::FETCH_COLUMN以在我的子数组中只获取一列，但您不需要这样做，您可以获得整个数组。$stmt = $pdo->query("SELECT title, genre&nbsp; &nbsp; FROM movie, genre, movie_genre&nbsp;&nbsp; &nbsp; WHERE movie.movie_id = movie_genre.movie_id&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; AND genre.genre_id = movie_genre.genre_id&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; And movie.title='snitch'");$data = $stmt->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_COLUMN);foreach($data as $title => $genres){&nbsp; &nbsp; echo '<h1>'.$title.'</h1>';&nbsp; &nbsp; foreach($genres as $genre){&nbsp; &nbsp; &nbsp; &nbsp; echo '<h2>'.$genre.'</h2>';&nbsp; &nbsp; }}

Answer 3

在选择部分您没有提到任何字段。你可以用 2 种方式。SELECT movie.movie_id , movie.title , genre.genreFROM movie, genre, movie_genre WHERE movie.movie_id =&nbsp;movie_genre.movie_id AND genre.genre_id = movie_genre.genre_id And&nbsp;movie.name='snitch'或者SELECT movie.movie_id , movie.title , genre.genreFROM movie&nbsp;INNER JOIN movie_genre ON movie.movie_id = movie_genre.movie_idINNER JOIN genre ON genre.genre_id = movie_genre.genre_id&nbsp;WHERE movie.name='snitch'