如果有多个作者[重复]，我该如何阻止php显示该书两次？

Question

This question already has an answer here:

• How to limit values when using distinct 4 answers

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>

As you can see from the image it shows the book twice with two authors, as you know books can have many authors so i am trying to figure out how it would just show one or the first one it finds related to that book.

</div>

Answer 1

I believe this can be achieved using GROUP_CONCAT. Check out this tutorial to get your head around it. EDIT, this might be a better explantaion.

So I guess it would be:

    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