Back To Programming / Scripting / Coding Forum

Problem with caching SQL results

  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8212
  • Loc: USA
  • Status: Offline

Post November 5th, 2010, 10:10 pm

Ok, I have the following function:
PHP Code: [ Select ]
    public function fetch_rowset($sql, $type = 'assoc')
    {
        // Checking if the SQL query was cached
        if($this->cache_results === true)
        {
            // The name of the cached file
            $sql_file = $this->cache_dir . md5($sql) . '.cache';
       
            // Checking if the SQL file exists
            if(file_exists($sql_file))
            {
                return unserialize(file_get_contents($sql_file));
            }
        }
       
        // Creating a result resource
        $results = $this->set_result_resource($sql, true);
       
        // Initiating the result array
        $result = array();
       
        // Checking what type of result we want
        if($type == 'assoc')
        {
            while($row = mysql_fetch_assoc($results))
            {
                $result[] = $row;
            }
        }
        elseif($type == 'array')
        {
            while($row = mysql_fetch_array($results))
            {
                $result[] = $row;
            }
        }
        elseif($type == 'object')
        {
            while($row = mysql_fetch_object($results))
            {
                $result[] = $row;
            }
        }
        elseif($type == 'row')
        {
            while($row = mysql_fetch_row($results))
            {
                $result[] = $row;
            }
        }
        else
        {
            while($row = mysql_fetch_assoc($results))
            {
                $result[] = $row;
            }
        }
       
        // Checking if we need to free the result resource
        if($this->free_result == true)
        {
            $this->free_result();
        }
       
        // Checking if we need to cache the result
        if($this->cache_results === true)
        {
            return $this->cache_results($sql, $result);
        }
       
        // Returning the result (if not cached)
        return ($type == 'object') ? (object) $result : $result;
    }
  1.     public function fetch_rowset($sql, $type = 'assoc')
  2.     {
  3.         // Checking if the SQL query was cached
  4.         if($this->cache_results === true)
  5.         {
  6.             // The name of the cached file
  7.             $sql_file = $this->cache_dir . md5($sql) . '.cache';
  8.        
  9.             // Checking if the SQL file exists
  10.             if(file_exists($sql_file))
  11.             {
  12.                 return unserialize(file_get_contents($sql_file));
  13.             }
  14.         }
  15.        
  16.         // Creating a result resource
  17.         $results = $this->set_result_resource($sql, true);
  18.        
  19.         // Initiating the result array
  20.         $result = array();
  21.        
  22.         // Checking what type of result we want
  23.         if($type == 'assoc')
  24.         {
  25.             while($row = mysql_fetch_assoc($results))
  26.             {
  27.                 $result[] = $row;
  28.             }
  29.         }
  30.         elseif($type == 'array')
  31.         {
  32.             while($row = mysql_fetch_array($results))
  33.             {
  34.                 $result[] = $row;
  35.             }
  36.         }
  37.         elseif($type == 'object')
  38.         {
  39.             while($row = mysql_fetch_object($results))
  40.             {
  41.                 $result[] = $row;
  42.             }
  43.         }
  44.         elseif($type == 'row')
  45.         {
  46.             while($row = mysql_fetch_row($results))
  47.             {
  48.                 $result[] = $row;
  49.             }
  50.         }
  51.         else
  52.         {
  53.             while($row = mysql_fetch_assoc($results))
  54.             {
  55.                 $result[] = $row;
  56.             }
  57.         }
  58.        
  59.         // Checking if we need to free the result resource
  60.         if($this->free_result == true)
  61.         {
  62.             $this->free_result();
  63.         }
  64.        
  65.         // Checking if we need to cache the result
  66.         if($this->cache_results === true)
  67.         {
  68.             return $this->cache_results($sql, $result);
  69.         }
  70.        
  71.         // Returning the result (if not cached)
  72.         return ($type == 'object') ? (object) $result : $result;
  73.     }

You see where it checks if it is cached? At the beginning to see if it needs to return near the very end where it checks if it was needed to be cached...

Anyway, the function that actually caches is:
PHP Code: [ Select ]
    private function cache_results($sql, $results)
    {
        // Generating the file name for the cache
        $sql_file = $this->cache_dir . md5($sql) . '.cache';
       
        // Creating the cache file
        if(touch($sql_file))
        {
            file_put_contents($sql_file, serialize($results));
        }
        return $results;
    }
  1.     private function cache_results($sql, $results)
  2.     {
  3.         // Generating the file name for the cache
  4.         $sql_file = $this->cache_dir . md5($sql) . '.cache';
  5.        
  6.         // Creating the cache file
  7.         if(touch($sql_file))
  8.         {
  9.             file_put_contents($sql_file, serialize($results));
  10.         }
  11.         return $results;
  12.     }

The problem I'm having is when I try to run the SQL and be caching them, it caches them as integer. I do var_dump($results) and it prints int(1).

I check the cache files and they say i:1;

Any help?
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post November 5th, 2010, 10:10 pm

  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8212
  • Loc: USA
  • Status: Offline

Post November 6th, 2010, 11:22 am

I still haven't being able find a solution to this problem yet. Is there anyone here who is able to help me solve this issue.

