代码之家  ›  专栏  ›  技术社区  ›  Max

PHP搜索不使用CASE函数

  •  0
  • Max  · 技术社区  · 6 年前

    我有下面的搜索片段,当我只使用mysql表中的value1和value2数据时,它就工作了。

    当我加上 Mysql CASE Function 对于下面示例中使用value1r和value2r的查询,并尝试在页面上进行搜索,它会给出此错误 "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in...." 这意味着我在查询中出错了。

    <?php
    
    if(isset($_POST['search']))
    {
        $valueToSearch = $_POST['valueToSearch'];
    
        $query = "SELECT * FROM gdata 
        WHERE            
        Value1 = '" . $valueToSearch . "' OR
        Value2 = '" . $valueToSearch . "' OR
        Value1r = '" . $valueToSearch . "' OR
        Value2r = '" . $valueToSearch . "' 
        ";
    
        $search_result = filterTable($query);
    
    }
     else {
        $query = "SELECT Value1, Value2,
    
      CASE
                     WHEN Value1 >= 90 AND Value1 <= 100
                     THEN 'A'
                     WHEN Value1 >= 80 AND Value1 <= 89
                     THEN 'B'
                     WHEN Value1 >= 70 AND Value1 <= 79
                     THEN 'C'  
                     WHEN Value1 >= 0 AND Value1 <= 69
                     THEN 'F'
                     ELSE '' END AS Value1r,
    
                       CASE
                     WHEN Value2 >= 90 AND Value2 <= 100
                     THEN 'A'
                     WHEN Value2 >= 80 AND Value2 <= 89
                     THEN 'B'
                     WHEN Value2 >= 70 AND Value2 <= 79
                     THEN 'C'  
                     WHEN Value2 >= 0 AND Value2 <= 69
                     THEN 'F'
                     ELSE '' END AS Value2r 
    
      FROM gdata";
    
    
        $search_result = filterTable($query);
    }
    
    // function to connect and execute the query
    function filterTable($query)
    {
        $connect = mysqli_connect("localhost", "root", "", "db");
        $filter_Result = mysqli_query($connect, $query);
        return $filter_Result;
    }
    
    ?>
    
    <!DOCTYPE html>
    <html>
        <head>
            <title>Gdata</title>
    </head>
    
        <body>
    
    
    <form action="gdata.php" method="post">
    <input type="text" name="valueToSearch" placeholder="Search"> 
    <input type="submit" name="search" value="Filter"> 
    
    <table> 
    <tr>
       <th>G1</th> 
       <th>G2</th>
       <th>R1</th>
       <th>R2</th>
    </tr>
    
    <?php 
        while($row = mysqli_fetch_array($search_result))
          { 
      echo "<tr>"; 
      echo "<td>" . $row['Value1'] . "</td>"; 
      echo "<td>" . $row['Value2'] . "</td>"; 
      echo "<td>" . $row['Value1r'] . "</td>";  
      echo "<td>" . $row['Value2r'] . "</td>";   
      echo "</tr>"; 
           } 
    
    echo "</table>";
    ?>     
    </form>
    
    </body>
    </html>
    

    我做错了什么?能帮我一点忙吗?谢谢

    注意:我的问题不是关于给定的错误。

    1 回复  |  直到 6 年前
        1
  •  1
  •   Mohasinali Nodoliya    6 年前

    这个查询会在我们的数据库中引发。

    CREATE OR REPLACE VIEW gdata_view AS
    SELECT Value1, Value2, CASE WHEN Value1 >= 90 AND Value1 <= 100 THEN 'A' WHEN Value1 >= 80 AND Value1 <= 89 THEN 'B' WHEN Value1 >= 70 AND Value1 <= 79 THEN 'C' WHEN Value1 >= 0 AND Value1 <= 69 THEN 'F' ELSE '' END AS Value1r, CASE WHEN Value2 >= 90 AND Value2 <= 100 THEN 'A' WHEN Value2 >= 80 AND Value2 <= 89 THEN 'B' WHEN Value2 >= 70 AND Value2 <= 79 THEN 'C' WHEN Value2 >= 0 AND Value2 <= 69 THEN 'F' ELSE '' END AS Value2r FROM gdata
    

    create view for gdata table.
    view is your temporary table.
    when you change any data in main table view automatically affected.
    
    when you submit data change your table name in your code gdata to gdata_view (gdata_view is a temporary table <= without view is not possible)
    
    $query = "SELECT * FROM gdata 
        WHERE            
        Value1 = '" . $valueToSearch . "' OR
        Value2 = '" . $valueToSearch . "' OR
        Value1r = '" . $valueToSearch . "' OR
        Value2r = '" . $valueToSearch . "' 
        ";
    replace by
    $query = "SELECT * FROM gdata_view 
        WHERE            
        Value1 = '" . $valueToSearch . "' OR
        Value2 = '" . $valueToSearch . "' OR
        Value1r = '" . $valueToSearch . "' OR
        Value2r = '" . $valueToSearch . "' 
        ";  
    
        2
  •  0
  •   Rakesh Jakhar    6 年前

    您可以使用以下命令跟踪错误,将此代码段放在 mysqli_query

    if (!$filter_Result) {
        printf("Error: %s\n", mysqli_error($connect));
        exit();
    }
    
        3
  •  0
  •   Creative87    6 年前

    此错误意味着查询将给出没有记录的空结果 尝试再次编写查询,并在phpmyadmin中测试它,如果它在那里工作正常,那么它将作为您的应用程序工作