It works!
Even though it requires twice as much space on the mysql cache it works perfectly. Thanks for that very usefull code that I could not find anywhere else.
I wouldn't worry about the cache. But if you want, you can uncompress the result on the PHP side.
Assuming you're using the default compression, zlib, then you need to cut off the first 4 bytes before you uncompress the data.
The first 4 bytes is reserved for the length of the uncompressed string.
see:
http://dev.mysql.com/doc/refman/5.0/...ction_compress
Here if a full example using PDO:
<?php
/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=test;host=localhost';
$user = 'root';
$password = '';
try {
$dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$sql = 'SELECT binary(compressed) AS compressed FROM `compressed` WHERE compress(:test) = compressed ';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':test' => 'test'));
$row = $sth->fetch();
// compressed result, with first 4 bytes being the length
$result = $row['compressed'];
// the length of uncompressed string
$len = substr($result, 0, 4);
// the compressed string
$compressed = substr($result, 4, strlen($result)-4);
// uncompress the string
echo gzuncompress($compressed, intval($len)); // test
?>
Edit:
Here is the structure of the table I used in the example:
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| value | text | NO | | NULL | |
| compressed | blob | NO | | NULL | |
+------------+------+------+-----+---------+-------+
And the dump:
--
-- Database: `test`
--
-- --------------------------------------------------------
--
-- Table structure for table `compressed`
--
CREATE TABLE IF NOT EXISTS `compressed` (
`value` text COLLATE utf8_unicode_ci NOT NULL,
`compressed` blob NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `compressed`
--
INSERT INTO `compressed` (`value`, `compressed`) VALUES
('test', 0x04000000789c2b492d2e0100045d01c1);
Offline 
Marked 
