结合的结果为一个,如果多个
问题描述:
我具有低于结合的结果为一个,如果多个
public function get_posts() {
$this->db->select('p.post_id, p.reply_id, p.user_id, u.username');
$this->db->from('post as p');
$this->db->join('user as u', 'u.user_id = p.user_id');
$this->db->where('p.post_id', $this->input->get('post_id'));
$this->db->or_where('p.reply_id', $this->input->get('post_id'));
// $this->db->group_by('p.user_id');
$query = $this->db->get();
return $query->result_array();
}
结果输出这两个功能
SELECT * FROM `post` WHERE `post_id` = '1' AND `reply_id` = '0' AND `user_id` = '2'
SELECT * FROM `post` WHERE `post_id` = '3' AND `reply_id` = '1' AND `user_id` = '1'
SELECT * FROM `post` WHERE `post_id` = '5' AND `reply_id` = '1' AND `user_id` = '1'
帖子总数功能
public function total_posts_by_user($user_id, $reply_id, $post_id) {
$this->db->from('post');
$this->db->where('post_id', $post_id);
$this->db->where('reply_id', $reply_id);
$this->db->where('user_id', $user_id);
// $this->db->group_by('user_id');
$query = $this->db->get();
if ($query->num_rows() > 0) {
return $query->num_rows();
}
}
的total_posts_by用户得到所示柱的数目in image below
问题正如你可以看到有2行管理信息显示。但我 希望他们合并,所以它只会说管行和2 职位。
我一直在使用上get_post但DOS GROUP_BY()无法正常工作
控制器
<?php
class Who_replied extends MX_Controller {
public function __construct() {
parent::__construct();
}
public function index() {
$data['posts'] = array();
$results = $this->get_posts();
foreach ($results as $result) {
$data['posts'][] = array(
'username' => $result['username'],
'total' => $this->total_posts_by_user($result['user_id'], $result['reply_id'], $result['post_id'])
);
$this->total_posts_by_user($result['user_id'], $result['reply_id'], $result['post_id']);
echo $this->db->last_query() . '</br>';
}
$data['total_posts'] = $this->total_posts();
return $this->load->view('default/template/forum/categories/who_replied_view', $data);
}
}
答
使用left
,因为目前它正在使用内部连接在第一种方法加入试过。之后使用群组
public function get_posts() {
$this->db->select('p.post_id, p.reply_id, p.user_id, u.username');
$this->db->from('post as p');
$this->db->join('user as u', 'u.user_id = p.user_id','left');
$this->db->where('p.post_id', $this->input->get('post_id'));
$this->db->or_where('p.reply_id', $this->input->get('post_id'));
$this->db->group_by('p.post_id');
$query = $this->db->get();
return $query->result_array();
}
并尝试。如果仍然面临同样的问题,请仔细检查是否有相同的TWO用户名admin
**您是否尝试过DISCTINCT
? 希望它将使用COUNT(p.user_id)占总帮助
+0
找到解决方案。谢谢你的时间 – user4419336
答
我发现我的解决方案从here
public function get_posts() {
$this->db->select('p.post_id, p.reply_id, p.user_id, u.username COUNT(p.user_id) AS total');
$this->db->from('post as p');
$this->db->join('user as u', 'u.user_id = p.user_id');
$this->db->where('p.post_id', $this->input->get('post_id'));
$this->db->or_where('p.reply_id', $this->input->get('post_id'));
$this->db->group_by('p.user_id');
$query = $this->db->get();
return $query->result_array();
}
有你只在第一种方法'get_posts试图'GROUP_BY( 'p.post_id')' ()'?此外,你可以尝试'DISTINCT' post_id –
@RejoanulAlam只是还是一样。 – user4419336
请检查我的回答 –