PHP MySQL Query Cache — Best Way

With this method, you can prevent excessive database usage by Cache the data you query for certain hours.

You can stop Flood attack this way.

Using 3 Functions for Cache Query

Creating Cache

function create_cache($file,$json_data){
 global $directory;
 global $file_ext;
 
 if (!file_exists($directory)) { mkdir($directory, 0777, true); } // Create Cache Folder if not Exist
 
 $file = $directory.$file.$file_ext; // .cache is custom, change how you want
 $json_data = json_encode($json_data);
 
 $f=fopen($file,’w+’);
 fwrite($f,$json_data);
 fclose($f);
 }

Get Cache

function get_cache($file){
 global $directory;
 global $file_ext;
 $file = $directory.$file.$file_ext;
 return json_decode(file_get_contents($file),TRUE);
 }

Check Cache

function check_cache($file,$time){
 global $directory;
 global $file_ext;
 
 $time = $time * 60;
 $file = $directory.$file.$file_ext;
 
 if (!file_exists($directory)) { mkdir($directory, 0777, true); } // Create Cache Folder if not Exist
 
 if (!file_exists($file) || filemtime($file) < (time() — (int)$time)) {
 return false;
 } else {
 return true;
 }
 }

• First we have to Check cache file exist and created (updated) time.
• If file not exist Run SQL codes and create cache file Query results.
• OR cache file time out recreate cache file with Query results.
• File ext is customizable
• Cache data is customizable (JSON encode/decode or Encrypt your data how you want)
• Time is customizable

Completely Codes

// Database Connection with PDO
 $localhost = “localhost”;
 $db_user = “root”;
 $db_pw = “”;
 $database = “test”;
 
 $db = new PDO(“mysql:dbname=$database;host=$localhost;”, “$db_user”, “$db_pw”);
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
 // Parameters
 $file_name = md5(“testcache_query”); // Encrypt File Name with MD5
 $file_ext = “.cache”; // File Extension
 $keep_time = 300; // Minutes (5 Hours)
 $directory = __DIR__.”/cache/”; // Directory Custom
 
 // Query && Check Cache Time
 
 $cache_control =…