niksoft

Sorry i just saw a typo: if(!$type || strtoupper($type)=="MYSQLI_NUM") return array_values($this->bind_arr); else return $bind_arr; should read if(!$type || strtoupper($type)=="MYSQLI_NUM") return array_values($this->bind_arr); else return $this->bind_arr;

I posted this on php-forums but i think they might be dead as i haven't gotten a reply in about 4 days now, so i'll see if anyone here has had a similar issue. I was pretty disappointed with PHPs handling of results with mysqli, rather, the difference in handling results of a query vs a prepared statement, so i decided to write a class around these differences to access mysql results in a fairly straight forward, way, not unlike how you would do it with a query, but i ran into an issue whereby when passing member array back from a method to another method, seems to only pass the array by reference, and when using it, no way i have been able to figure out that will assign that passed array by value (and this is where PHPs lack (or at least i have not been able to find it) of an explicit dereference operator, and the built in "smarts" make inefficiencies in the end code, due to having to work-around the issue) ok first the problem code (please dont scream at the sloppy bits, well, maybe a little, just don't enjoy it too much ): <?php class MyException extends Exception{} function throwMyException($message = null, $code = null) { throw new MyException($message, $code); } class MyResult { protected $result, $bind_arr=array(); function __construct(&$result) { if(!$result && get_class($result!="mysqli_stmt")) throw new MyException("No result set was available"); else { $this->result = $result; //this is kind of a dirty but kind of a cool way to solve this issue, but basically this binds array fields to the prepared statement result set to magicify this result class if(get_class($result)=="mysqli_stmt") { $fields = $this->result->result_metadata() or throwMyException("The result contains no data" . $this->result->error()); $fields = $fields->fetch_fields() or throwMyException("Could not fetch fields: " . $this->result->error()); $bind_cmd = '$this->result->bind_result('; foreach($fields as $field) { $bind_cmd .='$this->bind_arr[\''.$field->name.'\'],'; } $bind_cmd = substr($bind_cmd, 0, -1).");"; eval($bind_cmd); } } } function getOne($type=NULL) { if(get_class($this->result)!="mysqli_stmt") { if($this->result->num_rows < 1) throw new MyException ("We need one or more results in the result set first"); if(!$type) return $this->result->fetch_row(); else { switch(strtoupper($type)) { case "MYSQLI_ASSOC": return $this->result->fetch_array(MYSQLI_ASSOC); break; case "MYSQLI_NUM": return $this->result->fetch_array(MYSQLI_NUM); break; default: return $this->result->fetch_array(MYSQLI_BOTH); } } } else { if(!$this->result->fetch()) return false; if(!$type || strtoupper($type)=="MYSQLI_NUM") return array_values($this->bind_arr); else return $bind_arr; } } function getArray($type=NULL) { while($ret[] = $this->getOne($type)){} (get_class($this->result)=="mysqli_stmt") ? $this->result->free_result() : $this->result->free(); // Free the original result set return $ret; } } and a use case SQL: create table test_table (id int not null auto_increment primary key, value varchar(); insert into test_table (`value`) values ("val1"), ("val2"), ("val3"); test php code: <?php require_once "MyResult.php"; $conn = new mysqli("localhost", "user", "pass", "db"); if($conn->connect_errno) die('Connect Error: ' . $conn->connect_error); //PREPARED STATEMENT $stmt = $conn->prepare("select * from test_table limit 3;"); $stmt->execute(); if($stmt->errno) die('Prepare Error: ' . $stmt->error); try { $res = new MyResult($stmt); echo "Prepared statement<br/>"; print_r($res->getArray(NULL, "MYSQLI_ASSOC")); echo "<br/><br/>"; } catch (MyException $e) { die('Database Error: ' . $e->getMessage()); } $stmt->close(); //juust to be polite // QUERY $stmt = $conn->query("select * from test_table limit 3"); if($stmt->errno) die('Query Error: ' . $stmt->error); try { $res2 = new MyResult($stmt); echo "Query<br/>"; print_r($res2->getArray(NULL, "MYSQLI_ASSOC")); echo "<br/><br/>"; } catch (MyException $e) { die('Database Error: ' . $e->getMessage()); and the rather unexpected results So the way i had to work around it (after playing with it for a few hours, and i tried a lot of different approaches) is function getOne($type=NULL) { if(get_class($this->result)!="mysqli_stmt") { if($this->result->num_rows < 1) throw new MyException ("We need one or more results in the result set first"); if(!$type) return $this->result->fetch_row(); else { switch(strtoupper($type)) { case "MYSQLI_ASSOC": return $this->result->fetch_array(MYSQLI_ASSOC); break; case "MYSQLI_NUM": return $this->result->fetch_array(MYSQLI_NUM); break; default: return $this->result->fetch_array(MYSQLI_BOTH); } } } else { if(!$this->result->fetch()) return false; while (list($key, $val) = each($this->bind_arr)) { $tmp[$key] = $val; } reset($this->bind_arr); if(!$type || strtoupper($type)=="MYSQLI_NUM") return array_values($tmp); else return $tmp; } } So, this, with the same test code will produce the same result set for both the query an prepared procedure. The problem seems to be that $bind_arr is being passed to getArray as a reference, and when appending to the $ret, it is appended as a reference, without me explicitly doing so, so if you were to print the $ret array evey time in the while loop, you would see that with each result, each value in $ret is updated with every result to the current result in the $bind_arr. This is only the issue with returned $this->bind_arr though, returned array from fetch_array works juust as you would expect it to. Once again, maybe i am overlooking something, maybe there is a dereference operator in php that i dont know of, or maybe this is a bug in PHP 5.2.10 (if it works fine on another version, please let me know), oor maybe its something i should write to php guys about as a bug, and kindly ask them to fix it (if you guys too think it's a problem) Anyways, thanks in advance for any advice, thoughts, comments on this matter, -- Alex