PHP避免了重复查询结果的问题 - PHP avoid duplicate query result issue

Hi我有一个函数，它接收一个数组，并为数组中的每个元素运行一个查询，该查询选择一个随机的结果id。但是，我想避免重复。如何检查结果id是否重复并再次运行查询？一直在循环中，而不是滑雪元素？这是我的尝试（注意：$array_result是7个元素），也选择distinct不起作用，因为我必须单独运行每个数组元素的查询

$queryn = "select taxonomic_units.tsn, hierarchy.hierarchy_string, hierarchy.TSN, taxonomic_units.rank_id from hierarchy left join taxonomic_units on hierarchy.TSN = taxonomic_units.tsn where taxonomic_units.rank_id = 220 and hierarchy.hierarchy_string LIKE '%$array_result[0]%'order by rand() limit 1";
$resultn = $dbn -> Execute($queryn);
$rown=$resultn->FetchRow();
$newspecies = $rown['tsn'];
$result_array[] = $newspecies;
for($i=1; $i<count($array_result);$i++){
   $previous = implode(',', $result_array);
$queryn = "select taxonomic_units.tsn,
                  hierarchy.hierarchy_string,
                  hierarchy.TSN,
                  taxonomic_units.rank_id
           from hierarchy
           left join taxonomic_units
                  on hierarchy.TSN = taxonomic_units.tsn
           where taxonomic_units.rank_id = 220
                  and hierarchy.hierarchy_string LIKE '%$array_result[$i]%'
                  and taxonomic_units.tsn not in ('$previous')
           order by rand()
           limit 1";
$resultn = $dbn -> Execute($queryn);
$rown=$resultn->FetchRow();
$newspecies = $rown['tsn'];
$result_array[] = $newspecies;
}

您的$array_result结构是什么？

数组（''=>''）；

或

array（数组（"=>"））；

如果你的数组狭窄是第一个，那么你可以使用array_unique来检查重复的id。

如果您的数组结构是第二个，那么在运行查询之前，您可能需要执行一些筛选功能。。

function getrandomspecies($array_result){
 $dbn = adodbConnect(); //connect to database
 $result_array = array(); //make empty array for result array
  $i=0;
 a: foreach($array_result as $id){ // this loops 7 times
   for($j=0;$j<=$i;$j++)
   {
       if($array_result[$j]==$id)
       {
           goto a; //will go to foreach loop if there is a previous occurence of the element
       }
   }
  //complicated query that basically chooses a random id based on a likeness of a string that has other ids 
  $queryn = "select taxonomic_units.tsn, hierarchy.hierarchy_string, hierarchy.TSN, taxonomic_units.rank_id from hierarchy left join taxonomic_units on hierarchy.TSN = taxonomic_units.tsn where taxonomic_units.rank_id = 220 and hierarchy.hierarchy_string LIKE '%$id%'order by rand() limit 1";
   $resultn = $dbn -> Execute($queryn);
   $rown=$resultn->FetchRow();
   $newspecies = $rown['tsn'];
   $result_array[] = $newspecies; //this is the result array and the one result putting in
 }

}

您可以这样做：

$previous = implode(',', $result_array);
$queryn = "select taxonomic_units.tsn,
                  hierarchy.hierarchy_string,
                  hierarchy.TSN,
                  taxonomic_units.rank_id
           from hierarchy
           left join taxonomic_units
                  on hierarchy.TSN = taxonomic_units.tsn
           where taxonomic_units.rank_id = 220
                  and hierarchy.hierarchy_string LIKE '%$id%'order
                  and taxonomic_units.tsn not in (".$previous.")
           order by rand()
           limit 1";

请注意，最终的and不包括您已经检索到的tsn。它可能需要一些调整（检查$result_array是否为空等），但这是基本思想。

您可以在存储ID之前搜索重复项。只需使用in_array。

function getrandomspecies($array_result){
   ...
   $i=0;      //If your $array_result begin with 0
   while($i<count($array_result))
   {
       ...
       $newspecies = $rown['tsn'];
       if(in_array($newspecies, $result_array)))
          continue;
       else
       {
          $result_array[] = $newspecies;
          $i++;
       }
   }
}