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.

?>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.

you can use it in a while sentence to fetch and return an assoc array from the statement (as long as the statement is open):usage:$statement = $mysqli->prepare($query));$statement.execute();while($rowAssocArray = fetchAssocStatement($statement)){ //do something}

For those of you trying to bind rows into array,<?php$stmt = $db->prepare('SELECT id, name, mail, phone, FROM contacts');$stmt->execute();$stmt->store_result();$stmt->bind_result($arr['id'], $arr['name'], $arr['mail'], $arr['phone']);while ($stmt->fetch()) {$outArr[] = $arr;}$stmt->close();return $outArr;?>this will give you all the rows you asked for except that they would all be the same as the first one because of some gremlins in the background code (i've heard that PHP is trying to save memory here).

According to the above documentation:
"Depending on column types bound variables can silently change to the corresponding PHP type. "

if you specify a field as int (tinyint, mediumint, etc.) with zerofill property, it will be converted (silently) to PHP integer (erasing the leading zeros). In order to keep those leading zeros, one solution is to specify the field as decimal.

Note that this only happens when using prepared statements and not when executing the query directly.

Here's the complete example.
WARNING: When using 'prepare' to prepare a statement to retrieve LOBs the method order matters.
Also, method 'store_result()' must be called and be called in correct order.
Failure to observe this causes PHP/MySQLi to crash or return an erroneous value.
The proper procedure order is: prepare -> execute -> store_result -> bind -> fetch

To clarify for anyone having problems with arrays, PHP will automatically pass arrays as references, cloning the array if needed in the event of setting or unsetting a part of it, changing a referenced variable does not trigger cloning.

This is done for efficiency, to clone an array containing this information you may either use a foreach loop, or set/unset a key. Techniques like array_values will also work provided you don't mind losing your keys.

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;

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).