Caching SQL Statements with PHP

[PlumPlant]

Hello everyone, I am trying to "cache SQL statements (or rather the result of the statement, not the statement itself, sorry for that)" to avoid too many database queries. However, I have noticed that for example, if there are 100 or more posts/comments but only 20 are shown per page at caching time, I think it would replace the file with the new page if a user navigates between pages. I am trying to avoid this by creating new files for each page, for example: sqlcache_funcname.cache (normal initial page), sqlcache_funcname_page2.cache (page 2), and so on.

I am trying to avoid using third-party libraries.

Below is the PHP code for the caching functionality:

```php
<?php

class sqlCache {

# Folder name where files will be stored
protected $storage = 'cacheSQL';

# Prefix for the files
protected $prefix = 'sqlcache_';

# File extension
protected $extension = '.cache';

// Cache update time in seconds
protected $upgrade = 300; # 5 Min.

public function __construct() {
$this->storage = $this->setStorage();
}

private function setStorage() {
$storage = TS_STORAGE . $this->storage . TS_PATH;
if(!is_dir($storage)) {
mkdir($storage, 0777, true);
}
return $storage;
}

private function getTimeSqlCached(string $file = '') {
if (file_exists($file)) {
return (time() - filemtime($file) < $this->upgrade);
}
return false;
}

public function verifySqlCached(string $cache_key = '') {
return file_exists($this->storage . $this->prefix . $cache_key . $this->extension);
}

public function sqlCached(string $cache_key = '', array $data = []) {
$filename = $this->storage . $this->prefix . $cache_key . $this->extension;

$isCacheValid = $this->verifySqlCached($cache_key) && $this->getTimeSqlCached($filename);
$currentData = $isCacheValid ? unserialize(file_get_contents($filename)) : null;

if (!$isCacheValid || $currentData !== $data) {
file_put_contents($filename, serialize($data));
} else {
$data = $currentData;
}

return $data;
}

public function getSqlCached(string $namekey = '') {
$filename = $this->storage . $this->prefix . $namekey . $this->extension;
if ($this->verifySqlCached($namekey) && !$this->getTimeSqlCached($filename)) {
return unserialize(file_get_contents($filename));
} else {
return null;
}
}

public function setCache(string $key = '', array $data = [], int $max = 0) {
$key .= ($max !== $page OR $max <= 0) ? '' : "_p{$page}";
if(!$this->verifySqlCached($key)) {
return $this->sqlCached($key, $data);
} else {
return $this->getSqlCached($key);
}
}

public function getCachedData($cacheKey) {
if ($this->upgrade && file_exists($cacheFile) && (time() - filemtime($cacheFile) < $upgrade)) {
return unserialize(file_get_contents($cacheFile));
} else {
unlink($cacheFile);
$data = $this->$cacheKey();
file_put_contents($cacheFile, serialize($data));
return $data;
}
}

}
$sqlCache = new sqlCache;
```

The result of `$storage = TS_STORAGE . $this->storage . TS_PATH;` would be something like 'root/storage/cacheSQL/' where the files will be stored.

Explanation:
- `sqlCached()` function creates the cache file.
- `getSqlCached()` function verifies the existence of the cache file.
- `setCache()` function generates and retrieves data from the cache file.

The issue is in `setCache()`. I hope I have explained in a way that makes it clear what I am trying to achieve. Thank you in advance!

(This is part of the ZCode [develop branch] in c.posts.php #190 line)