在mysql codeigniter中加入3个表 [英] join 3 tables in mysql codeigniter

问题描述

我的数据库中有3个表:-

I have 3 tables in my database :-

1. tbl_roles(角色名称，角色名称);
2. tbl_users(id，role_id，用户名，电子邮件，密码);
3. tbl_tickets_replies(id，ticket_id，user_id，role_id，comments)

role_id, id, id是相应表的主键. 我需要:-

role_id, id, id are primary keys of corresponding tables. i need :-

1. tbl_users中的用户名.
2. tbl_roles中的角色名称.
3. 来自tbl_tickets的评论

其中tbl_tickets_replies = $ticket_id中的ticket_id作为参数.

where ticket_id from tbl_tickets_replies = $ticket_id coming as a parameter.

我的模型函数是:-

function fetch_comments($ticket_id){
        $this->db->select('tbl_tickets_replies.comments,tbl_users.username,tbl_roles.role_name');
        $this->db->where('tbl_tickets_replies.ticket_id',$ticket_id);
        $this->db->from('tbl_tickets_replies');
        $this->db->join('tbl_users','tbl_users.id = tbl_tickets_replies.user_id');
        $this->db->join('tbl_roles','tbl_roles.role_id=tbl_tickets_replies.role_id');
        $comments = $this->db->get('tbl_tickets_replies');
        return $comments;
     }

这显示数据库错误，即我在执行错误的查询. 我想问一下如何联接三个表以从3个不同的表中获取数据

this is showing database error i.e., I am doing wrong query. I want to ask how can I join three tables to fetch data from 3 different tables

此错误显示:-

发生数据库错误
错误编号:1066

A Database Error Occurred
Error Number: 1066

不唯一的表/别名:'tbl_tickets_replies'

Not unique table/alias: 'tbl_tickets_replies'

选择tbl_tickets_replies.comments，tbl_users.username， tbl_roles.role_name FROM(tbl_tickets_replies， tbl_tickets_replies)JOIN tbl_users ON tbl_users.id = tbl_tickets_replies.user_id联接tbl_roles ON tbl_roles.role_id = tbl_tickets_replies.role_id在哪里 tbl_tickets_replies.ticket_id ='6'

SELECT tbl_tickets_replies.comments, tbl_users.username, tbl_roles.role_name FROM (tbl_tickets_replies, tbl_tickets_replies) JOIN tbl_users ON tbl_users.id = tbl_tickets_replies.user_id JOIN tbl_roles ON tbl_roles.role_id=tbl_tickets_replies.role_id WHERE tbl_tickets_replies.ticket_id = '6'

文件名:C:\ wamp \ www \ local.helpdesk.com \ bonfire \ codeigniter \ database \ DB_driver.php

Filename: C:\wamp\www\local.helpdesk.com\bonfire\codeigniter\database\DB_driver.php

行号:330`

推荐答案

您两次引用tbl_tickets_replies. 试试这个:

You are referring to tbl_tickets_replies twice. Try this:

function fetch_comments($ticket_id){
    $this->db->select('tbl_tickets_replies.comments, 
           tbl_users.username,tbl_roles.role_name');
    $this->db->where('tbl_tickets_replies.ticket_id',$ticket_id);
    $this->db->join('tbl_users','tbl_users.id = tbl_tickets_replies.user_id');
    $this->db->join('tbl_roles','tbl_roles.role_id=tbl_tickets_replies.role_id');
    return $this->db->get('tbl_tickets_replies');
}

这篇关于在mysql codeigniter中加入3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！