custom mysqli database class returning data it shouldn't

Question

I am creating a MySQLi database class for my own personal use to clean up the prepared statements usage within functions on my own code and for my own learning and experience. However I have run into a complication when running 2 or more queries on a single page.

Using my basic SELECT function I can run 2 queries as such:

$data = $db->SELECT("SELECT * FROM hist WHERE id = ?", array('i', 2));
$data2 = $db->SELECT("SELECT * FROM hist WHERE id = ?", array('i', 3));

and as seen, these two queries are saved to separate variables.

Doing a var_dump on $data shows me the correct result, for id=2:

array(1) {
  [0]=>
  array(5) {
    ["id"]=>
    int(2)
    ["value"]=>
    float(51.4)
  }
}

However doing a var_dump on $data2 shows me the data for id=2 and id=3 which sort of suggests the results are concatenating with each other?

array(2) {
  [0]=>
  array(5) {
    ["id"]=>
    int(2)
    ["value"]=>
    float(51.4)
  }
  [1]=>
  array(5) {
    ["id"]=>
    int(3)
    ["value"]=>
    float(476)
  }
}

What could be causing this and how could this be fixed? Do I have to initiate a new instance of the class per query? I tried to unset the resulting data at the end of the function but this didn't seem to help.

My SELECT function from my class:

public function SELECT($sql, $args=null) {
    if ($stmt = $this->link->prepare($sql)) {
        if(isset($args)) {
            $method = new ReflectionMethod('mysqli_stmt', 'bind_param'); 
            $method->invokeArgs($stmt, $this->refValues($args));
        }
        if(!$stmt->execute()) {
            array_push($this->err, 'execute() failed: ' . htmlspecialchars($stmt->error));
        }           
        $result = $stmt->get_result();

        if (count($result) >= 1) {
            while($row = $result->fetch_array(MYSQLI_ASSOC)) {
                array_push($this->rs, $row);
            }
        } 
        else {
            array_push($this->rs, "No data has been returned.");
        }
    }
    else {
        array_push($this->err, 'prepare() failed: ' . htmlspecialchars($this->link->error));
    }

    if(!empty($this->err)) {
        if($this->debug) {
            return $this->err;
        }
    }
    else {
        return $this->rs;
    }
    $stmt->close();
    unset($this->err);
    unset($this->rs);
}

Doing a var_dump on $data2 shows me the data from both $data and $data2: that's not clear... At first glance, I'd say it's just not possible... So, I certainly misunderstand this sentence... What are $data and $data2 supposed to contain? What does your table contains? — Arcesilas
– Arcesilas, Commented May 20, 2016 at 23:58
@Arcesilas If i var_dump $data i get 1 result, for id=2 as expected. If i var_dump on $data2 i get 2 results, 1 for id=2 and 1 for id=3 which is not expected. It seems to be 'adding' the two results together query after query. I am sure the database is correct, especially since my ID is a pk and of course can only return 1 result if its a pk. — Matt
– Matt, Commented May 21, 2016 at 0:03
@Arcesilas I have edited my question - I hope this is clearer? — Matt
– Matt, Commented May 21, 2016 at 0:06
First, we don't know what's in your DB. Second, we don't know with which parameters you execute your queries. Third, there are methods you use but don't show (like refValues()). Conclusion: it's not easy to help... :) — Arcesilas
– Arcesilas, Commented May 21, 2016 at 0:07
What's the point of that: $method = new ReflectionMethod('mysqli_stmt', 'bind_param');? Why not just call $stmt->bind_param()? — Arcesilas
– Arcesilas, Commented May 21, 2016 at 0:10

TheDrot · Accepted Answer · 2016-05-21 00:26:34Z

1

Problem is this part.

if(!empty($this->err)) {
    if($this->debug) {
        return $this->err;
    }
}
else {
    return $this->rs;
}
$stmt->close();
unset($this->err);
unset($this->rs);

You are returning either $this->rs or $this->err so unset() never executes or more precisely as Arcesilas said, it unsets variables only if there is error and debugging is off.

Store result in temp variable and return result.

$stmt->close();
if(!empty($this->err)) {
    $result = $this-err;
    unset($this-err);
    if($this->debug) {
        return $result;
    }
}
else {
    $result = $this->rs;
    unset($this->rs);
    return $result;
}

You could refactor this with cleaner code but you get the idea.

Easier alternative is to just move

unset($this->err);
unset($this->rs);

to the top of function SELECT.

edited May 21, 2016 at 0:26

answered May 21, 2016 at 0:10

TheDrot

4,3381 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Arcesilas Over a year ago

They are executed if there is an error an debug is off.

Matt Over a year ago

What would the fix be? how can I unset it after return, or reset the rs variable upon a new use of the class?

TheDrot Over a year ago

@Arcesilas Yes, apart from that.

Arcesilas Over a year ago

Store the values in a temporary variable, unset, return the temporary variable.

Matt Over a year ago

I had to add in a final re-declaration of the arrays rs/err but otherwise it worked great. Thanks.

|

Collectives™ on Stack Overflow

custom mysqli database class returning data it shouldn't

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related