Caching a MySQL Resultset

I need a working code for storing a MySQL result set in APC cache!

i searched on Google & SO and did not find any!

Hope someone will share a working code.

Example:

$stmt=mysqli_prepare($con,"SELECT UID FROM Users");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $UID);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);

Example 2:

$stmt=mysqli_prepare($con,"SELECT Events FROM Calendar where UID=?");
mysqli_stmt_bind_result($stmt, "i",$UID);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $events);
while(mysqli_stmt_fetch($stmt))
{
   ...
}
mysqli_stmt_close($stmt);

How store the outcom开发者_如何学Going SQL result in APC?

i want to know the code to cache it in APC (not in MySQL)

You probably don't really want to do this!

Adding a caching layer to your application should be one of the last things you do. Caching can uncover a tremendous number of bugs in your code, both subtle and obvious.

If you are trying to improve the performance of your code, you should be performing code profiling and eliminating real bottlenecks rather than perceived ones.

Especially in this case.

Your example query here is bad. Horrible, even. You're fetching the UID column from every row in the table, failing to specify an order for those rows, and then only actually fetching the first row, whichever it might be.

Even using this query, not to even think about caching it, is dangerous. If it's a real query then your code is broken to begin with! Results coming back from the database are not guaranteed to be in any specific order. Indeed, at least with MySQL, you can actually reset the on-disk data ordering using ALTER TABLE ... ORDER BY. If you only want one row, you should be using an ORDER BY clause and a LIMIT clause, not selecting everything and then only fetching one row.

I'm going to operate under the assumption that you've oversimplified the query as an example, and will humor you with a quick primer on the two or three APC functions that you need to know. You should read the manual pages for all of the other functions to gain an understanding of how APC works.

Let's look at your code.

$stmt=mysqli_prepare($con,"SELECT UID FROM Users");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $UID);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);

In this code, you are preparing the query SELECT UID FROM Users, binding the first column of the result set into the PHP variable $UID, fetching a single row, then closing the statement handle (discarding all other results).

$UID contains the single value you want to cache. To cache this value in APC, you can use apc_store:

apc_store($key, $UID);

$key is the cache key name.

Of course, putting it in the cache is silly without, say, only querying the database if you need to. We can use apc_fetch to look for the cached value first.

$value_in_cache = false;
$value = apc_fetch($key, $value_in_cache);
if(!$value_in_cache) {
    $value = null;
    $stmt = mysqli_prepare($con,"SELECT UID FROM Users");
    mysqli_stmt_execute($stmt);
    mysqli_stmt_bind_result($stmt, $value);
    mysqli_stmt_fetch($stmt);
    mysqli_stmt_close($stmt);
    apc_store($key, $value);
}

This code will always try and pull $key out of the cache before querying the database, which is more likely what you wanted to accomplish. Note that I've intentionally left off the cache expire time here.

You will note that obviously only one value got cached here. If you insist on using bound result variables, then you're either going to need to manually build an array and cache that array instead. I highly suggest not using bound variables. Look at using mysql_stmt_get_result to fetch a result set, which you can then grab an array from. Watch out, the manual says that get_result returns boolean, yet has it return a result set object in the example code. YMMV. May I suggest PDO for your future projects?

No matter what database interface is being used, you can not cache the actual statement handle or result set objects, only the data that they return.

Here is a working code :)

<?php
$dba_host='localhost';
$dba_name='root';
$dba_pass='';
$dba_db='DB';

$con=mysqli_connect($dba_host,$dba_name,$dba_pass,$dba_db) or die('Connection Refused !');

$stmt=mysqli_prepare($con,"SELECT UID FROM Main");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $value);
while(mysqli_stmt_fetch($stmt))
 $result[] = $value;
mysqli_stmt_close($stmt);
apc_store('uid', $result);


$value_in_cache = false;
$value = apc_fetch('uid', $value_in_cache);
if(!$value_in_cache) {
 echo 'Looking at DB';
$stmt=mysqli_prepare($con,"SELECT UID FROM Main");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $value);
while(mysqli_stmt_fetch($stmt))
    $result[] = $value;
mysqli_stmt_close($stmt);
apc_store('uid', $result);
}
else
{
 echo 'Looking at Memory';
 print_r($result);
}

mysqli_close($con);
?>