php - 如果 mysql 列 =x echo 'x' 否则如果 y echo 'y'

Question

我有一个脚本，用于检查数据库中应用程序的状态，每次用户提交应用程序时，数据库中有 4 列:“form1_completed”、“form2_completed”、“form3_completed”和“form4_completed”其中每一个都将从“否”更新为"is"，具体取决于他们提交的申请。

mysql查询基本上搜索数据库中设置为“no”的任何行，即form1_completed =“no”或form2_completed =“no”，如果其中任何一个设置为“no”，则表示状态申请流程正在等待

同样，如果用户已提交所有申请，则所有 4 个列都将更新为"is"，并且查询的第二部分告诉我们申请过程已完成。

现在我在代码中引入了另一个元素，如果用户提交申请并且他们填写不正确或者没有通过初始检查，那么数据库中名为“status”的列将更新为“失败了。

所以我想要做的是，如果“状态”列的值为“失败”，那么我希望应用程序进程被列为“失败”而不是待处理，但因为 4 个中的一个或多个列; 'form1_completed'、'form2_completed' 等仍然没有，然后 mysql 会回显这一点，因为应用程序仍然处于挂起状态。但我想做的是说一些如何，如果“状态”设置为失败，则说应用程序进程失败，否则如果“状态”为空或“失败”不存在，则应用程序进程将处于待处理状态

请有人告诉我如何修改我的脚本才能完成这项工作

 <?php include 'config.php';
 $data = mysql_query("SELECT *, TIMESTAMPDIFF(DAY, date, CURDATE()) AS expire_date
                     FROM supplier_session 
                     WHERE form1_completed = 'No' 
                       OR form2_completed = 'No' 
                       OR form3_completed = 'No' 
                       OR form4_completed = 'No'
                     ORDER BY expire_date ASC") 
     or die(mysql_error()); 

     echo "<table class=\"table\" style=\"width:995px;  font-family: 'Lucida Grande', Tahoma, Verdana, Arial, sans-serif;
     font-size:11px;\" >

<tr>
    <td style=\"width:100px;\">ID:</td><td>Company Name:</td><td>Company Reg No:</td><td>Application Started:</td><td style=\"width:200px;\">Application Duration:</td><td>Status:</td></tr>";


     while($row = mysql_fetch_array( $data )) { 
       $days = $row['expire_date'];
       $when = $days*0; 
       $str = $row['expire_date'];
       $str2 = substr($str, 0); // "quick brown fox jumps over the lazy dog."


       if ($when <= 31){
         echo "<tr><td style=\"width:100px;\"><p>".$row['id'] . "</p></td>"; 
         echo "<td style=\"width:150px;\"><p>".$row['company_name'] . "</p></td>"; 
         echo "<td style=\"width:150px;\"><p>".$row['company_reg_number'] . "</p></td>";
         echo "<td>"; echo date('d/m/Y',strtotime($row['date'])); echo "</td>";

         if ($days >= 8) {
             echo "<td style=\"width:200px;\"><p>";
             print ($str2 -7);
            echo " days overdue</td>";      
         }


        elseif ($when <= 7){
             echo "<td style=\"width:200px;\"><p>{$str2} of 7 days past</td>";
          }

        }

        $q2 = "SELECT * FROM supplier_session 
               WHERE form1_completed = 'NO' 
                 OR form2_completed = 'NO' 
                 OR form3_completed = 'NO' 
                 OR form4_completed = 'NO'" 
              or die (mysql_error());
    $result2 = mysql_query($q2);
    $count2 = mysql_num_rows($result2);

    echo "<td>";
    echo "Pending</td>";
    echo "<tr>";

     }


 $data2 = mysql_query("SELECT *,
                              TIMESTAMPDIFF(DAY, date, CURDATE()) AS expire_date
                      FROM supplier_session 
                      WHERE form1_completed = 'Yes' 
                        AND form2_completed = 'Yes' 
                        AND form3_completed = 'Yes' 
                        AND form4_completed = 'Yes'
                      ORDER BY expire_date ASC") 
     or die(mysql_error()); 


     while($row = mysql_fetch_array( $data2 )) { 
       $days = $row['expire_date'];
       $when = $days*0; 
       $str = $row['expire_date'];
       $str2 = substr($str, 0); // "quick brown fox jumps over the lazy dog."


       if ($when <= 31){
         echo "<tr><td style=\"width:100px;\"><p>".$row['id'] . "</p></td>"; 
         echo "<td style=\"width:150px;\"><p>".$row['company_name'] . "</p></td>"; 
         echo "<td style=\"width:150px;\"><p>".$row['company_reg_number'] . "</p></td>";
         echo "<td style=\"width:203px;\">"; echo date('d/m/Y',strtotime($row['date'])); echo "</td>";


        echo "<td style=\"width:200px;\"><p> Completed in ";
             print ($str2);
            echo " days</td>";      
         }

        $q2 = "SELECT * FROM supplier_session 
               form1_completed = 'Yes' AND form2_completed = 'Yes' AND form3_completed = 'Yes' AND form4_completed = 'Yes'" 
             or die (mysql_error());
    $result2 = mysql_query($q2);
    $count2 = mysql_num_rows($result2);

    echo "<td>";
    echo "Completed</td>";
    echo "<tr>";

     }

      echo "</table>"; //Close the table in HTML


  ?>

Answer 1

更改各种查询以包括对任何 form* 列是否失败的测试。

这是“待处理”查询:

SELECT * FROM supplier_session 
               WHERE 
                 (    form1_completed = 'NO' 
                  OR form2_completed = 'NO' 
                  OR form3_completed = 'NO' 
                  OR form4_completed = 'NO')
               AND NOT ('failed' IN (form1_completed,
                                     form2_completed,
                                     form3_completed,
                                     form4_completed ))