php - 如果这本书有多个作者，我怎样才能阻止 php 显示两次

Question

这个问题已经有答案了:

How to limit values when using distinct (4 个回答)

已关闭 9 年前。

PHP

$sql =  "SELECT DISTINCT bk.title AS Title, bk.year AS Year, bk.publisher AS Publisher, aut.authorname AS Author 
         FROM book bk 

         JOIN book_category bk_cat 
         ON bk_cat.book_id = bk.bookid

         JOIN categories cat 
         ON cat.id = bk_cat.category_id

         JOIN books_authors bk_aut 
         ON bk_aut.book_id = bk.bookid

         JOIN authors aut
         ON aut.id = bk_aut.author_id";

if (isset($_GET['searchInput'])){

    $getters = array();
    $queries = array();

    foreach ($_GET as $key => $value) {
        $temp = is_array($value) ? $value : trim($value);
        if (!empty($temp)){
        if (!in_array($key, $getters)){
            $getters[$key] = $value;
            }
        }
    }

    if (!empty($getters)) {

        foreach($getters as $key => $value){
            ${$key} = $value;
            switch ($key) {
                case 'searchInput':
                    array_push($queries,"(bk.title LIKE '%$searchInput%' 
                    || bk.description LIKE '%$searchInput%' || bk.isbn LIKE '%$searchInput%' 
                    || bk.keywords LIKE '%$searchInput%' || aut.authorname LIKE '%$searchInput%')");
                break;
                case 'srch_publisher':
                    array_push($queries, "(bk.publisher = $srch_publisher)");
                break;
                case 'srch_author':
                    array_push($queries, "(bk_aut.author_id = $srch_author)");
                break;          
        }
    }
}

if(!empty($queries)){
    $sql .= " WHERE ";
    $i = 1;
    foreach ($queries as $query) {
        if($i < count($queries)){
            $sql .= $query." AND ";
        } else {
            $sql .= $query;
        }   
        $i++;
    }
}
$sql .= " ORDER BY bk.title ASC";

}

HTML

<?php if($tot_rows > 0) { ?>
            <table id="tbl_repeat">
                <tr>
                    <th scope="col" class="bookTitle">Book Title</th>
                    <th scope="col">Author</th>
                    <th scope="col">Publisher</th>
                </tr>
                <?php do{ ?>
                <tr>
                    <td class="bookTitle"><a href=""><?php echo $rows['Title']; ?></a></td>
                    <td><?php echo $rows['Author']; ?></td>
                    <td><?php echo $rows['Publisher']; ?></td>
                </tr>
                <?php } while ($rows = mysql_fetch_assoc($rs)); ?>
            </table>
            <?php } 
            else {
                if (!empty($queries)){
                    echo "<p>There are no records matching your search criteria.</p>";
                } else {
                    echo "<p>There are currently no records available.</p>";
                }
            }
            ?>
    </div>

正如您从图像中看到的，它显示了这本书两次，有两位作者，正如您所知，书籍可以有很多作者，所以我试图弄清楚它如何只显示与该书相关的一个或第一个作者.

Answer 1

我相信这可以使用 GROUP_CONCAT 来实现。查看this教程来帮助您了解它。编辑，this可能是一个更好的解释。

所以我想应该是:

    SELECT DISTINCT bk.title AS Title, bk.year AS Year, bk.publisher AS Publisher, GROUP_CONCAT(aut.authorname) AS Author 
         FROM book bk 

         JOIN book_category bk_cat 
         ON bk_cat.book_id = bk.bookid

         JOIN categories cat 
         ON cat.id = bk_cat.category_id

         JOIN books_authors bk_aut 
         ON bk_aut.book_id = bk.bookid

         JOIN authors aut
         ON aut.id = bk_aut.author_id
GROUP BY bk.title, bk.year, bk.pulisher