PHP search not working with CASE function

Question

I have the following search snippet and it works when I only use it with the value1 and value2 data from mysql table.

When I add Mysql CASE Function to the query with value1r and value2r as in the example below, and try to make a search on the page, it gives me this error "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in...." which means I have a mistake in the query.

<?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>

What am I doing wrong here? Can I get a little help please? thanks!

Note: My question is not about the given error specifically.

Well I don't see a syntax error in your SQL statement. Try to put `if ($filter_Result == false) { echo mysqli_error($connect); exit; }` in `filterTable()` to understand more details about the error — codtex, Apr 19 '19 at 08:24
You should also escape or remove special characters in `$valueToSearch`. If you use a search phrase with `'` or `;` you will get SQL syntax errors in the `if` part of your code. The `else` part is looking correct to me. — HelgeB, Apr 19 '19 at 08:34
Possible duplicate of [mysqli\_fetch\_array() expects parameter 1 to be mysqli\_result, boolean given in](https://stackoverflow.com/questions/15439919/mysqli-fetch-array-expects-parameter-1-to-be-mysqli-result-boolean-given-in) — Progman, Apr 19 '19 at 12:17

score 1 · Accepted Answer · answered Apr 19 '19 at 09:20

this query fire on our database.

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

thanks a lot! It works when I create a view as you explained, and a little change in the code. — Max, Apr 19 '19 at 13:53

score 0 · Answer 2 · answered Apr 19 '19 at 08:24

0

You can trace your error by using the following, put this code snippet after the mysqli_query

if (!$filter_Result) {
    printf("Error: %s\n", mysqli_error($connect));
    exit();
}

answered Apr 19 '19 at 08:24

Rakesh Jakhar

6,380
2
11
20

1

This may provide useful information, but does not provide a solution to the problem. – Nigel Ren Apr 19 '19 at 08:27
@NigelRen this will provide what is the error actually and help to fix that. – Rakesh Jakhar Apr 19 '19 at 08:28
1

As you say yourself - it will *help*, but this is supposed to be a solution. – Nigel Ren Apr 19 '19 at 08:29

score 0 · Answer 3 · answered Apr 19 '19 at 08:50

0

This error means that query give empty result with no records Try to write query again and test it in phpmyadmin if it works fine there so it will work as your application

answered Apr 19 '19 at 08:50

Creative87

125
9

PHP search not working with CASE function

3 Answers3

this query fire on our database.