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 = "";
$html = @file_get_contents($url);

$query = 
    sprintf("replace into mycache(url, html) values('%s', '%s') ",
        mysql_escape_string(gzcompress($html)) );

The following code retrieves the compressed data and extract the text.

$url = "";
$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']);



Tagged with: , ,
Posted in PHP, SQL