php - 如何从数据库中选择数据并在重复时设置为单行

Question

我想要的是当我从数据库中选择时将重复值设置为一行。

根据我的结果，流派列上的数据不在一行中。

那么在流派这一列，如何将流派数据设置成一行呢？

例如一行中的结果:Drama, Western, Romance

表(电影类型):

表(类型):

结果:

PHP/HTML 代码:

<?php
 $categorySql = "SELECT DISTINCT movie.id,movie.name,genre.type,movie.price,movie.language,movie.subtitle,movie.director,movie.cast,movie.runningtime FROM movie INNER JOIN movie_genre ON movie.id = movie_genre.movie_id INNER JOIN genre ON genre.id = movie_genre.genre_id";

 if ($categoryStmt = $conn->prepare($categorySql)) {
/* execute query */
$categoryStmt->execute();

 $categoryStmt->bind_result($id, $name, $genre, $price, $language, $subtitle, 
$director, $cast, $time);


$rows = array();


while ($categoryStmt->fetch()) {
    $row = array(
        'id' => $id,
        'name' => $name,
        'genre' => $genre,
        'price' => "RM " . $price,
        'language' => $language,
        'subtitle' => $subtitle,
        'director' => $director,
        'cast' => $cast,
        'runningtime' => $time . " mins"

    );
    $rows[] = $row;
}
}
$categoryStmt->close();
?>

<div class="movielisttable">
<table id="movielist_table">
    <tr id="">
        <th>ID:</th>
        <th>Movie Name</th>
        <th>Genre</th>
        <th>Price</th>
        <th>Language</th>
        <th>Subtitle</th>
        <th>Director</th>
        <th>Cast</th>
        <th>Running Time</th>
    </tr>
    <tbody>
    <?php
    foreach ($rows as &$value) {
        echo
        "<tr>
          <td>{$value['id'] }</td>
          <td>{$value['name']}</td>
          <td>{$value['genre']}</td>
          <td>{$value['price']}</td>
          <td>{$value['language']}</td>
          <td>{$value['subtitle']}</td>
          <td>{$value['director']}</td>
          <td>{$value['cast']}</td>
          <td>{$value['runningtime']}</td>

        </tr>\n";
    }
    ?>

Answer 1

可能是您正在寻找 group_concat 和 group by

  $categorySql = "SELECT 
        movie.id
        ,movie.name
        , group_concat(genre.type) as type
        ,movie.price
        ,movie.language
        ,movie.subtitle
        ,movie.director
        ,movie.cast
        ,movie.runningtime 
  FROM movie 
  INNER JOIN movie_genre ON movie.id = movie_genre.movie_id 
  INNER JOIN genre ON genre.id = movie_genre.genre_id
  GROUP BY  movie.id ";