代码是不言自明的
如何将这两个SQL查询合并为一个？
类似于"加入..ON..或shop_tags.parent_tag_id=shop_tags_link.tag_id"
// query PARENT tags with this Design ID
$list_tags_link_query = "SELECT * FROM shop_tags_link JOIN shop_tags ON shop_tags_link.tag_id = shop_tags.tag_id WHERE shop_tags_link.design = '$design'";
$list_tags_link_exec = mysql_query($list_tags_link_query) or die(mysql_error());
while($row = mysql_fetch_array($list_tags_link_exec)) {
$tag_id = $row["tag_id"];
$tag_fr = utf8_decode($row["tag_fr"]);
$tag_en = utf8_decode($row["tag_en"]);
echo "[<a href='"$canon/?delete=$tag_id'">x</a>] $tag_fr ($tag_en), ";
// query CHILD tags with the corresponding PARENT's tag_id
$list_tags_child_query = "SELECT * FROM shop_tags WHERE parent_tag_id = '$tag_id'";
$list_tags_child_exec = mysql_query($list_tags_child_query) or die(mysql_error());
while($row = mysql_fetch_array($list_tags_child_exec)) {
$tag_fr = utf8_decode($row["tag_fr"]);
$tag_en = utf8_decode($row["tag_en"]);
$tag_id = $row["tag_id"];
echo "<font size='"1'">[<a href='"$canon/?delete=$tag_id'">x</a>] $tag_fr ($tag_en), </font>";
}
}
我只想要1个查询，而不是2个
如果您只想处理一个查询，那么您可以尝试以下解决方案。但是，如果数据库表包含更多的记录，则此查询需要更多的执行时间。
$list_tags_link_query = "SELECT stl.*,st.*,(select GROUP_CONCAT(CONCAT_WS('@@',in_t.tag_id,if(in_t.tag_fr <> '',in_t.tag_fr,''),if(in_t.tag_en <> '',in_t.tag_en,'')) SEPARATOR '|||') from shop_tags as in_t where in_t.parent_tag_id = stl.tag_id) as childs_details FROM shop_tags_link stl JOIN shop_tags st ON stl.tag_id = st.tag_id WHERE stl.design = '$design'";
$list_tags_link_exec = mysql_query($list_tags_link_query) or die(mysql_error());
while ($row = mysql_fetch_array($list_tags_link_exec)) {
$tag_id = $row["tag_id"];
$tag_fr = utf8_decode($row["tag_fr"]);
$tag_en = utf8_decode($row["tag_en"]);
echo "[<a href='"$canon/?delete=$tag_id'">x</a>] $tag_fr ($tag_en), ";
$childs_details=$row["childs_details"];
if($childs_details!=""){
$child_row=explode("|||",$childs_details);
if(is_array($child_row) && count($child_row)>0){
for($i=0;$i<count($child_row);$i++){
$child_row_detail=explode("@@",$child_row[$i]);
$tag_id = $child_row_detail[0];
$tag_fr = utf8_decode($child_row_detail[1]);
$tag_en = utf8_decode($child_row_detail[2]);
echo "<font size='"1'">[<a href='"$canon/?delete=$tag_id'">x</a>] $tag_fr ($tag_en), </font>";
}
}
}
}
我认为你的代码几乎是正确的，但你在其中犯了一个错误。$row在循环中被覆盖，所以只需更正它。请检查下面的代码。
// query PARENT tags with this Design ID
$list_tags_link_query = "SELECT * FROM shop_tags_link JOIN shop_tags ON shop_tags_link.tag_id = shop_tags.tag_id WHERE shop_tags_link.design = '$design'";
$list_tags_link_exec = mysql_query($list_tags_link_query) or die(mysql_error());
while($row = mysql_fetch_array($list_tags_link_exec)) {
$tag_id = $row["tag_id"];
$tag_fr = utf8_decode($row["tag_fr"]);
$tag_en = utf8_decode($row["tag_en"]);
echo "[<a href='"$canon/?delete=$tag_id'">x</a>] $tag_fr ($tag_en), ";
// query CHILD tags with the corresponding PARENT's tag_id
$list_tags_child_query = "SELECT * FROM shop_tags WHERE parent_tag_id = '$tag_id'";
$list_tags_child_exec = mysql_query($list_tags_child_query) or die(mysql_error());
while($child_row = mysql_fetch_array($list_tags_child_exec)) {
$tag_fr = utf8_decode($child_row["tag_fr"]);
$tag_en = utf8_decode($child_row["tag_en"]);
$tag_id = $child_row["tag_id"];
echo "<font size='"1'">[<a href='"$canon/?delete=$tag_id'">x</a>] $tag_fr ($tag_en), </font>";
}
}