Select with Prepared statements Mysqli

Asked

Viewed 3,509 times

10

//// The connection is like Mysqli was passed by the constructor method and assigned the variable $db;

public function getById($id) {

    $query = $this -> db -> prepare("SELECT * FROM tabela WHERE id = ?");
    $query -> bind_param('i', $id);
    $query -> execute();
    return $query -> fetch();

}

Why is the fetch() method returning a boolean value? when I give a var_dump() it returns me true or 1 Would you like fetch to return an array, and how can I choose the type of array? associative or otherwise?

php mysqli

  • Simply use return $query->fetch_object(); or return $query->fetch_assoc(); would not return what you want?

  • 1

    It returns this: Call to Undefined method mysqli_stmt::fetch_object()

1 answer

7


When using Prepared staments with Mysqli in a select, you first need to pass the database result to php, this is done with the method/function get_result() the great advantage of it is that you do not need to specify the columns individually as it is done with bind_result()

Changes your method to:

  $query->execute();
  $result = $query->get_result();
  return $result->fetch_all();
}

Or if you want to do it manually.

  $query->execute();
  $result = $query->get_result();

  $lista = array();
  while($item = $result->fetch_assoc()){
     $lista[] = $item;
  }
  return $lista;
}

After that you can get the result with fetch() or variant in a foreach.

Example with bind_result

public function getById($id) {
   $query = $this->db->prepare("SELECT nome, idade, profissao, aniversario
                                FROM pessoas WHERE id = ?");
   $query->bind_param('i', $id);
   $query->execute();
   $query->bind_result($nome, $idade, $profissao, $aniversario);

   $lista = array();
   $i = 0;
   while($query->fetch()){
      $lista[$i]['nome'] = $nome;
      $lista[$i]['idade'] = $idade;
      $lista[$i]['profissao'] = $profissao;            
      $lista[$i]['aniversario'] = $aniversario;
      $i++;
   }
   return $lista;
}

  • I tried with get_result and var_dump does not return me an array but a boolean yet... but the bind_result I always have to do manually? for example, I’m making an abstract Model class, when I used PDO I was just giving the fetch that it returned me the array and I picked it out of the scope of the method, I just want to return the array and manipulate it out of the method, you know? Thank you.

  • @NGTHM4R3, Mysqli works different from PDO, so I showed the difference between the bind_result() and the get_result(). Aleterei a resposta para o método retornar um array no lugar do result.

  • Thanks, I need to assign a variable because it would be the execute() method that has the rest of the other methods like get_result?

  • @NGTHM4R3, the execute() send your query to the bank, to get the return you need the get_result() it does return the queried lines(resultset). You may notice that doing an Insert/update/delete is quite different from a select with Prepared statements.

  • I understood, in case, to do the Insert/update/delete would only be with execute, right? or I would need get_result to know if the query was successful.

  • No Insert/update/delete you don’t need get_result(), only no select. @ngthm4r3

  • Could you also add to your answer, an explanation, that array can be instantiated within the loop even without prior outside instances.

  • I get it, taking advantage of this question, how do I fix the character encoding? it returns me the buged characters, in PDO I passed a variable as options, changing the charset to utf8, how can I change? Thanks.

  • @NGTHM4R3 $this->db->set_charset("utf8").

  • @NGTHM4R3 plays this code when creating the object, creates and already arrow the charset, then passes forward the connection.

  • Got it, got it, thanks!

Show 6 more comments

Browser other questions tagged php mysqli

You are not signed in. Login or sign up in order to post.