A rather interesting issue with mysqli, arrays and result

[niksoft]

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

Prepared statement

Array ( [0] => Array ( [id] => 3 [value] => val3 ) [1] => Array ( [id] => 3 [value] => val3 ) [2] => Array ( [id] => 3 [value] => val3 ) [3] => )

 

Query

Array ( [0] => Array ( [id] => 1 [value] => val1 ) [1] => Array ( [id] => 2 [value] => val2 ) [2] => Array ( [id] => 3 [value] => val3 ) [3] => )

 

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

[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;