如何解决这个错误：mysql_fetch_array（）期望参数1是mysqli_result

Question

I am getting this error:

mysql_fetch_array() expects parameter 1 to be mysqli_result

I am trying to do searching a table. I am not sure how my fetch array works with specifically, but I know it returns the row which suits the condition given.

My PHP code :

class Registration
{
    private $db_connection = null;

    public function __construct()
    {
        if (isset($_POST["search"])) 
        {
           $this->doSearch();
        }
    }

   private function doSearch()
   {
     $field = $_POST["field"];
     $find = $_POST["find"];
     echo "you are searching ". $_POST['find'] . " in " . $_POST["field"] . " category"; 
     $this->db_connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
     $sql = "SELECT * FROM users WHERE upper $field LIKE $find";
     $result_search = $this->db_connection->query($sql); // mysql_fetch_array() expects parameter 1 to be mysqli_result
     while($row = mysqli_fetch_array($result_search)) 
          {
            echo $row['user_name']."</br>";
            echo $row['user_email']."</br>";
            echo $row['user_phone']."</br>";
            echo "<br>";
          }
   }
}

How to solve this problem using OOP.

I can solve in procedural methods but not in OOP.

Answer 1

I reopened this question because there are at least four issues to correct here.

First issue: you have syntax errors in your SQL:

 $sql = "SELECT * FROM users WHERE upper $field LIKE $find";

If you wanted to use the upper() function, you need to use parentheses:

 $sql = "SELECT * FROM users WHERE upper($field) LIKE $find";

Second issue: strings must be quoted.

 $sql = "SELECT * FROM users WHERE upper($field) LIKE '$find'";

Third issue: you're copying a variable into your query in an unsafe manner, so you are creating an SQL injection vulnerability. You should use query parameters to avoid this.

 $sql = "SELECT * FROM users WHERE upper($field) LIKE ?";

Fourth issue: it's still unsafe because $field is untrusted input, and could still introduce SQL injection. But query parameters don't work for column names, they only work in place of literal values like a quoted string or a number. The best fix for variable column names is to validate the input against a whitelist of columns that really exist in your table, and reject anything else (or choose a default). Here's a quick way to do that:

 $columns = array("user"=>"user_name", "first"=>"first_name", "last"=>"last_name",
     "DEFAULT"=>"username");
 $realfield = $columns[$field] ?: $columns["DEFAULT"];
 $sql = "SELECT * FROM users WHERE upper($realfield) LIKE ?";

Notice my array keys in $columns don't even have to be exactly the SQL column names. So this allows us to make the web usage friendlier than the SQL usage, and allows us to change one without having to change the other (decoupling).

Fifth issue: LIKE is already a case-insensitive comparison when using the default collation order, so you don't need upper() anyway. That won't cause an error, but it's just good practice.

 $sql = "SELECT * FROM users WHERE $realfield LIKE ?";

Sixth issue: this is the one that actually caused the error message in your summary. The query() method returns false instead of a statement resource if there's an error. You must always check for that false before trying to use it as though it is a statement.

Here's a better way to code this, putting it all together:

 $columns = array("user"=>"user_name", "first"=>"first_name", "last"=>"last_name",
     "DEFAULT"=>"username");
 $realfield = $columns[$field] ?: $columns["DEFAULT"];

 $sql = "SELECT * FROM users WHERE $realfield LIKE ?";

 if (!($stmt = $this->db_connection->prepare($sql))) {
     die($this->db_connection->error);
 }

 $stmt->bind_param("s", $find);
 if (!$stmt->execute()) {
     die($stmt->error);
 }

 $results = $stmt->get_results();

 while($row = $results->fetch_array()) {
     . . .
 }