时间戳

首页 > 解决方案 > 我如何运行这些查询，以便一旦找到结果它们就会停止

php - 我如何运行这些查询，以便一旦找到结果它们就会停止

问题描述

我正在尝试使用以下查询从一个表中提取数据，但我需要它运行第一个查询并在它得到结果时停止，如果没有则运行第二个查询。每次运行搜索时，我都会得到两个结果，但两个结果之一是回显没有结果。我还计划添加更多查询，以便可以根据需要返回结果。我已经在这个问题上工作了几天，最后我想我会寻求帮助。提前致谢。

$conn = new mysqli("localhost", "user", "password", "dbname");                                                                               
if(!$conn) {                                                                                                                                              
    die("Connection failed: " . mysqli_connect_error());                                                                                                  
}                                                                                                                                                         

//echo $conn->host_info . "\n";                                                                                                                           

$sql = "SELECT * FROM SCANMSTR WHERE BadgeNumber LIKE $query AND Active = 1 AND ExpirationDate >= curdate()";                                             
$result = mysqli_query($conn, $sql);                                                                                                                      

if (mysqli_num_rows($result) > 0) {                                                                                                                       
   while($row = mysqli_fetch_assoc($result)) {                                                                                                            
        echo "Badge Number: " .$row["BadgeNumber"]. "- Name: " . $row["BadgeName"]. "<br>";                                                               
     }                                                                                                                                                    
}                                                                                                                                                         

else  {                                                                                                                                                   
  echo "Not in system1";                                                                                                                                  
}                                                                                                                                                         


$sql1 = "SELECT * FROM SCANMSTR WHERE BadgeNumber LIKE $query and Active = 0 AND Barred = 1 AND Lost = 1";                                                
$result1 = mysqli_query($conn, $sql1);                                                                                                                    

if (mysqli_num_rows($result1) >0) {                                                                                                                       
   while($row1 = mysqli_fetch_assoc($result1)) {                                                                                                          
        echo "Card not active";  }                                                                                                                        
      }                                                                                                                                                   

else {                                                                                                                                                    
   echo "Not in system";

//新添加的代码

$row = mysqli_fetch_assoc($result);
if ($row) {
if ($row['Active']) {
echo "Badge Number:" .$row["BadgeNumber"]. "- 名称：".$row["BadgeName"]。"
";
}
else {
echo "卡无效";
}
if ($row) {
if($row['Barred'])
echo "持卡人被禁止";
}
if ($row) {
if($row['Lost'])
echo "卡报丢失";
}
}
else {
echo "不在系统中";

标签： phpmysqli

解决方案

您也许可以将两个查询放在一个中，并且只获取找到的第一行？像这样的东西：

$sql = "SELECT BadgeNumber, BadgeName, Active FROM SCANMSTR WHERE BadgeNumber LIKE $query AND Active = 1 AND ExpirationDate >= CURDATE()
        UNION
        SELECT BadgeNumber, BadgeName, Active FROM SCANMSTR WHERE BadgeNumber LIKE $query and Active = 0 AND Barred = 1 AND Lost = 1
        LIMIT 1";

$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
if ($row) {
    if ($row['Active']) {
        echo "Badge Number: " .$row["BadgeNumber"]. "- Name: " . $row["BadgeName"]. "<br>";
    }
    else {
        echo "Card not active";  
    }
}
else {
    echo "Not in system";
}

编辑要将其扩展到 N 检查，您可以添加更多

UNION
SELECT ...

到 $sql 的行，比如

$sql = "SELECT BadgeNumber, BadgeName, Active FROM SCANMSTR WHERE BadgeNumber LIKE $query AND Active = 1 AND ExpirationDate >= CURDATE()
        UNION
        SELECT BadgeNumber, BadgeName, Active FROM SCANMSTR WHERE BadgeNumber LIKE $query and Active = 0 AND Barred = 1 AND Lost = 1
        UNION
        SELECT ...
        UNION
        SELECT ...
        ...
        LIMIT 1";

由于 LIMIT 1，查询将根据需要检查尽可能多的 SELECT 查询以返回一行。因此，即使多个 SELECT 匹配该人（不确定是否/应该可能？）你会得到第一个，而不是全部。

或者您可以简单地获取所有检查所需的列，然后在后端逻辑中执行它们：

$sql = "SELECT BadgeNumber, BadgeName, Active, ExpirationDate 
        FROM SCANMSTR 
        WHERE BadgeNumber LIKE $query
        LIMIT 1";

$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
if ($row) {
    if ($row['Active']) {
        echo "Badge Number: " .$row["BadgeNumber"]. "- Name: " . $row["BadgeName"]. "<br>";
    }
    else if ($row['ExpirationDate'] < date('Y-m-d')) {
        echo "Card not active";
    }
}
else {
    echo "Not in system";
}

就我个人而言，我认为我会这样做，感觉更干净，更少重复的 SQL 等。两种方式都应该工作，虽然:)..

推荐阅读