显示列的sql查询输出 [英] display sql query output for columns

问题描述

我有这张桌子

id | apple | banana | coconut | pear|
1     1       1       1           0
2     0       0        1          1
3     1       0        0          1

和此sql查询

select tree
from ((select id, 'apple' as tree
       from trees
       where apple = 1
      ) union all
      (select id, 'banana' as tree
       from trees
       where banana = 1
      ) union all
      (select id, 'coconut' as tree
       from trees
       where coconut = 1
      ) union all
      (select id, 'pear' as tree
       from trees
       where pear = 1
      )
     ) t
where id = 1;

输出是

apple 
banana
coconut

我该如何用php写这个，以便我可以回显结果

how would i write this in php so i can echo out the result

这是我到目前为止所拥有的

this is what i have so far

$sql = " select tree
    from ((select id, 'apple' as tree
           from trees
           where apple = 1
          ) union all
          (select id, 'banana' as tree
           from trees
           where banana = 1
          ) union all
          (select id, 'coconut' as tree
           from trees
           where coconut = 1
          ) union all
          (select id, 'pear' as tree
           from trees
           where pear = 1
          )
         ) t
    where id = '$id'";
 $result = mysqli_query($conn, $sql);

但是我不知道该怎么办之后，我无法进行while循环，或者只是回显它给出错误的结果

but i dont know what to do after this i cant put a while loop or just echo out the result it gives an error

推荐答案

您可以简单地使用mysqli_fetch_assoc()循环搜索结果:

You can simply loop over your results using mysqli_fetch_assoc():

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['tree'] . "\n";
}

但是，执行类似的操作可能更简单/有效，该操作使用列名称生成输出数据，并在列的值不为0时回显该名称:

However, it is probably simpler/more efficient to do something like this, which uses the column names to generate the output data, echoing the name when the value in the column is not 0:

$sql = "SELECT * FROM trees WHERE id = '$id'";
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_assoc($result)) {
    foreach ($row as $key => $value) {
        if ($key == 'id') continue;
        if ($value) echo "$key\n";
    }
}

这篇关于显示列的sql查询输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！