Hi David,
Got some spare time so am doing some PT stuff!
Looking to centralise a load of shared stuff between sites in one central DB table - mainly variables/accounts that can be used across all my sites.
Now, I don't want queries being made on every page load so is there a good way of loading the data in once and then 'caching' it so to speak? I guess it would involve either cookies or maybe a scheduled script to write these values in to a config file which would then be read as normal?
Ideas appreciated!
Cheers.
Keeop
Hi David,
So, if I use the cached query to populate my site config, this query won't run again on every page load (which is where I'd put it) or will it still run but not return results? I don't think I explained very well to start with! Basically, I'm looking to store a lot of config variables that I currently have set in all my various sites' config.php files. A lot of these values are site specific and I'd rather the config.php was kept generic and the specific values pulled from a database instead. Plus, if there are shared values, I only need to update in one place, such as if I was updating my variable for 'minimum Amazon spend for free delivery'.
Is there a good way of doing this?
Cheers.
Keeop
Hi Keeop,
It sounds like the above will do the trick! database_querySelectCached() will always return results, it just depends whether an actual database query is made, or if there is a recent enough cached version (based on $cacheAge) then the results come from the cached results instead - but you will always get a result..
Cheers,
David.
--
PriceTapestry.com
Cool, thanks David - I'll give it a go. My only concern is it's one extra query it's got to run even if the results returned are cached.
Keeop
Hi Keeop,
The check for a cached result is simply a file_exists() check against the cache/ folder - it doesn't hit the database at all - so extremely fast compared to an actual database query!
Cheers,
David.
--
PriceTapestry.com
Hi David,
Thanks. Didn't even check the code - of course! So, no different to reading in the config.php file on each page load? Does the 'fopen' work the same way as an 'include' or 'require'? Would this be cached in memory somewhere? Don't want to add any extra disk accessing if possible - not asking for much am I?!
Cheers.
Keeop
Hi Keeop,
If you keep all your settings in a single table so that they can be loaded using a single query then using database_querySelectCached() would be, in terms of disk access (assuming a cache HIT of course), identical to:
<?php
if (file_exists("settings.php"))
{
require("settings.php");
}
?>
Cheers,
David.
--
PriceTapestry.com
Hello Keeop,
It's straight forward to make a caching version of the database_querySelect() function. Firstly, make a cache folder sqlcache/ in the top level folder of your Price Tapestry installation, and make sure that it is writable by PHP. The easiest way to do this is normally with your FTP program. Create the folder in the remote window and then right-click on the new folder and look for Permissions... or maybe Properties... and then Permissions, and give WRITE access to all users (owner/group/world).
With that in place, add the following to includes/database.php
function database_querySelectCached($sql,&$rows,$cacheAge){
$cacheDir = "sqlcache/";
$filename = $cacheDir.md5($sql);
if (!file_exists($filename) || (filemtime($filename) < (time()-$cacheAge)))
{
if (database_querySelect($sql,$rows))
{
$fp = fopen($filename,"w");
fwrite($fp,serialize($rows));
}
}
else
{
$rows = unserialize(file_get_contents($filename));
}
return count($rows);
}
And then wherever you wish to convert live queries to cached, simply replace database_querySelect with database_querySelectCached, and include the additional 3 parameter $cacheAge, which is the period in seconds for the cached result to remain valid.
For example, to show total number of compared products, cached for 24 hours you might use:
<?php$sql = "SELECT COUNT(DISTINCT(name)) AS numProducts FROM `".$config_databaseTablePrefix."products`";
database_querySelectCached($sql,$rows,86400);
print "<p>Comparing ".$rows[0]["numProducts"]." products</p>";
?>
Hope this helps!
Cheers,
David.
--
PriceTapestry.com