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;

You should remove all of your suppressed function calls, ie. the `@` preceding `unserialize`, `mysql_num_rows`, etc. just to rule out errors that may be present from those calls. — segFault, Jan 03 '16 at 15:04

segFault · Accepted Answer · 2016-01-03T15:37:52.200

4

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;

edited Jan 03 '16 at 15:37

answered Jan 03 '16 at 15:15

segFault

3,887
1
19
31

you need to add a semicolon after `$data[] = $row` or it will generate PHP parse error. Thanx for your help – Ya Basha Jan 03 '16 at 15:35
so basically this is a no-write-through-approach for the update which is very nice. Any suggestions on how to modify the code and use it to "purge" any queries that are affected by the update, insert, delete (a write-through-approach) – OAH Mar 19 '20 at 08:06
@OAH In theory, you would perform a similar lookup for the equivalent "SELECT" statement(s) for the now deleted/updated row(s) as soon as you perform the delete/update operation. It would be ideal that you know the exact lookup queries your application uses as that makes the purge/refresh less complex. The process would perform a delete/update on the cached result based on what you are doing. If you need assistance with that, please post a new question after you have attempted something to that effect and if you have any trouble! – segFault Mar 19 '20 at 12:13
@segFault I will give it a try and will let you know for sure. Thx a ton for the tip :) – OAH Mar 20 '20 at 10:47

store mysql query in redis using predis

1 Answers1