gpt4 book ai didi

php - Codeigniter-MySql : Join three column values of one table with the ID of another table

转载 作者:行者123 更新时间:2023-11-29 12:33:46 24 4
gpt4 key购买 nike

我有一个为每个类别命名的类别表,然后有一个艺术品表,其中包含三列,为每件艺术品提供三个不同的类别。我正在构建一个选择查询,它将查看一个类别的所有三列并加入类别表,以便我可以获得类别名称。我为每个连接都有别名,但我的查询仍然返回category_id2 和category_id3 的空值。非常感谢任何帮助。

来自 Codeigniter 模型:

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();
}

如果需要,来自 Controller :

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.";

}
}

感谢您的帮助!

最佳答案

您不需要与类别表进行三个联接。相反,将所有三个字段作为接受的匹配进行连接:

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 ?

关于您的事件记录查询:

  • 当您有联接时,您不需要在 from 子句中重复表名称
  • where 函数没有 after 参数,将其更改为 db->like(c.category', $category, 'after'

关于php - Codeigniter-MySql : Join three column values of one table with the ID of another table,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/27093320/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com