2009-04-07 18:29 阅读 52

Codeigniter:MySQL Where子句和引号

Query in CodeIgniter:

$this->db->select('comments.created_at, comments.section_id, comments.submittedby_id, users.username, comments.text,');
$this->db->order_by('comments.created_at', 'desc');
$this->db->where('comments.submittedby_id',  'users.user_id'); 
$this->db->where('comments.section_id', ''); 

$query = $this->db->get(array('comments', 'users', 'sections'),10);

Produce SQL Request:

SELECT pdb_comments.created_at, pdb_comments.section_id, pdb_comments.submittedby_id, pdb_users.username, pdb_comments.text, FROM (pdb_comments, pdb_users, pdb_sections) WHERE pdb_comments.submittedby_id = 'users.user_id' AND pdb_comments.section_id = '' ORDER BY pdb_comments.created_at desc LIMIT 10

The issue is that the database prefix (pdb_) does not get added in the WHERE clause. I can manually insert the prefix by appending $this->db->dbprefix, but this doesn't fix the main problem.


`pdb_comments`.`submittedby_id` = 'pdb_users.user_id'

The quotes on the right side are not accurate, and generate 0 results for me. Is there any way to make CodeIgniter recognize the second half of the where clause as a piece of my table; thereby adding the database prefix, and properly placing the quotes by avoiding two joins? Is there another way to do this? Thanks in advance.



  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    dongweicha6077 dongweicha6077 2009-04-07 19:15


    $this->db->select('comments.created_at, comments.section_id, comments.submittedby_id, users.username, comments.text,');
    $this->db->join('users', 'comments.submittedby_id=users.user_id'); 
    $this->db->join('sections', ''); 
    $this->db->order_by('comments.created_at', 'desc');
    $query = $this->db->get();


    点赞 评论 复制链接分享
  • douci2516 douci2516 2009-04-07 18:37

    Possibly a dumb question, but why don't you just write the SQL directly? The interface doesn't look like it's giving you anything but clutter.

    点赞 评论 复制链接分享