Matt Matt - 6 months ago 24
PHP Question

custom mysqli database class returning data it shouldn't

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);
}

Answer

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.

Comments