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