php - How to get data from many to many relationship table and print all rows in one line PHP
问题描述
I am currently working on a library management system as a project and I have been struggling with many to many relationship tables. So, in my database, I got books, book_has_authors and authors table as shown in a picture below.
Picture of tables and relations between them
So, I have created view_books.php file where I am trying to list all the books from the database and it looks as follows:
<table class="table table-hover">
<!-- table table-striped table-bordered table-hover begin -->
<thead>
<!-- thead begin -->
<tr>
<!-- tr begin -->
<th scope="col"> ISBN: </th>
<th scope="col"> Book Title: </th>
<th scope="col"> Publisher: </th>
<th scope="col"> Publication Year: </th>
<th scope="col"> Category: </th>
<th scope="col"> Number of copies: </th>
<th scope="col"> Edit: </th>
<th scope="col"> Delete: </th>
</tr><!-- tr finish -->
</thead><!-- thead finish -->
<tbody>
<!-- tbody begin -->
<?php
$i = 0;
$get_books = "SELECT book.ISBN, book.bookTitle, book.bookPublisher, book.bookPublicationDate, bookcategory.categoryName FROM book INNER JOIN bookcategory ON book.bookCategory = bookcategory.categoryID ";
// $get_payments = "SELECT * FROM books";
$run_books = mysqli_query($conn, $get_books);
while ($book_row = mysqli_fetch_array($run_books)) {
$ISBN = $book_row['ISBN'];
$title = $book_row['bookTitle'];
$publisher = $book_row['bookPublisher'];
$pubdate = $book_row['bookPublicationDate'];
$cat_name = $book_row['categoryName'];
// get total number of copies for each book
$count_copies = "SELECT * FROM bookcopy WHERE ISBN = $ISBN ";
$run_count = mysqli_query($conn, $count_copies);
$copy_num = mysqli_num_rows($run_count);
// get all authors
$authors_array = array();
$get_authors = "SELECT author.authorsFullName FROM book INNER JOIN book_has_authors ON book_has_authors.book_ISBN = book.ISBN INNER JOIN author ON author.authorID = book_has_authors.authors_authorID WHERE ISBN=$ISBN;";
$run_authors = mysqli_query($conn, $get_authors);
while($author_row = mysqli_fetch_array($run_authors)){
$authors_array[] = $author_row;
}
$i++;
echo '<tr scope="row"><!-- tr begin -->
<td>' . $ISBN . '</td>
<td>' . $title . '</td>
<td>' . $publisher . '</td>
<td>' . $pubdate . '</td>
<td>' . $cat_name . '</td>
<td>' . $copy_num. '</td>
<td><a href="index.php?delete_product' . $ISBN . '>
<i class="fas fa-trash"></i> Delete
</a>
</td>
<td>
<a href="index.php?edit_product' . $ISBN . '>
<i class="fa fa-pencil"></i> Edit
</a>
</td>
</tr><!-- tr finish -->
';
}
?>
</tbody><!-- tbody finish -->
</table>
As this is many to many relationships, one book can have many authors and many authors can have one book. In the view_books.php I have created the while loop that iterates through all books and prints them in the table. At the moment I can get all data but not the authors as the query returns multiple rows(authors) for each book. As I mentioned before I would like to list all the books in the form of HTML table. Could someone explain to me please how can I concatenate all authors for each book and print them in a single table cell next to each other? That is what I would like to achieve:
解决方案
首先检查这个:($authors_array[] = $author_row;
你错过了 $author_row["authorsFullName"]
)。
然后你可以IMPLODE $authors_array
并且$stringAuthors
应该用于显示输出:
$stringAuthors = implode(',',$authors_array);
//Author1, Author2, Author3, etc...
implode — 用字符串连接数组元素
<?php
$array = array('lastname', 'email', 'phone');
$comma_separated = implode(",", $array);
echo $comma_separated; // lastname,email,phone
// Empty string when using an empty array:
var_dump(implode('hello', array())); // string(0) ""
?>
推荐阅读
- javascript - Intersection Observer 在 600px 视口 (+GSAP) 以下不工作
- c++ - 添加路径以包含路径
- java - 调用枚举的抽象方法
- php - PHP, jQuery (AJAX) - 刷新信息
- selenium-ide - 如何获取 Selenium IDE 中每个命令可用的定位器(目标)的完整列表?
- javascript - 渲染 Handlebars 模板时出现错误“unexpected char '#'”
- c++ - 奇怪的重复模板模式和无限递归
- python - 检查动态数组
- c# - 如何根据配置切换 EF Core 使用的数据库提供程序?
- sql-server - TFS GIT 获取完整错误 TF30042。tbl_content 已满