我有这个sql,它是在别人的帮助下制作的。
$sql ="
select e.*, i.*, group_concat( t.tag separator ',') as tag_list
from nv_entries e
JOIN nv_tags t on t.entrie_id = e.id
LEFT JOIN nv_images i on i.entrie_id = e.id
where t.tag in ( $tag_list )
group by e.id
having count(t.id) = $num_tags ";
结果是这样的(我在这里只显示一个条目,可能更多):
[1] => Array
(
[id] => 2
[band] => Kids for Cash
[album] => No More Walls E.P.
[label] =>
[year] => 1986
[text] => Text about album kids for cash.
[entrie_id] => 2
[source] => img02_9lch1.png
[tag_list] => tree
)
对于标签,我必须显示条目具有的所有标签,并突出显示用于获取结果的标签。在这种情况下,[tag_list] => tree
仅显示一个标记,即搜索字段中使用的标记。我的问题是,我怎样才能得到这样的结果?
...
[tag_list] => tree, green, foo, bar
[used_tags] => tree
)
因为数组也很好,但是当它只是一个项目时,也请一个数组。
如果我理解正确,请在有条件中使用>=
$sql ="
select e.*, i.*, group_concat( t.tag separator ',') as tag_list
from nv_entries e
LEFT JOIN nv_images i on i.entrie_id = e.id
JOIN nv_tags t on t.entrie_id = e.id
where t.tag in ( $tag_list )
group by e.id
having count(t.id) >= $num_tags ";
加
子查询方法:
$sql ="
select e.*, i.*, group_concat( t.tag separator ',') as tag_list
from nv_entries e
JOIN nv_tags t on t.entrie_id in (
select se.id
from nv_entries se
JOIN nv_tags st on st.entrie_id = se.id
where st.tag in ( $tag_list )
group by se.id
having count(st.id) >= $num_tags
)
LEFT JOIN nv_images i on i.entrie_id = e.id
WHERE 1
group by e.id
";
进入子查询,我得到条目的 ID 列表,至少有请求的标签,然后在主查询中我得到所有 infox
添加固定查询(请参阅提问者注释)
子查询方法,修复了"e"和"t"之间丢失的连接:
$sql ="
select e.*, i.*, group_concat( t.tag separator ',') as tag_list
from nv_entries e
JOIN nv_tags t on t.entrie_id = e.id
LEFT JOIN nv_images i on i.entrie_id = e.id
WHERE e.id in (
select se.id
from nv_entries se
JOIN nv_tags st on st.entrie_id = se.id
where st.tag in ( $tag_list )
group by se.id
having count(st.id) >= $num_tags
)
group by e.id
";