mysqli_stmt::bind_param
A note to people to want to return an array of results - that is, an array of all the results from the query, not just one at a time.
<?php
// blah blah...
call_user_func_array(array($mysqli_stmt_object, "bind_result"), $byref_array_for_fields);
$results = array();
while ($mysqli_stmt_object->fetch()) {
$results[] = $byref_array_for_fields;
}
?>
This will NOT work. $results will have a bunch of arrays, but each one will have a reference to $byref.
PHP is optimizing performance here: you aren't so much copying the $byref array into $results as you are *adding* it. That means $results will have a bunch of $byrefs - the same array repeated multiple times. (So what you see is that $results is all duplicates of the last item from the query.)
hamidhossain (01-Sep-2008) shows how to get around that: inside the loop that fetches results you also have to loop through the list of fields, copying them as you go. In effect, copying everything individually.
Personally, I'd rather use some kind of function that effectively duplicates an array than write my own code. Many of the built-in array functions don't work, apparently using references rather than copies, but a combination of array_map and create_function does.
<?php
// blah blah...
call_user_func_array(array($mysqli_stmt_object, "bind_result"), $byref_array_for_fields);
// returns a copy of a value
$copy = create_function('$a', 'return $a;');
$results = array();
while ($mysqli_stmt_object->fetch()) {
// array_map will preserve keys when done here and this way
$results[] = array_map($copy, $byref_array_for_fields);
}
?>
All these problems would go away if they just implemented a fetch_assoc or even fetch_array for prepared statements...
mysqli_stmt::bind_result
mysqli_stmt_bind_result
(PHP 5)
mysqli_stmt::bind_result -- mysqli_stmt_bind_result — Passa variáveis para um preparado comando por resultado armazenado
Descrição
Modo orientado a objeto (método):
Modo procedural:
Passa colunas em um conjunto de resultados para variáveis.
Quando mysqli_stmt_fetch() é chamada para obter dados, o MySQL client/server protocol coloca a informação das colunas nas especificadas variáveis var1, ... .
Nota: Note que todas as colunas precisam ser determinadas antes de mysqli_stmt_execute() e antes de chamar mysqli_stmt_fetch(). Dependendo do tipo da coluna de determinada variável pode silenciosamente mudar para o tipo do PHP correspondente.
Uma coluna pode ser especificada ou não especificada em dado tempo, até mesmo depois de um conjunto de resultado tem sido parcialmente recebido. A nova passagem pega o efeito da última chamada a mysqli_stmt_fetch().
Parâmetros
- stmt
-
Apenas para estilo de procedimento: Um identificador de statement retornado por mysqli_stmt_init().
- var1
-
A variável a ser determinada.
Valor Retornado
Retorna TRUE em caso de sucesso ou FALSE em falhas.
Exemplos
Exemplo #1 Modo orientado a objeto
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {
$stmt->execute();
/* bind variables to prepared statement */
$stmt->bind_result($col1, $col2);
/* fetch values */
while ($stmt->fetch()) {
printf("%s %s\n", $col1, $col2);
}
/* close statement */
$stmt->close();
}
/* close connection */
$mysqli->close();
?>
Exemplo #2 Modo procedural
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (!$link) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* prepare statement */
if ($stmt = mysqli_prepare($link, "SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) {
mysqli_stmt_execute($stmt);
/* bind variables to prepared statement */
mysqli_stmt_bind_result($stmt, $col1, $col2);
/* fetch values */
while (mysqli_stmt_fetch($stmt)) {
printf("%s %s\n", $col1, $col2);
}
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
O exemplo acima irá imprimir:
AFG Afghanistan ALB Albania DZA Algeria ASM American Samoa AND Andorra
Veja Também
- mysqli_stmt_bind_param() - Passa variáveis para um preparado comando como parâmetros
- mysqli_stmt_execute() - Executa uma preparada query
- mysqli_stmt_fetch() - Obtém resultados de um preparado comando e os coloca nas determinadas variáveis
- mysqli_prepare() - Prepare a SQL statement for execution
- mysqli_stmt_prepare() - Prepara uma declaração SQL para execução
- mysqli_stmt_init() - Initializes a statement and returns an object for use with mysqli_stmt_prepare
- mysqli_stmt_errno() - Returns the error code for the most recent statement call
- mysqli_stmt_error() - Returns a string description for last statement error
add a note
User Contributed Notesmysqli_stmt::bind_result
uramihsayibok, gmail, com
27-Jul-2009 06:35
27-Jul-2009 06:35
Miguel Hatrick
17-Jul-2009 02:18
17-Jul-2009 02:18
Took some cool code from here and made a little class for those object oriented kind of guys
used like this:
<?php
// execute prepared statement
$stmt->execute();
$stmt->store_result();
//custom class :D bind to Statement Result mambo jambo!
$sr = new Statement_Result($stmt);
$stmt->fetch();
printf("ID: %d\n", $sr->Get('id') );
/////////////////////////////////
class Statement_Result
{
private $_bindVarsArray = array();
private $_results = array();
public function __construct(&$stmt)
{
$meta = $stmt->result_metadata();
while ($columnName = $meta->fetch_field())
$this->_bindVarsArray[] = &$this->_results[$columnName->name];
call_user_func_array(array($stmt, 'bind_result'), $this->_bindVarsArray);
$meta->close();
}
public function Get_Array()
{
return $this->_results;
}
public function Get($column_name)
{
return $this->_results[$column_name];
}
}
?>
atulkashyap1 at hotmail dot com
25-Apr-2009 08:31
25-Apr-2009 08:31
bind_ result can also be used to return an array of variables from a function,
This took me a long time to figure out, so I would like to share this.
<?php
function extracting(){
$query="SELECT topic, detail, date, tags
FROM updates
ORDER BY date DESC
LIMIT 5 ";
if($stmt = $this->conn->prepare($query)) {
$stmt->execute();
$stmt->bind_result($updates[0],$updates[1],$updates[2],$updates[3]);
$i=0;
while($stmt->fetch()){
$i++;
$name='t'.$i;
$$name = array($updates[0],$updates[1],$updates[2],$updates[3]);
}
return array($t1,$t2,$t3,$t4,$t5,);
$stmt->close();
}
}
?>
hamidhossain at gmail dot com
01-Sep-2008 12:07
01-Sep-2008 12:07
lot of people don't like how bind_result works with prepared statements! it requires you to pass long list of parameters which will be loaded with column value when the function being called.
To solve this, i used call_user_func_array function and result_metadata functions. which make easy and automatically returns an array of all columns results stored in an array with column names.
please don't forget to change setting variables with your own credentials:
<?php
$host = 'localhost';
$user = 'root';
$pass = '1234';
$data = 'test';
$mysqli = new mysqli($host, $user, $pass, $data);
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
if ($stmt = $mysqli->prepare("SELECT * FROM sample WHERE t2 LIKE ?")) {
$tt2 = '%';
$stmt->bind_param("s", $tt2);
$stmt->execute();
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field())
{
$params[] = &$row[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), $params);
while ($stmt->fetch()) {
foreach($row as $key => $val)
{
$c[$key] = $val;
}
$result[] = $c;
}
$stmt->close();
}
$mysqli->close();
print_r($result);
?>
bb at servertje dot nl
23-Feb-2008 09:24
23-Feb-2008 09:24
Although inspired by an earlier post, this method could be added to any of your database objects. It's an object oriented implementation of an earlier post.
The method returns an array with objects representing a row. Each property represents a column and its value.
<?php
private function getresult($stmt)
{
$result = array();
$metadata = $stmt->result_metadata();
$fields = $metadata->fetch_fields();
for (;;)
{
$pointers = array();
$row = new stdClass();
$pointers[] = $stmt;
foreach ($fields as $field)
{
$fieldname = $field->name;
$pointers[] = &$row->$fieldname;
}
call_user_func_array(mysqli_stmt_bind_result, $pointers);
if (!$stmt->fetch())
break;
$result[] = $row;
}
$metadata->free();
return $result;
}
?>
thejkwhosaysni at gmail dot com
19-Oct-2005 06:12
19-Oct-2005 06:12
I've created these functions which will act like mysqli_fetch_array() and mysqli_fetch_object() but work with bound results.
<?php
function fetch_object() {
$data = mysqli_stmt_result_metadata($this->stmt);
$count = 1; //start the count from 1. First value has to be a reference to stmt.
$fieldnames[0] = &$this->stmt;
$obj = new stdClass;
while ($field = mysqli_fetch_field($data)) {
$fn = $field->name; //get all the feild names
$fieldnames[$count] = &$obj->$fn; //load the fieldnames into an object..
$count++;
}
call_user_func_array(mysqli_stmt_bind_result, $fieldnames);
mysqli_stmt_fetch($this->stmt);
return $obj;
}
function fetch_array() {
$data = mysqli_stmt_result_metadata($this->stmt);
$count = 1; //start the count from 1. First value has to be a reference to the stmt. because bind_param requires the link to $stmt as the first param.
$fieldnames[0] = &$this->stmt;
while ($field = mysqli_fetch_field($data)) {
$fieldnames[$count] = &$array[$field->name]; //load the fieldnames into an array.
$count++;
}
call_user_func_array(mysqli_stmt_bind_result, $fieldnames);
mysqli_stmt_fetch($this->stmt);
return $array;
}
?>
Hope this helps some people, I was puzzled by this for a while.
andrey at php dot net
07-Oct-2005 10:38
07-Oct-2005 10:38
If you select LOBs use the following order of execution or you risk mysqli allocating more memory that actually used
1)prepare()
2)execute()
3)store_result()
4)bind_result()
If you skip 3) or exchange 3) and 4) then mysqli will allocate memory for the maximal length of the column which is 255 for tinyblob, 64k for blob(still ok), 16MByte for MEDIUMBLOB - quite a lot and 4G for LONGBLOB (good if you have so much memory). Queries which use this order a bit slower when there is a LOB but this is the price of not having memory exhaustion in seconds.
matti at withoutthis dot keller dot com
25-Jul-2005 12:30
25-Jul-2005 12:30
Hi
I saw a bit of discussion about using mysqli_stmt_bin_result dynamically, without knowing exactly how many columns will be returned.
After a while i developed this snippet to mimic the same behaviour as mysql_fetch_array():
<?php
# of fields in result set.
$nof = mysqli_num_fields( mysqli_stmt_result_metadata($handle) );
# The metadata of all fields
$fieldMeta = mysqli_fetch_fields( mysqli_stmt_result_metadata($handle) );
# convert it to a normal array just containing the field names
$fields = array();
for($i=0; $i < $nof; $i++)
$fields[$i] = $fieldMeta[$i]->name;
# The idea is to get an array with the result values just as in mysql_fetch_assoc();
# But we have to use call_user_func_array to pass the right number of args ($nof+1)
# So we create an array:
# array( $stmt, &$result[0], &$result[1], ... )
# So we get the right values in $result in the end!
# Prepare $result and $arg (which will be passed to bind_result)
$result = array();
$arg = array($this->stmt);
for ($i=0; $i < $nof; $i++) {
$result[$i] = '';
$arg[$i+1] = &$result[$i];
}
call_user_func_array ('mysqli_stmt_bind_result',$arg);
# after mysqli_stmt_fetch(), our result array is filled just perfectly,
# but it is numbered (like in mysql_fetch_array() ), not indexed by field name!
# If you just want to mimic that ones behaviour you can stop here :)
mysqli_stmt_fetch($this->stmt);
# Now you can use $result
print_r($result);
# But beware! when using the fetch in a loop, always COPY $result or else you might
# end with all the same values because of the references
?>
Hope that this will help someone....
Matt
brad dot jackson at resiideo dot com
22-Mar-2005 03:10
22-Mar-2005 03:10
A potential problem exists in binding result parameters from a prepared statement which reference large datatypes like mediumblobs. One of our database tables contains a table of binary image data. Our largest image in this table is around 50Kb, but even so the column is typed as a mediumblob to allow for files larger than 64Kb. I spent a frustrating hour trying to figure out why mysqli_stmt_bind_result choked while trying to allocate 16MB of memory for what should have been at most a 50Kb result, until I realized the function is checking the column type first to find out how big a result _might_ be retrieved, and attempting to allocate that much memory to contain it. My solution was to use a more basic mysqli_result() query. Another option might have been to retype the image data column as blob (64Kb limit).