How to save 1000 or more sql by storing data in file

Storing Data with PHP, Save mysql query or sql, Reduce MySQL load, Improve page loading speed, php store data without database, How do I store an array in a file, Save array to PHP file, php – Print array to a file, php save array to file and read, php print array to file.

Save 1000 or more sql and speed up your webpage by storing MySQL result data in inc or php file. Here example given with detailed example, how to store MySQL or long processed data in php file and use later for speed up page loading.

For using this technique, first we have to decide which data used most from database and not update frequently. Also some time we prepare data after long process. We can also save this data by this process. Technique is like cache data and reuse.

First step to store MySQL data in file and use in code

Example for category list, suppose we always get category list from database. So every time page loads sql fire. If page load 1000 time sql fire 1000 time. To overcome this issue here is simple example given.

Add function for write array in file

Add below function to your function.php file with your existing functions.

/**
 * Create and store array data in file.
 * @param array $arr - array prepared from database
 * @param string $fileName - filename with path (e.g., path/dir/filename.inc)
 * @param string $arrName - variable name of array data
 * @return boolean - return true on successfully file create
 */
function createDataFile($arr, $fileName, $arrName)
{
    //PREPARE FILE CONTENT
    $fileData = '<?php';
    $fileData .= "\n".$arrName.' = '.var_export($arr, true).';';
    $fileData .= "\n";

    //WRITE DATA IN FILE
    $result = file_put_contents($fileName, $fileData);
	
    return $result ? true : false;
}

Check datafile and create file if not exist

Now in file where you want to use category list, get category list by below sequence.

  • Check category data file exist
  • If file exist include file in code
  • Check category list array exist, if file included it found else not found
  • If array not found get data from MySQL database, prepare category array and call function to save array in file
  • Now use category array in code

From now when page refresh second time, category list data file found and array used from file. In this way we can skip MySQL use again and reduce sql queries.

//INCLUDE FILE IN CODE AND USE ARRAY DATA
if(file_exists('category.inc'))
    include 'category.inc';

//CHECK DATA GET FROM FILE, IF NOT GET FROM DATABASE AND SET IN FILE
if(empty($categoryArr))
{
    //=> Process for get data from database
    //=> For example get  category_id => category_name
    //=> Loop data and prepare data in array format like below array
    $categoryArr = array('1' => 'Category1',
        '2' => 'Category2',
        '3' => 'Category3',
        '4' => 'Category4');

    //create file from data array
    createDataFile($categoryArr, 'category.inc', '$categoryArr');
}

print_r($categoryArr);

Update category data file on category update

Important point, we need to update file when data update from admin. We have to add code that will prepare fresh category array and call function to create new data file.

//=> Process for get data from database
//=> For example get  category_id => category_name
//=> Loop data and prepare data in array format like below array
$categoryArr = array('1' => 'Category1',
    '2' => 'Category2',
    '3' => 'Category3',
    '4' => 'Category4');

//create file from data array
createDataFile($categoryArr, 'category.inc', '$categoryArr');