beginner's MySQL/memcache/PHP question

[John Kramlich]

Josh Meyer wrote:
>
> I use mysqli in PHP 5 right now and want to start incorporating the 
> use of memcache.  I'm able to store something in memcache, but I'm not 
> quite able to get things out.
> I put data in with this:
>    $db = new mysqli('host','user','password','database');
>    $sql = ... <a SELECT statement>
>    $result = $db->query($sql);
>    $memcache->set($memcache_key,$result,TRUE,86400) or die ("Failed to 
> save data at the server");
>
> The output of this:
>    $cached_result = $memcache->get($memcache_key);
>    var_dump($cached_result);
>
> is this:
>    object(mysqli_result)#2 (0) { }
>
> So, shouldn't I be able to treat $cached_result as a mysqli result 
> set?  Shouldn't something like the following work (it doesn't)?
>    $num_results = $cached_result->num_rows;
>
> Should I massage $result into something else before sticking it into 
> $memcache->set()?
>
> Thanks in advance,
> Josh

What we do at Gigoit is convert the MySQL result set into a 
multidimensional array.  Each item in the array maps to a returned row 
from MySQL.  The column names are used as keys.  Below is the method we 
use in our custom MySQL and Memcache class.  I would advise you not to 
perform a call to die if you cannot set the value in Memcache.  Ideally 
your application should run even if memcache goes down for whatever 
reason.  You could however, log that failure to a file.

Oh and the reason you can't get the result set out in it's current form 
is that you cannot serialize a MySQL result set.  You can only store 
things in Memcache that can be serialized and unserialized from PHP.

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    /**
    * Query Database
    *
    * The method queryDB() performs a query on the MySQL database. 
    * It optionally stores the result set in memcache 
    *
    * @author John Kramlich
    * @version 1.0
    * @param string $sql The SQL query to perform.  Don't pass queries 
that update or insert
    * @param integer $expires Int in seconds dictating when the item 
will be flushed.  0 = item will persist indefinitely until specifically 
flushed
    */
    public function queryDB($sql,$expires = 10 ,$cache = TRUE){
        ## Connect to the Database
        $db_connection = new 
mysqli(self::DB_HOST,self::DB_USER,self::DB_PASSWORD,self::DB_DATABASE);
        if (mysqli_connect_errno()){
            printf("Connect failed: %s\n",mysqli_connect_error());
            exit();
        }
        ## Perform Query
        $objResultSet = $db_connection->query($sql);
       
        ## Init the intermediate arrays
        $ary_field_list = array();
        $ary_results = array();
       
        ## Check if the resultset has 0 rows
        if($objResultSet->num_rows > 0){
           
            ## Fetch the Column information
            $finfo = $objResultSet->fetch_fields();
           
            ## Loop through columns, add them to column array
            foreach ($finfo as $val){
                $ary_field_list[] = $val->name;
            }
           
            ## Reset the seek point
            $objResultSet->data_seek(0);
           
            ## Loop through result set to build array
            while($row = mysqli_fetch_row($objResultSet)){
                $ary_row = array();
                foreach ($ary_field_list as $key){
                    $ary_row[$key] = 
$row[array_search($key,$ary_field_list)];
                }
                $ary_results[] = $ary_row;
            }
        }
       
        ## Should this be cached
        if($cache){
       
            ## Log that we saved to the cache
            gLog::add($sql);
           
            ## Store the resultset in the cache

            self::getMe()->set(MD5($sql), $ary_results, intval($expires));
        }
        ## return it
        return $ary_results;
    }
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

- John