php - 查询未返回所有可能的结果

Question

我遇到了 MySQL LIKE 未返回所有可能结果的问题。我有以下代码:

<?php
$query  = "SELECT `user_id`,
                  `rank`, 
                  `habbo_name`,
                  `rating`,
                  `branch` 
             FROM `personnel` 
            WHERE status='active' AND
                  rating='(DIROPS)' 
         ORDER BY `habbo_name`";

$result = $con->prepare($query);
$result->execute();
while ($row = $result->fetch())
{
    echo "<b>DIROPS:</b>&nbsp" . htmlspecialchars($row['habbo_name']) . "<br>";
}

$query  = "SELECT `user_id`, 
                  `rank`, 
                  `habbo_name`, 
                  `rating`, 
                  `branch` 
             FROM `personnel` 
            WHERE status='active' AND 
                  rating='(CO TECOM)' AND 
                  `branch`='usmc' 
         ORDER BY `habbo_name`";

$result = $con->prepare($query);
$result->execute();
while ($row = $result->fetch())
{
    echo "<b>CO TECOM:</b>&nbsp" . htmlspecialchars($row['habbo_name']) . "<br>";
}

$query  = "SELECT `user_id`, 
                  `rank`, 
                  `habbo_name`, 
                  `rating`, 
                  `branch` 
             FROM `personnel` 
            WHERE status='active' AND 
                  rating='(XO TECOM)' AND 
                  `branch`='usmc' 
         ORDER BY `habbo_name`";

$result = $con->prepare($query);
$result->execute();
while ($row = $result->fetch())
{
    echo "<b>XO TECOM:</b>&nbsp" . htmlspecialchars($row['habbo_name']) . "<br>";
}
?>
<br>
<?php
$query  = "SELECT `user_id`, 
                  `rank`, 
                  `habbo_name`, 
                  `rating`, 
                  `branch` 
             FROM `personnel` 
            WHERE status='active' AND 
                  `rating` LIKE '%(TECOM%'  OR 
                  `rating` LIKE '%/TECOM%'  AND 
                  status='active' 
         ORDER BY `habbo_name`";

$result = $con->prepare($query);
$result->execute();
while ($row = $result->fetch())
{
    echo "<b>TECOM:</b>&nbsp" . htmlspecialchars($row['habbo_name']) . "<br>";
}
?>

据我所知，这应该打印出评级设置为(DIROPS)、(CO TECOM)、(XO TECOM)然后(TECOM 或/TECOM)的人。

我遇到的问题是只有 DIROPS 和其他几个被退回。这是我从该代码中得到的结果:

谁能告诉我我哪里出错了？谢谢!

编辑:应该显示的记录:-

TEO-emo12 (DIROPS)
RockerManiac123 (CO TECOM) 
Rach-L (Lugn)  (XO TECOM)
Reyess  (TECOM)
zachary1142 (AuXHiDef-) (TECOM) 
norsk.no (TECOM)
lordoftjefly (mingrana) (PPO/TECOM)
spen1000 (TECOM/DI)

Answer 1

您应该使用一个查询，而不是为每个条件多次运行相同的查询，将所有评分条件包装在一个带有父AND运算符的组中，然后循环遍历结果集忽略会影响执行时间的多个循环

SELECT `user_id`, 
                  `rank`, 
                  `habbo_name`, 
                  `rating`, 
                  `branch` 
             FROM `personnel` 
            WHERE status='active' AND (
                  `rating` LIKE '%(DIROPS)%' OR 
                  `rating` LIKE '%(CO TECOM)%' OR                      
                  `rating` LIKE '%(XO TECOM)%'   OR
                  `rating` LIKE '%(TECOM%'  OR 
                  `rating` LIKE '%/TECOM%' 
                 ) AND  `branch`='usmc' 
         ORDER BY `habbo_name`

或者

 SELECT `user_id`, 
                  `rank`, 
                  `habbo_name`, 
                  `rating`, 
                  `branch` 
             FROM `personnel` 
            WHERE status='active' AND (
                  `rating` LIKE '%(DIROPS)%' OR 
                  `rating` LIKE '%(TECOM%'  OR 
                  `rating` LIKE '%/TECOM%' 
                 ) OR `rating`   IN
 (SELECT `rating` FROM `personnel` WHERE `branch`='usmc' 
  AND ( `rating` ='(CO TECOM)' OR `rating` ='(XO TECOM)'))   
         ORDER BY `habbo_name`