时间戳

首页 > 解决方案 > PHP搜索不适用于CASE功能

php - PHP搜索不适用于CASE功能

问题描述

我有以下搜索片段，当我只将它与 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>

我在这里做错了什么？我能得到一点帮助吗？谢谢！

注意：我的问题与给定的错误无关。

标签： phpmysql

解决方案

这个查询在我们的数据库上触发。

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 . "' 
    ";

推荐阅读