1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

<?php

function get_query_results($sql)
{
  if (!($results = get_query_results_from_cache($sql)))
  {
    $results = get_query_results_from_db($sql);
    put_query_results_in_cache($sql, $results);
  }
  return $results;
}

function get_query_results_from_db($sql)
{
  $query = mysql_query($sql) or die('Query error');
  $arr = array();
  while($row = mysql_fetch_assoc($query))
  {
    $result = array();
    foreach($row as $key=>$val)
    {
      $results[$key] = $val;
    }
    $arr[] = $result;
  }
  return $arr;
}

function get_query_results_from_cache($sql)
{
  $update_time_sql = 'SELECT `updated` FROM `ftp_updates` ORDER BY `updated` DESC LIMIT 1';
  $cache_sql = 'SELECT * FROM `query_cache` WHERE `query` = \''.mysql_escape_string($sql).'\' AND cached>('.$update_time_sql.') LIMIT 1';
  $cache_qry = mysql_query($cache_sql) or die('Cache Error::: '.mysql_error());
  $row = mysql_fetch_row($qry);
  if (!empty($row) && !empty($row['results']))
  {
    return unserialize($row['results']);
  }
  else
  {
    return false;
  }
}

function put_query_results_in_cache($sql, $results)
{
  //NOTE: this assumes that the query has field has a unique key constraint
  $cache_sql = 'INSERT INTO `query_cache` (`cached`, `query`, `results`) VALUES (now(), \''.mysql_escape_string($sql).'\', \''.mysql_escape_string(serialize($arr)).'\') ON DUPLICATE KEY UPDATE cached=now(), query=VALUES(query), results=VALUES(results)';
  mysql_query($cache_sql) or die('Cache Update Error'); // same message for any failed insert/update into the cache
}

?>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

<?php
function convert_datetime($str)
 {
  list($date, $time)            = explode(' ', $str);
  list($year, $month, $day)     = explode('-', $date);
  list($hour, $minute, $second) = explode(':', $time);
  $timestamp                    = mktime($hour, $minute, $second, $month, $day, $year);
  return $timestamp;
 }

function db_cache_2($query)
 {
  // Query cache table
  $sql = 'SELECT * FROM `query_cache` WHERE `query` = \''.mysql_escape_string($query).'\' AND `type` = \'2\' LIMIT 1';
  $qry = mysql_query($sql) or die('Cache Query Error::: '.mysql_error());

  // No cache
  if(mysql_num_rows($qry) < 1)
   {
    $qry2  = mysql_query($query) or die('Query error');
    $i     = 0;

    while($row = mysql_fetch_assoc($qry2))
     {
      foreach($row as $key=>$val)
       {
        $arr[$i][$key] = $val;
       }
      ++$i;
     }

    $x = (count($arr)-1);

    for($y=0; $y<=$x; ++$y)
     {
      $a .= 'HTML'
     }

    $sql2 = 'INSERT INTO `query_cache` (`cached`, `query`, `results`, `type`) VALUES (now(), \''.mysql_escape_string($query).'\', \''.$a.'\', \'2\')';
    mysql_query($sql2) or die('Cache Insert Error');
   }
  else
   {
    // Cache avail
    $row = mysql_fetch_assoc($qry);

    // Check the file updates
    $sql2 = 'SELECT `updated` FROM `ftp_updates` ORDER BY `updated` DESC LIMIT 1';
    $qry2 = mysql_query($sql2) or die('Cache Compare Query Error::: '.mysql_error());
    $row2 = mysql_fetch_row($qry2);

    // Out of date - Update the cache
    if(convert_datetime($row['cached']) < convert_datetime($row2[0]))
     {
      $qry3  = mysql_query($query) or die('Query error');
      $i    = 0;

      while($row3 = mysql_fetch_assoc($qry3))
       {
        foreach($row3 as $key=>$val)
         {
          $arr[$i][$key] = $val;
         }
        ++$i;
       }

    $x = (count($arr)-1);

    for($y=0; $y<=$x; ++$y)
     {
      $a .= 'HTML';
     }

      $sql2 = 'UPDATE `query_cache` SET `cached` = now(), `query` = \''.mysql_escape_string($query).'\', `results` = \''.$a.'\' WHERE `id` = \''.$row['id'].'\'';
      mysql_query($sql2) or die('Cache Update Error');
     }
    else
     {
      // Cache Valid
      // Would have put this above the if - but I think the unserialize would slow things down more than the else statement would.
      // Plus I would have to blank out the arr to make sure the array was correct in the end (No old data).
      $a = $row['results'];
     }
   }
  return($a);
 }
?>