Implementation of Php+mysql Cache technology _php Tutorial

Suitable for readers

This tutorial is suitable for PHP programmers who are interested in caching SQL queries to reduce the load on database connections and execution, and improve script performance.
Overview

Many sites use databases as containers for site data storage. The database contains production information, directory structure, articles, or message books, some of which are likely to be completely static, and will benefit greatly from a cache system.
Such a system can block the connection of the database by caching the results of the SQL query to a file in the system, thereby increasing the response time by constructing the query and obtaining the return result.
Some system databases are not placed on the Web server, which requires a remote connection (TCP or something like that), or a large amount of data from the database, so you have to endure more time, which depends on the system response time and resource utilization.
Premise

This tutorial uses MySQL as a database. You need to install MySQL (www.mysql.com download is valid) and activate the PHP MySQL extension (which is activated by default).
Because you want to query the database, you need to know some basic knowledge of SQL (Structured Query language).
Caching SQL query Results
Why would I cache query results?
Caching query results can greatly improve script execution time and resource requirements.
Caching SQL query results also allows you to post-process data. If you use the file cache to store the output of all scripts (HTML output), this may not work.
When you execute an SQL query, the process of the DOT code is:
L Connecting to a database
L Preparing SQL queries
L Send query to Database
L Get return results
L To close a database connection
The above methods are very resource-intensive and adversely affect the performance of the script. These two elements can only be reconciled by the large number of returned data and the location of the database server. Although persistent connections can improve the load when connecting to a database, it is very memory intensive, and if you get a large amount of data, the whole time of storage is very short.
Create an SQL query:
SQL (Structured Query Language) query is used as an interface to manipulate the database and its contents. SQL can be used to define and edit the structure of a table, insert data into a table, update or delete information in a table.
SQL is the language used to communicate with data, and in most PHP database extensions (mysql,odbc,oracle, etc.) manage the entire process by passing SQL queries into the database.
In this tutorial, only the Select language is used to get the data in the database. The data will be cached and then used as the data source.
Decide when to update the cache:
Depending on the needs of the program, the cache can take many forms. The 3 most common ways are:
L Time-triggered cache (expired timestamp)
L Content changes trigger cache (update cache accordingly when data changes are found)
L Manually triggering the cache (manually informing the system that the information is overdue and forcing new caches to occur)
Your caching requirements may be one or more of the above-mentioned principles of synthesis. This tutorial discusses how time is triggered. However, in a comprehensive caching mechanism, 3 methods of synthesis will be used.
Cached results:
The basic cache is the PHP two functions serialize () and Unserialize () (these two functions represent serialization and deserialization respectively).
The function serialize () is used to store the value of PHP, which guarantees the type and structure of the values without losing them.
In fact, the session extension of PHP is a serialized variable that stores the session variable ($_session) in a file in the system.
The function unserialize () Reverses the above operation and returns the serialized string to its original structure and data content.
In this example, take an e-commerce store as an example. The store has 2 basic tables, categories and products (here is the original database table name). The Product table may change every day, and categories remains constant.
To display the product, you can use an output cache script to store the output HTML results into a file. However, the Categories table may require post-processing. For example, all directories are displayed through the variable category_id (obtained through $_request[' category_id '), and you may want to highlight the currently selected directory.

Table Categories Structure
Field Type Key Extra
category_id Int (Ten) unsigned PRI auto_incremen
Category_name varchar (255)
Category_description text
In this example, the time-triggered caching technique is applied to set a period of time after which the cached SQL output expires. In this particular case, the set time is 24 hours.
Examples of serialization:
L Connect to database
L Execute Query
L Get all the results that make up an array so you can access it later
serializing arrays
L Save serialized Array to file */

$file = ' sql_cache.txt ';
$link = mysql_connect (' localhost ', ' username ', ' password ') or Die (Mysql_error ());
mysql_select_db (' Shop ') or Die (Mysql_error ());

/* Construct SQL query */
$query = "SELECT * FROM Categories";
$result = mysql_query ($query) or Die (Mysql_error ());
while ($record = Mysql_fetch_array ($result)) {
$records [] = $record;
}
$OUTPUT = serialize ($records);
$fp = fopen ($file, "w"); Open a file as a write permission
Fputs ($fp, $OUTPUT);
Fwrite ($fp, $OUTPUT);
Fclose ($FP);

/* To view the Sql_cache.txt file, the contents may resemble this:
A:1:{i:0;a:6:{i:0;s:1: "1"; s:11: "category_id"; s:1: "1"; I:1;s:9: "Computers"; s:13: "Category_name"; S:9: "Computers"; I:2;S:25: "Description for Computers", s:20: "Category_description"; s:25: "Description for Computers";}}
This output is the internal representation of its variables and types. If you use the Mysql_fetch_array () function to return an array of numeric indexes and an associative array (that's why the data looks like it happened two times), one is a numeric index and the other is a string index.
Using the cache:
To use the cache, you need to use the function unserialize () to restore the data to the original format and type.
You can use the file_get_contents () function to read the contents of the Sql_cache.txt file and assign it to a variable.
Please note: This function is valid in PHP4.3.0 and above. If you are using an older version of PHP, an easy way is to use the file () function (read the entire document into an array, each line becomes an array). The implode () function is used to concatenate elements of an array into a string and then deserialize using Unserialize (). */

File_get_contents () Suitable for PHP < 4.3.0
$file = ' sql_cache.txt ';
$records = unserialize (Implode (', File ($file)));
You can now pass the $records array and get the data from the original query:
foreach ($records as $id = = $row) {
Print $row [' category_name ']. "
";
}
/* Note that $records is an array (a numeric index column that contains the results of the query--each row is a number and a string ...). A row of chaos).
Put them in one piece:
Based on the time in this example, decide whether to cache. If the timestamp of the file modification is greater than the current time stamp minus the expiration timestamp, then cache is used, otherwise the cache is updated.
L Check if the file exists and the timestamp is less than the set expiration time
L Get the records stored in the cache file or update the cache file
$file = ' sql_cache.txt '; */
$expire = 86400; 24 Hours (unit: seconds)
if (file_exists ($file) &&filemtime ($file) > (Time ()-$expire)) {
Get the records in the cache
$records = Unserialize (file_get_contents ($file));
} else {
Create a cache with the Serialize () function
}
/* Attach other possible:
L Store cached results in shared memory for faster speeds
L Add a function to run the SQL query randomly and check if the output is consistent with the cached output. If not, update the cache (the probability of this function's run count can be set to 1/100). A hashing algorithm (such as MD5 ()) helps to determine whether a string or file has changed.
L Add an administrator function to manually delete this cache file to force the update of the cache (such as when the File_exists () function returns false). You can use the function unlink () to delete a file.
Script: */

$file = ' sql_cache.txt ';
$expire = 86400; 24 hours
if (file_exists ($file) &&filemtime ($file) > (Time ()-$expire)) {
$records = Unserialize (file_get_contents ($file));
$records =unserialize (Fread ($file, FileSize ($file)));
} else {
$link = mysql_connect (' localhost ', ' username ', ' password ') or Die (Mysql_error ());
mysql_select_db (' Shop ') or Die (Mysql_error ());
/* Construct SQL query */
$query = "SELECT * FROM Categories";
$result = mysql_query ($query) or Die (Mysql_error ());
while ($record = Mysql_fetch_array ($result)) {
$records [] = $record;
}
$OUTPUT = serialize ($records);
$fp = fopen ($file, "w");
Fputs ($fp, $OUTPUT);
Fwrite ($fp, $OUTPUT);
Fclose ($FP);
}//End Else

The results of the query are in the array $records
foreach ($records as $id = = $row) {
if ($row [' category_id '] = = $_request[' category_id ']) {
The selected directory displays bold characters
print '. $row [' category_name ']. '
';
} else {
Other directories are displayed with regular fonts
Print $row [' category_name ']. '
';
}
}//End foreach

http://www.bkjia.com/PHPjc/478006.html www.bkjia.com true http://www.bkjia.com/PHPjc/478006.html techarticle Suitable for readers this tutorial is suitable for PHP programmers who are interested in caching SQL queries to reduce the load on database connections and execution, and improve script performance. Overview Many sites use the database for ...