The function has now changed to the following:
PHP Code: [ Select ]
    public function fetch_rowset($sql, $type = 'assoc')
 
    {
 
        // Checking if the SQL query was cached
 
        if($this->cache_results === true)
 
        {
 
            // The name of the cached file
 
            $sql_file = $this->cache_dir . md5($sql) . '.cache';
 
       
 
            // Checking if the SQL file exists
 
            if(file_exists($sql_file))
 
            {
 
                return unserialize(file_get_contents($sql_file));
 
            }
 
        }
 
       
 
        // Creating a result resource
 
        $results = $this->set_result_resource($sql, true);
 
       
 
        // Checking what type of result we want
 
        if($type == 'assoc')
 
        {
 
            while($row = mysql_fetch_assoc($results))
 
            {
 
                $result = $row;
 
            }
 
        }
 
 
 
        elseif($type == 'array')
 
        {
 
            while($row = mysql_fetch_array($results))
 
            {
 
                $result = $row;
 
            }
 
        }
 
        elseif($type == 'object')
 
        {
 
            while($row = mysql_fetch_object($results))
 
            {
 
                $result = $row;
 
            }
 
        }
 
        elseif($type == 'row')
 
        {
 
            while($row = mysql_fetch_row($results))
 
            {
 
                $result = $row;
 
            }
 
        }
 
        else
 
        {
 
            while($row = mysql_fetch_assoc($results))
 
            {
 
                $result = $row;
 
            }
 
        }
 
       
 
        // Checking if we need to free the result resource
 
        if($this->free_result == true)
 
        {
 
            $this->free_result();
 
        }
 
       
 
        // Checking if we need to cache the result
 
        if($this->cache_results === true)
 
        {
 
            $this->cache_result($sql, $result);
 
        }
 
       
 
        // Returning the result (if not cached)
 
        return ($type == 'object') ? (object) $result : $result;
 
    }
  1.     public function fetch_rowset($sql, $type = 'assoc')
  2.  
  3.     {
  4.  
  5.         // Checking if the SQL query was cached
  6.  
  7.         if($this->cache_results === true)
  8.  
  9.         {
  10.  
  11.             // The name of the cached file
  12.  
  13.             $sql_file = $this->cache_dir . md5($sql) . '.cache';
  14.  
  15.        
  16.  
  17.             // Checking if the SQL file exists
  18.  
  19.             if(file_exists($sql_file))
  20.  
  21.             {
  22.  
  23.                 return unserialize(file_get_contents($sql_file));
  24.  
  25.             }
  26.  
  27.         }
  28.  
  29.        
  30.  
  31.         // Creating a result resource
  32.  
  33.         $results = $this->set_result_resource($sql, true);
  34.  
  35.        
  36.  
  37.         // Checking what type of result we want
  38.  
  39.         if($type == 'assoc')
  40.  
  41.         {
  42.  
  43.             while($row = mysql_fetch_assoc($results))
  44.  
  45.             {
  46.  
  47.                 $result = $row;
  48.  
  49.             }
  50.  
  51.         }
  52.  
  53.  
  54.  
  55.         elseif($type == 'array')
  56.  
  57.         {
  58.  
  59.             while($row = mysql_fetch_array($results))
  60.  
  61.             {
  62.  
  63.                 $result = $row;
  64.  
  65.             }
  66.  
  67.         }
  68.  
  69.         elseif($type == 'object')
  70.  
  71.         {
  72.  
  73.             while($row = mysql_fetch_object($results))
  74.  
  75.             {
  76.  
  77.                 $result = $row;
  78.  
  79.             }
  80.  
  81.         }
  82.  
  83.         elseif($type == 'row')
  84.  
  85.         {
  86.  
  87.             while($row = mysql_fetch_row($results))
  88.  
  89.             {
  90.  
  91.                 $result = $row;
  92.  
  93.             }
  94.  
  95.         }
  96.  
  97.         else
  98.  
  99.         {
  100.  
  101.             while($row = mysql_fetch_assoc($results))
  102.  
  103.             {
  104.  
  105.                 $result = $row;
  106.  
  107.             }
  108.  
  109.         }
  110.  
  111.        
  112.  
  113.         // Checking if we need to free the result resource
  114.  
  115.         if($this->free_result == true)
  116.  
  117.         {
  118.  
  119.             $this->free_result();
  120.  
  121.         }
  122.  
  123.        
  124.  
  125.         // Checking if we need to cache the result
  126.  
  127.         if($this->cache_results === true)
  128.  
  129.         {
  130.  
  131.             $this->cache_result($sql, $result);
  132.  
  133.         }
  134.  
  135.        
  136.  
  137.         // Returning the result (if not cached)
  138.  
  139.         return ($type == 'object') ? (object) $result : $result;
  140.  
  141.     }
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8212
  • Loc: USA
  • Status: Offline

Post November 6th, 2010, 8:03 pm

Ok, never mind. The problem wasn't with the caching... I finally managed to fix it. Talk about a load off my back :lol: I can finally continue with my project.
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13458
  • Loc: Florida
  • Status: Offline

Post November 7th, 2010, 9:53 am

Glad you worked it out. I've looked at this thread a couple of times wondering why the MySQL query cache wasn't cache enough. :)
Strong with this one, the sudo is.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8212
  • Loc: USA
  • Status: Offline

Post November 7th, 2010, 7:40 pm

joebert wrote:
Glad you worked it out. I've looked at this thread a couple of times wondering why the MySQL query cache wasn't cache enough. :)

:D

The problem was with how I was implementing the cache.

I was using my _get_() function which calls mysql_num_rows(), which then, it caches it. Then it calls on mysql_fetch_row and caches it as well... the problem with that is that the SQL was the same for both queries.

Well, that wasn't THE problem, but they both tried to write the results to the same cache file.

With Ubuntu, as soon as it creates the written file, they are automatically chmodded as read-only, so mysql_fetch_row was returning int(1) since that's what is in the cache after mysql_num_rows.


I added _num to the SQL that mysql_num_row caches as file name md5($sql . '_num')
"Bring forth therefore fruits meet for repentance:" Matthew 3:8

Page 1 of 1

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 154 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.