Store compressed data in database using PHP
When storing big text contents in a database, it is better to store after compressing the data. The following is an example storing HTML contents in a database cache.
We first create a table mapping URLs to HTML contents. The field for HTML contents is BLOB since we will store compressed data.
CREATE TABLE mycache (
url varchar(128) PRIMARY KEY,
html blob default NULL,
updated_time timestamp NOT NULL default CURRENT_TIMESTAMP
);
In PHP, use gzcompress and gzuncompress to compress and extract data. The following code compresses the text before storing into the database.
$url = "http://theoryapp.com/";
$html = @file_get_contents($url);
$query =
sprintf("replace into mycache(url, html) values('%s', '%s') ",
mysql_escape_string($url),
mysql_escape_string(gzcompress($html)) );
mysql_query($query);
The following code retrieves the compressed data and extract the text.
$url = "http://theoryapp.com/";
$query = sprintf("select * from mycache where url = '%s' ",
mysql_escape_string($url) );
$result = mysql_query($query);
if ($result && $row = mysql_fetch_assoc($result))
{
$html = gzuncompress($row['html']);
}