Select with prepared statements MySQLi

Question

Select with prepared statements MySQLi

Navigation

#1 by (7 votes)

9

//// The connection is of type 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 I would like the fetch to return an array, and how can I choose the array type? associative or otherwise?

php mysqli

asked by anonymous 18.10.2015 / 00:44

1 answer

How do I get the current position of a div by its class? What's the difference between SOME, IN and ANY?

score 7 · Accepted Answer

When using prepared staments with MySQLi in a select, you first need to pass the result from the database 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 is done with bind_result ()

Change 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;
}