note 109594 added to mysqli-stmt.fetch

A quick improvement to denath2 at yahoo dot com's comment. I felt it was lacking some depth for those interested in how to apply the fix and why it even works. Here goes. Here is how you may have attempted to save the results of each row to an array. <?php // Example One $stmt=$mysqli->stmt_init(); $stmt->prepare('SELECT col1, col2 FROM table'); $stmt->execute(); $stmt->bind_result($result['col1'], $result['col2']); $data=array(); while($stmt->fetch()){ $data[]=$result; } $stmt->close(); ?> Which should in theory leave you with $data as an array full of arrays to the number of rows returned. That is what I did and was bemused as to why it didn't work. Especially when I did this and it did work. <?php // Example Two // ... while($stmt->fetch()){ var_dump($result); } // ... ?> Hmm... makes no sense you'll agree? Well actually once you get your head around how fetch() works it will make perfect sense. So here is the fix you need to apply. I will explain afterwards how and why it works, just read it before anything else. <?php // Example Three // ... while($stmt->fetch()){ $row=NULL; foreach($result as $key=>$value){ $row[$key]=$value; } $data[]=$row; } // ... ?> First off you need to know that fetch() returns a reference to the data you want, not the actual data. This means that when you var_dump() out the $result variable, you are outputting that row's data. On the next loop of your while() the $result gets wiped and filled up with the next row's data and then outputted. So in the while() loop in example one you are filling your $data array up with REFERENCES to your data (not the actual data). So in example one your $data array acually looks like this. <?php // Example Four // $data[0]=$referenceToRow; // $data[1]=$referenceToRow; // $data[2]=$referenceToRow; // $data[...]=$referenceToRow; ?> This means that in example one on the last loop of your while() the variable $result is equal to this. <?php // Example Five // $result=array(['col1']=>'lastRowCol1Value', ['col2']=>'lastRowCol2Value'); ?> Meaning that example four becomes this. <?php // Example Six // $data[0]=array(['col1']=>'lastRowCol1Value', ['col2']=>'lastRowCol2Value'); // $data[1]=array(['col1']=>'lastRowCol1Value', ['col2']=>'lastRowCol2Value'); // $data[2]=array(['col1']=>'lastRowCol1Value', ['col2']=>'lastRowCol2Value'); // $data[...]=array(['col1']=>'lastRowCol1Value', ['col2']=>'lastRowCol2Value'); ?> All your references are pointing to the $result array which now contains the results of the last row you requested in your query! Obviosuly this means all your data is now the same!!! The fix in example three takes the reference and loops through it (at that point in the script whilst it still contains the data you want) and saves it to a temporary variable ($row) whilst mantaining the structure. This is then added to the $data array and because it is a real variable (not a reference to another variable) it will retain its value in $data. Hope this helped someone as it was driving me crazy for a day trying to find a fix to this problem. It then drove me crazy for another day as to why it was working. I hate implementing code in my applications without knowing what it does. Au revoir! Tldr; Replace the while loop in example one with the while loop in example three and you have perfect results waiting for you in $data ;) ---- Server IP: 69.147.83.197 Probable Submitter: 81.109.241.129 ---- Manual Page -- http://www.php.net/manual/en/mysqli-stmt.fetch.php Edit -- https://master.php.net/note/edit/109594 Del: integrated -- https://master.php.net/note/delete/109594/integrated Del: useless -- https://master.php.net/note/delete/109594/useless Del: bad code -- https://master.php.net/note/delete/109594/bad+code Del: spam -- https://master.php.net/note/delete/109594/spam Del: non-english -- https://master.php.net/note/delete/109594/non-english Del: in docs -- https://master.php.net/note/delete/109594/in+docs Del: other reasons-- https://master.php.net/note/delete/109594 Reject -- https://master.php.net/note/reject/109594 Search -- https://master.php.net/manage/user-notes.php