question about mysqli prepare

Question

I have following mysqli configuration settings on a separate file:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

Now, I have some functions in a class which is on another file. Sth like:

function username_exists($username){
    global $mysqli;
    if ($stmt = $mysqli->prepare("SELECT username FROM users WHERE username=?")) {
        $stmt->bind_param("s", $username);
        $stmt->execute();
        $stmt->store_result();
        $count=$stmt->num_rows;
        $stmt->close();
    }
    return ($count > 0 ? true : false);
    }

I was wondering about:

1. Why do i need to include global $mysqli; inside each function in the class, if i dont include, i get error message like:

   Fatal error: Call to a member function prepare() on a non-object in...

2. What does $stmt->store_result(); do? If I do not include it, it returns 0 rows.

3. Do I need to use $stmt->close(); after each query? (For example I have other functions below that function, do I need to use it at the end of each function or end of last function in the class?

Thanks.

Answer 1

First question: If your object reference $mysqli is defined outside the function, it must be either passed in as a parameter to the function like function username_exists($username, $mysqli) or referenced with the global keyword. The function parameter method is probably preferable, to avoid using global scope variables in context of function scope. If you don't do either of these, $mysqli is not known inside the function since the function cannot see outside its own scope.

If you want to avoid referencing the global $mysqli, I would suggest including a MySQLi object as a member of your class. After you pass in an existing MySQLi object, you can reference it in each function as $this->mysqli

class My_Class()
{
  public $mysqli;
  // everything else

  function some_func()
  {
    $this->mysqli->prepare();
    // etc
  }
}
$x = new My_Class();
$x->mysqli = $mysqli; // or new mysqli("localhost", "my_user", "my_password", "world");

Second question: $stmt->store_result() retrieves the full result set from the completed query called by execute(). Without it, MySQLi has not yet requested any results to be returned from the MySQL server, even though the query may have been successful. It is necessary only for a SELECT query that returns rows.

Third question: If you are calling the same SQL statement over and over in a loop, you don't need to call $stmt->close() after each iteration. It is a good idea to call $stmt->close() in other circumstances, freeing memory and letting the MySQL server know you are done using that statement and result set.