使用join（codeigniter）从单个字段中的逗号分隔符后检索和打印值

I have one problem while retrieveing value from mysql using codeigniter.

我使用codeigniter从mysql中检索值时遇到一个问题。

I have one table name task and in that there is one field of assigneduserid.

我有一个表名任务,并且有一个assigneduserid字段。

Table - Task:

表 - 任务:

id  | title     | assigneduserid
-------------------------------------
1   | Workspace | 23,21

Table - User

表 - 用户

id  | title 
-----------------
23  | Ashish
21  | Ritesh

In assigneduserid field there are two values in that field that is 23,21.

在assigneduserid字段中,该字段中有两个值为23,21。

I want to print both usertitle like this : Ashish,Ritesh from usertable using that assigneduserid.

我想要像这样打印两个usertitle:Ashish,Ritesh来自usertable使用该assigneduserid。

And also i want to set that value in ascending order which are in assigneduseid

而且我想要按照assigneduseid的升序设置该值

I am trying to print that record on page using echo like following

我试图在页面上使用echo打印该记录,如下所示

Following is Code Where i am printing:

以下是我打印的代码:

<?php echo $task_row->usertitle;?>

Above code is printing only Ashish.

上面的代码只打印Ashish。

but i would like to print Ashish,Ritesh

但我想打印Ashish,Ritesh

following is my model.

以下是我的模特。

function getTask($id, $is_master_admin) 
        {
         $this->db->select('workspace.title as workspacetitle, user.title as usertitle,task.*');
        $this->db->join(WORKSPACE, WORKSPACE . '.id = ' . TASK . '.workspaceid', 'inner');
        $this->db->join(USER, USER . '.id = ' . TASK . '.assigneduserid', 'inner');
        $this->db->from(TASK);
        if (!$is_master_admin) {
            $this->db->where(USER . '.id', $id);
        }
        $this->db->where(TASK . '.tasktypeid', '1');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return false;
        }
    }

Thank you in advanced

先谢谢你

3 个解决方案

#1

use this

function getTask($id, $is_master_admin)
{

$this->db->select('task.*, workspace.title as workspacetitle, GROUP_CONCAT( user.title ) AS usertitle,task.assigneduserid,user.id',FALSE);
$this->db->join(WORKSPACE , WORKSPACE . '.id = ' . TASK . '.workspaceid', 'inner');
$this->db->join(USER,("FIND_IN_SET(USER .id , TASK.assigneduserid)"), 'inner');
$this->db->from(TASK);
$this->db->group_by("task.id");
if (!$is_master_admin)
{

$this->db->where ("FIND_IN_SET($id, task.assigneduserid)");
}
$this->db->where(TASK . '.tasktypeid', '1');
$query = $this->db->get();

if ($query->num_rows() > 0) {
return $query->result();
} else {
return false;
}

}

#2

Use FIND_IN_SET() & GROUP_CONCAT() functions

使用FIND_IN_SET()和GROUP_CONCAT()函数

Try this:

SELECT t.id, t.title, GROUP_CONCAT(u.title) AS userTitle
FROM Task t 
INNER JOIN User u ON FIND_IN_SET(t.assigneduserid, u.id) 
GROUP BY t.id

EDIT::

SELECT t.id, t.title, GROUP_CONCAT(u.title) AS userTitle
FROM Task t 
INNER JOIN `User` u ON CONCAT(',', t.assigneduserid, ',') LIKE ('%,', u.id, ',%')
GROUP BY t.id

#3

@Ritesh: follow Saharsh Shah, you can put this query into this command $this->db->query(); or you can follow this link to do what you want http://ellislab.com/codeigniter/user-guide/database/active_record.html

@Ritesh:关注Saharsh Shah,你可以把这个查询放到这个命令$ this-> db-> query();或者您可以按照此链接执行您想要的操作http://ellislab.com/codeigniter/user-guide/database/active_record.html

#1