doubu0897 2014-11-23 19:31
浏览 51
已采纳

Codeigniter - MySql:使用另一个表的ID加入一个表的三个列值

I have a category table that names each category, then I have an artwork table that has three columns offering three different categories per art piece. I am building a select query that will look in all three columns of one category and join the category table so I can get the category name. I have alias' for each join, but my query is still returning null values for category_id2 and category_id3. Any assistance is greatly appreciated.

From Codeigniter Model:

function category_search($category) {
    $this -> db -> select('a.id, a.artist_fname, a.artist_lname, b.artist_id, b.sm_file_name, b.category_id, b.category_id2, b.category_id3, c.id, c.category');
    $this -> db -> from('ap_mini_artist a', 'ap_mini_artwork b', 'ap_art_categories c', 'ap_art_categories c2', 'ap_art_categories c3');
    $this -> db -> where('c.category', $category, 'after');
    $this -> db -> join('ap_mini_artwork b', 'b.artist_id=a.id', 'left');
    $this -> db -> join('ap_art_categories c', 'c.id=b.category_id', 'left');
    $this -> db -> join('ap_art_categories c2', 'c2.id=b.category_id2', 'left');
    $this -> db -> join('ap_art_categories c3', 'c3.id=b.category_id3', 'left');
    $query = $this -> db -> get();
    return $query -> result();
}

From Controller if needed:

public function category_search() {

    $category = $this -> input -> post('categoryValue');
    $query = $this -> mini_show_model -> category_search($category);

    if (!empty($query)) {
        $json = json_encode($query);
        print $json;
    } else {
        echo "Your query is empty, please try again.";

    }
}

Thanks for your help!

  • 写回答

1条回答 默认 最新

  • douerqu2319 2014-11-24 12:28
    关注

    You do not need three joins with the category table. Instead join with all three fields as an accepted match:

    select a.id, a.artist_fname, a.artist_lname, b.artist_id, b.sm_file_name, b.category_id, b.category_id2, b.category_id3, c.id, c.category
    from ap_mini_artist a
    left join ap_mini_artwork b on b.artist_id = a.id
    left join ap_art_categories c on c.id in (b.category_id, b.category_id2, b.category_id3)
    where c.category LIKE ?
    

    Regarding your active record query:

    • When you have joins you do not need to repeat table names in the from clause
    • where function has no after arguments, change it to db->like(c.category', $category, 'after'
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题