store mysql query in redis using predis

Question

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';
Predis\Autoloader::register();

$redis = new Predis\Client(array(
    "scheme" => "tcp",           
    "host" => "127.0.0.1",       
    "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 1

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) {
        die(mysql_error());
    }
    // 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;