Jump to content

A rather interesting issue with mysqli, arrays and result


niksoft

Recommended Posts

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 :wtf:

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

Link to comment
Share on other sites

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;

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.