How do i store / serialize php returned mysqli_query results?

I’m trying to store results returned from mysqli_query as cache, so if the same “SELECT” statement is executed, it will get from the cache instead of going through mysql server BUT the following code, no matter what i tried with serialize / json_encode,decode or even just storing output as variable, i can’t do

        $row = $this->result->fetch_object();

Code as below. Does anyone know why? How do I just store the returned results and re use it? it does not work with $this->result = mysqli_query($dbh, $query);

                global $dbcache;
//                      $this->result = mysqli_query( $dbh, $query );
                if (isset($dbcache[$query])){
                        $this->result = json_decode($dbcache[$query]);
//                        echo 'JSON? = '.$query.'<br>'.$dbcache[$query];
                }else{
//                      echo ' === '.$dbcache[$query].' === '."n";
                        $this->result = mysqli_query( $dbh, $query );
                        //$dbcache[$query] = serialize($this->result);
                        $dbcache[$query] = json_encode($this->result);
//                      echo 'Serialize? = '.$query."n".hash('sha3-512' , $query).'<br>';
                }

Answer

You should not store mysqli_result object for longer than necessary to fetch the data. It is only meant to be used as a temporary object used for fetching the data from MySQL. Cache the values instead.

if (isset($dbcache[$query])) {
    $this->result = $dbcache[$query];
} else {
    $dbcache[$query] = $this->result = $dbh->query($query)->fetch_all(MYSQLI_ASSOC);
}

However, I would be wary of how useful this approach is with prepared statements. You would probably want to make the key a combination of the query and parameters. For example:

$key = serialize([$query, ...$params]);
if (isset($dbcache[$key])) {
    $this->result = $dbcache[$key];
} else {
    //prepare bind execute
    $stmt = $dbh->prepare($query);
    if ($params) {
        $stmt->bind_param(str_repeat("s", count($params)), ...$params);
    }
    $stmt->execute();

    // Fetch results into multidimensional array
    $dbcache[$key] = $this->result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

You really should abstract from using mysqli functions in your application code. If you are building some kind of abstraction layer than you should only return the data to the application and use mysqli only internally. Using my recommended approach you would be accessing the data as a normal array; no more fetch_object() methods. Of course, this requires that you change your application code, but it is a good idea to do so. I would also strongly advise to start using PDO.