Ya Basha Ya Basha - 2 years ago 184
SQL Question

store mysql query in redis using predis

I want to use redis cache to store mysql queries in redis, the first time it works as expected (because there is no key in redis) and execute the query, but later

$rs = @unserialize( $redis->get($key)
returns nothing; I tried many solutions but no luck, my code below:

require __DIR__ . '/vendor/autoload.php';

$redis = new Predis\Client(array(
"scheme" => "tcp",
"host" => "",
"port" => 6379,
"password" => "testRedis"));

$sql="SELECT * FROM news where status=1 and (title like \"%$sword%\" or body like \"%$sword%\" or name like \"%$sword%\" or rss like \"%$sword%\")";

$key = "sql_cache:" . md5($sql);

if ($rs = @unserialize( $redis->get($key) ) === false){

$rs = mysql_query($sql)or die(mysql_error());

// Put data into cache for 1 hour
$redis->set($key, serialize($rs));
$redis->expire($key, 3600);
echo 'rs= '.$rs;
$nr = @mysql_num_rows($rs);
echo "Number of rows: " . $nr;

Answer Source

You need to iterate through your query results, the resource returned by mysql_query cannot be serialized.

You could try something like:

$key = "sql_cache:" . md5($sql);
// If $key exists get and unserialize it, otherwise set $data to empty array
$data = $redis->exists($key) 
    ? unserialize($redis->get($key)) 
    : array();

if (empty($data)) { 
    $rs = mysql_query($sql);       
    if ($rs === false) {
    // Iterate through results...
    while ($row = mysql_fetch_assoc($rs)) {
        $data[] = $row;

    // Put data into cache for 1 hour                   
    $redis->set($key, serialize($data));                  
    $redis->expire($key, 3600);                           
echo 'data= '.$data;                                   
$nr = count($data);                             
echo "Number of rows: " . $nr;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download