代码启动器帖子和类别

Question

我正在做一个codeigniter项目，我对codeigniter是个新手，但现在对它有了一定程度的了解。我正在为uni构建一个简单的应用程序，我在尝试显示哪些项目属于哪些类别时遇到了问题。

我的数据库结构

|------------------|  |--------------------|  |---------------------------------|
| books            |  | book_categories    |  | book_linktable                  |
|------------------|  |--------------------|  |---------------------------------|
| book_id          |  | book_category_id   |  | book_linktable_book_id          |
| book_name        |  | book_category_name |  | book_linktable_book_category_id |
| book_description |  |--------------------|  |---------------------------------|
| book_grade       |
| book_author      |
| book_edition     |
| book_publisher   |
| book_price       |
| book_image       |
|------------------|

好了，在我的模型中使用连接，我已经成功地从book_linktable中提取了我想要的数据，并且我已经在我的视图中使用了一个简单的foreach循环来显示它。

问题是，假设一本书属于两个或更多类别，它在book_linktable中看起来像这样：

|------------------------|---------------------------------|
| book_linktable_book_id | book_linktable_book_category_id |
|------------------------|---------------------------------|
|           1            |                1                |
|           1            |                2                |
|------------------------|---------------------------------|

(所以这里的第一本书属于类别1和类别2)

然而，问题是，当我在视图中执行foreach循环时，它将它们显示为两本不同的书，如下所示：

|Name  | Grade   | Price | Categories   |
|---------------------------------------|
|Book1 | Grade 1 | £7.00 | Announcement |
|Book1 | Grade 1 | £7.00 | Event        |

我只需要知道如何将图书放在不同的类别中，而不是多次显示图书。如下所示：

|Name  | Grade   | Price | Categories          |
|----------------------------------------------|
|Book1 | Grade 1 | £7.00 | Announcement, Event |

下面是我的模型函数：

function get_books(){       
        $this->db->select('books.book_id, books.book_name, books.book_grade, books.book_edition, books.book_price, book_categories.book_category_id, book_categories.book_category_name');
        $this->db->from('book_linktable');
        $this->db->join('books', 'book_linktable.book_linktable_book_id = books.book_id');
        $this->db->join('book_categories', 'book_linktable.book_linktable_book_category_id = book_categories.book_category_id');

        $query = $this->db->get();
        return $query->result();
    } //function get_books()

下面是我的控制器函数：

function books($condition = FALSE){
        if($condition === TRUE OR $this->admin_model->logged_in() === TRUE)
        {
            $data = $this->admin_model->display();
            $data['query'] = $this->admin_model->get_books();
            $data['page_title'] = 'Books';
            $this->load->view('admin_section/books/main', $data);
        }
        else
        {
            $this->load->view('admin_section/details');
        }
    }  //function books()

这是我在视图中的foreach：

    <?php
      foreach($query as $row) {
        echo "<tr>";
        echo "<td>" . $row->book_name . "</td>";
        echo "<td>" . $row->book_grade . "</td>";
        echo "<td>" . $row->book_edition . "</td>";
        echo "<td>" . "&pound;" . $row->book_price . "</td>";
        echo "<td>" . $row->book_category_name . "</td>";
        echo "<td>" . anchor('books_admin/edit_book/' . $row->book_id, img(array('src'=>'/sys_icons/edit.png','border'=>'0','alt'=>'Edit'))) . " ";
echo "<td>" . anchor('books_admin/delete_book/' . $row->book_id, img(array('src'=>'/sys_icons/delete.png','border'=>'0','alt'=>'Delete', 'class' => 'delete_book'))) . "</td>";
        echo "</tr>";
      }
    ?>

任何帮助都将不胜感激

谢谢，汤姆

Answer 1

function getMyRequirements()
{
$query = "
select
  books.book_id,
  books.book_name,
  books.book_grade,
  books.book_edition,
  books.book_price,
  group_concat(book_categories.book_category_id) as book_category_id,
  book_categories.book_category_name
from book_linktable
  left join books
    on book_linktable.book_linktable_book_id = books.book_id
  left join book_categories
    on book_linktable.book_linktable_book_category_id = book_categories.book_category_id
group by books.book_id
";   

$query = $this->db->query($query);
return $query->result() ;
}

使用此查询。Group concat会给你一个逗号分隔的字段，你可以在php端分解它。

在控制器中

function getWhatiNeed(){
    $this->load->model('test');// The model where the getMyRequirements function is
    $result = $this->test->getMyRequirements();
    $data['result'] = $result;
    $this->load->view('my_view',$data);
}

现在，在视图中，当您循环结果时

if(count($result)>0)
    foreach($results as $row){
        $categories = explode (',',$row->book_category_id);

        foreach($categories as $category){
              echo  $category;
        }
    }